src/MatControl.Data/Import/ExcelImporter.cs - master - valzem/MatControl

src/MatControl.Data/Import/ExcelImporter.cs
406 строк13 KB
MatControl Dev
Бизнес-правила и целостность данных: списание только с МОЛ, возврат МЗ по сумме выдач, детерминированный выбор МОЛ, лимит количества МЗ, свободные ОС для МОЛ; состояние 'К списанию'; инструкция, AGENTS.md, скиллы и задачи обновлены (78 тестов)
09 авг 2026, 07:22
09 авг 2026, 07:226b6ae74
using System.Data;
using System.Globalization;
using ExcelDataReader;
using MatControl.Domain;

namespace MatControl.Data.Import;

public static class ExcelImporter
{
    static ExcelImporter()
    {
        System.Text.Encoding.RegisterProvider(System.Text.CodePagesEncodingProvider.Instance);
    }

    public static List<ParsedAsset> ReadAssets(string filePath, string sourceKey)
    {
        using var stream = File.Open(filePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
        using var reader = ExcelReaderFactory.CreateReader(stream);
        var dataSet = reader.AsDataSet(new ExcelDataSetConfiguration
        {
            ConfigureDataTable = _ => new ExcelDataTableConfiguration { UseHeaderRow = false }
        });

        var table = dataSet.Tables[0];
        return ParseAssetTable(table, sourceKey);
    }

    public static List<ParsedSupply> ReadSupplies(string filePath)
    {
        using var stream = File.Open(filePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
        using var reader = ExcelReaderFactory.CreateReader(stream);
        var dataSet = reader.AsDataSet(new ExcelDataSetConfiguration
        {
            ConfigureDataTable = _ => new ExcelDataTableConfiguration { UseHeaderRow = false }
        });

        var table = dataSet.Tables[0];
        return ParseSupplyTable(table);
    }

    public static List<string> ReadHeaders(string filePath)
    {
        using var stream = File.Open(filePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
        using var reader = ExcelReaderFactory.CreateReader(stream);
        var dataSet = reader.AsDataSet(new ExcelDataSetConfiguration
        {
            ConfigureDataTable = _ => new ExcelDataTableConfiguration { UseHeaderRow = false }
        });

        var table = dataSet.Tables[0];
        if (table.Rows.Count == 0)
        {
            return new List<string>();
        }

        var headers = new List<string>();
        for (var col = 0; col < table.Columns.Count; col++)
        {
            headers.Add(table.Rows[0][col]?.ToString()?.Trim() ?? $"Колонка {col + 1}");
        }

        return headers;
    }

    public static List<ParsedAsset> ReadAssetsWithMapping(string filePath, string sourceKey, int headerRow, AssetColumnMapping mapping)
    {
        using var stream = File.Open(filePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
        using var reader = ExcelReaderFactory.CreateReader(stream);
        var dataSet = reader.AsDataSet(new ExcelDataSetConfiguration
        {
            ConfigureDataTable = _ => new ExcelDataTableConfiguration { UseHeaderRow = false }
        });

        var table = dataSet.Tables[0];
        return ParseAssetTableWithMapping(table, sourceKey, headerRow, mapping);
    }

    public static List<ParsedSupply> ReadSuppliesWithMapping(string filePath, int headerRow, SupplyColumnMapping mapping)
    {
        using var stream = File.Open(filePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
        using var reader = ExcelReaderFactory.CreateReader(stream);
        var dataSet = reader.AsDataSet(new ExcelDataSetConfiguration
        {
            ConfigureDataTable = _ => new ExcelDataTableConfiguration { UseHeaderRow = false }
        });

        var table = dataSet.Tables[0];
        return ParseSupplyTableWithMapping(table, headerRow, mapping);
    }

    public static List<ParsedAsset> ParseAssetTableWithMapping(DataTable table, string sourceKey, int headerRow, AssetColumnMapping mapping)
    {
        var assets = new List<ParsedAsset>();
        var sequence = 0;
        for (var row = headerRow + 1; row < table.Rows.Count; row++)
        {
            var cells = GetRowCells(table, row);
            var name = mapping.NameColumn >= 0 ? GetCell(cells, mapping.NameColumn)?.Trim() : null;
            if (string.IsNullOrEmpty(name))
            {
                continue;
            }

            sequence++;

            var inventoryNumber = mapping.InventoryColumn >= 0 ? GetCell(cells, mapping.InventoryColumn)?.Trim() : null;
            if (string.IsNullOrEmpty(inventoryNumber))
            {
                inventoryNumber = $"{sourceKey}-{sequence:D3}";
            }

            var price = mapping.PriceColumn >= 0 ? ParseDecimal(GetCell(cells, mapping.PriceColumn)) : 0;
            var yearInUse = mapping.YearColumn >= 0 ? ParseYear(GetCell(cells, mapping.YearColumn)) : null;

            assets.Add(new ParsedAsset
            {
                InventoryNumber = inventoryNumber,
                Name = name,
                YearInUse = yearInUse,
                State = mapping.StateColumn >= 0 ? ParseState(GetCell(cells, mapping.StateColumn)) : AssetState.Рабочее,
                Price = price
            });
        }

        return assets;
    }

    public static List<ParsedSupply> ParseSupplyTableWithMapping(DataTable table, int headerRow, SupplyColumnMapping mapping)
    {
        var supplies = new List<ParsedSupply>();
        for (var row = headerRow + 1; row < table.Rows.Count; row++)
        {
            var cells = GetRowCells(table, row);
            var name = mapping.NameColumn >= 0 ? GetCell(cells, mapping.NameColumn)?.Trim() : null;
            if (string.IsNullOrEmpty(name))
            {
                continue;
            }

            if (string.Equals(name, "Итого", StringComparison.OrdinalIgnoreCase))
            {
                break;
            }

            var price = mapping.PriceColumn >= 0 ? ParseDecimal(GetCell(cells, mapping.PriceColumn)) : 0;
            var quantity = mapping.QuantityColumn >= 0 ? ParseDecimal(GetCell(cells, mapping.QuantityColumn)) : 0;
            if (price <= 0 || quantity <= 0)
            {
                continue;
            }

            supplies.Add(new ParsedSupply
            {
                Name = name,
                Price = price,
                Quantity = quantity,
                TotalSum = Math.Round(price * quantity, 2)
            });
        }

        return supplies;
    }

    public static List<ParsedAsset> ParseAssetTable(DataTable table, string sourceKey)
    {
        var assets = new List<ParsedAsset>();
        var columns = FindAssetColumns(table);
        if (columns.HeaderRow < 0 || columns.NameColumn < 0 || columns.InventoryColumn < 0)
        {
            return assets;
        }

        var sequence = 0;
        for (var row = columns.HeaderRow + 1; row < table.Rows.Count; row++)
        {
            var cells = GetRowCells(table, row);
            var number = GetCell(cells, 0)?.Trim();
            if (string.Equals(number, "Итого", StringComparison.OrdinalIgnoreCase))
            {
                break;
            }

            var name = GetCell(cells, columns.NameColumn)?.Trim();
            if (string.IsNullOrEmpty(name) || !int.TryParse(number, out _))
            {
                continue;
            }

            sequence++;

            var inventoryNumber = GetCell(cells, columns.InventoryColumn)?.Trim();
            if (string.IsNullOrEmpty(inventoryNumber))
            {
                inventoryNumber = $"{sourceKey}-{sequence:D3}";
            }

            var price = ParseDecimal(GetCell(cells, columns.PriceColumn));
            var yearInUse = ParseYear(GetCell(cells, columns.YearColumn));

            assets.Add(new ParsedAsset
            {
                InventoryNumber = inventoryNumber,
                Name = name,
                YearInUse = yearInUse,
                State = ParseState(GetCell(cells, columns.StateColumn)),
                Price = price
            });
        }

        return assets;
    }

    public static List<ParsedSupply> ParseSupplyTable(DataTable table)
    {
        var supplies = new List<ParsedSupply>();
        for (var row = 0; row < table.Rows.Count; row++)
        {
            var cells = GetRowCells(table, row);
            var name = GetCell(cells, 0)?.Trim();
            if (string.IsNullOrEmpty(name))
            {
                continue;
            }

            if (string.Equals(name, "Итого", StringComparison.OrdinalIgnoreCase))
            {
                break;
            }

            var price = ParseDecimal(GetCell(cells, 1));
            var quantity = ParseDecimal(GetCell(cells, 2));
            if (price <= 0 || quantity <= 0)
            {
                continue;
            }

            supplies.Add(new ParsedSupply
            {
                Name = name,
                Price = price,
                Quantity = quantity,
                TotalSum = Math.Round(price * quantity, 2)
            });
        }

        return supplies;
    }

    private static AssetColumns FindAssetColumns(DataTable table)
    {
        var result = new AssetColumns { HeaderRow = -1 };
        for (var row = 0; row < table.Rows.Count; row++)
        {
            var cells = GetRowCells(table, row);
            for (var col = 0; col < cells.Length; col++)
            {
                var value = GetCell(cells, col)?.Trim() ?? string.Empty;
                if (value.Contains("Инвентарный номер", StringComparison.OrdinalIgnoreCase))
                {
                    result.InventoryColumn = col;
                    result.HeaderRow = row;
                }
                else if (value.Contains("Основное средство", StringComparison.OrdinalIgnoreCase))
                {
                    result.NameColumn = col;
                }
                else if (value.Contains("Дата принятия к учету", StringComparison.OrdinalIgnoreCase))
                {
                    result.YearColumn = col;
                }
                else if (value.Contains("Состояние", StringComparison.OrdinalIgnoreCase))
                {
                    result.StateColumn = col;
                }
                else if (value.Contains("Балансовая стоимость", StringComparison.OrdinalIgnoreCase))
                {
                    result.PriceColumn = col;
                }
            }
        }

        return result;
    }

    private static object?[] GetRowCells(DataTable table, int row)
    {
        var values = new object?[table.Columns.Count];
        for (var col = 0; col < table.Columns.Count; col++)
        {
            values[col] = table.Rows[row][col];
        }

        return values;
    }

    private static string? GetCell(object?[] cells, int index)
    {
        if (index < 0 || index >= cells.Length)
        {
            return null;
        }

        return cells[index]?.ToString();
    }

    private static decimal ParseDecimal(string? value)
    {
        if (string.IsNullOrWhiteSpace(value))
        {
            return 0;
        }

        var normalized = value.Trim().Replace(" ", "").Replace("\u00A0", "");
        if (decimal.TryParse(normalized, NumberStyles.Number, CultureInfo.GetCultureInfo("ru-RU"), out var ru))
        {
            return ru;
        }

        if (decimal.TryParse(normalized, NumberStyles.Number, CultureInfo.InvariantCulture, out var inv))
        {
            return inv;
        }

        return 0;
    }

    private static int? ParseYear(string? value)
    {
        if (string.IsNullOrWhiteSpace(value))
        {
            return null;
        }

        if (DateTime.TryParseExact(value, "dd.MM.yyyy", CultureInfo.InvariantCulture, DateTimeStyles.None, out var date))
        {
            return date.Year;
        }

        return null;
    }

    private static AssetState ParseState(string? value)
    {
        if (string.IsNullOrWhiteSpace(value))
        {
            return AssetState.Рабочее;
        }

        var normalized = value.Trim().ToLowerInvariant();
        if (normalized.Contains("к списанию") ||
            normalized.Contains("для списания") ||
            normalized.Contains("подлежит списанию"))
        {
            return AssetState.К_списанию;
        }

        if (normalized.Contains("списан"))
        {
            return AssetState.Списан;
        }

        if (normalized.Contains("не рабочее") || normalized.Contains("нерабочее"))
        {
            return AssetState.Не_рабочее;
        }

        return AssetState.Рабочее;
    }

    private sealed class AssetColumns
    {
        public int HeaderRow { get; set; }

        public int InventoryColumn { get; set; } = -1;

        public int NameColumn { get; set; } = -1;

        public int YearColumn { get; set; } = -1;

        public int StateColumn { get; set; } = -1;

        public int PriceColumn { get; set; } = -1;
    }
}

public class AssetColumnMapping
{
    public int InventoryColumn { get; set; } = -1;

    public int NameColumn { get; set; } = -1;

    public int YearColumn { get; set; } = -1;

    public int StateColumn { get; set; } = -1;

    public int PriceColumn { get; set; } = -1;
}

public class SupplyColumnMapping
{
    public int NameColumn { get; set; } = -1;

    public int PriceColumn { get; set; } = -1;

    public int QuantityColumn { get; set; } = -1;
}