RhSolutions-AddIn/RhSolutions.AddIn/Services/ExcelReader.cs

using System.IO;
#if !NET472
using System.Runtime.Versioning;
using RhSolutions.Tools;
#endif

namespace RhSolutions.Services;

#if !NET472
[SupportedOSPlatform("windows")]
#endif
public class ExcelReader : IReader, IDisposable
{
    private ProgressBar _progressBar;
    private readonly Dictionary<string, string> headers;
    private readonly Application _application;

    public ExcelReader(Application application, IAddInConfiguration configuration)
    {
        _application = application;
        headers = configuration.GetPriceListHeaders();
    }

    public Dictionary<Product, double> ReadProducts(Range range)
    {
        object[,] cells = range.Value2;
        Dictionary<Product, double> readResult = new();

        for (int row = 1; row <= range.Rows.Count; row++)
        {
            if (cells[row, 1] == null || cells[row, 2] == null)
                continue;

            string currentSku = null;
            double? currentAmount = null;

            for (int column = 1; column <= 2; column++)
            {
                object currentCell = cells[row, column];

                if (ProductSku.TryParse(currentCell.ToString(), out var validSku))
                {
                    currentSku = validSku.FirstOrDefault().ToString() ?? null;
                }

                else if (currentCell.GetType() == typeof(string)
                    && double.TryParse(currentCell.ToString(), out _))
                {
                    currentAmount = double.Parse((string)currentCell);
                }

                else if (currentCell.GetType() == typeof(double))
                {
                    currentAmount = (double)currentCell;
                }
            }

            if (currentSku == null || currentAmount == null)
            {
                continue;
            }

            Product product = new(currentSku)
            {
                Name = $"Распознанный артикул"
            };

            if (readResult.ContainsKey(product))
            {
                readResult[product] += currentAmount.Value;
            }

            else
            {
                readResult.Add(product, currentAmount.Value);
            }
        }

        return readResult;
    }

    public IEnumerable<(string, Dictionary<Product, double>)>
        ReadProducts(IEnumerable<Worksheet> worksheets)
    {
        List<(string, Dictionary<Product, double>)> result = new();
        foreach (Worksheet worksheet in worksheets)
        {
            if (!worksheet.IsValidSource())
            {
                continue;
            }

            string wbName = Path.GetFileNameWithoutExtension(
                worksheet.Parent.Name);

            Range amountCell = worksheet.Cells.Find(headers["Amount"]);
            Range headerRow = amountCell.EntireRow;
            Range skuCell = headerRow.Find(headers["Sku"]),
                productLineCell = headerRow.Find(headers["ProductLine"]),
                nameCell = headerRow.Find(headers["Name"]),
                measureCell = headerRow.Find(headers["Measure"]);

            var lastRowIndex = worksheet.Cells[worksheet.Rows.Count, skuCell.Column]
                .End[XlDirection.xlUp].Row;

            Dictionary<Product, double> readResult = new();            

            for (int row = amountCell.Row + 1; row <= lastRowIndex; row++)
            {
                double? amount = worksheet.Cells[row, amountCell.Column].Value2 as double?;

                if (amount != null && amount.Value != 0)
                {
                    object productLine = worksheet.Cells[row, productLineCell.Column].Value2;
                    object name = worksheet.Cells[row, nameCell.Column].Value2;
                    object sku = worksheet.Cells[row, skuCell.Column].Value2;
                    object measure = worksheet.Cells[row, measureCell.Column].Value2;
                    var productMeasure = measure?.ToString() switch
                    {
                        "м" => Measure.M,
                        "шт" => Measure.P,
                        "м2" => Measure.M2,
                        "кг" => Measure.Kg,
                        _ => Measure.P,
                    };
                    if (productLine == null || name == null || sku == null)
                        continue;

                    if (!ProductSku.TryParse(sku.ToString(), out _))
                        continue;

                    Product p = new(sku.ToString())
                    {                        
                        ProductLines = new List<string>() { productLine.ToString() },
                        Name = name.ToString().Split('\n').First(),
                        ProductMeasure = productMeasure
                    };

                    if (readResult.ContainsKey(p))
                    {
                        readResult[p] += amount.Value;
                    }

                    else
                    {
                        readResult.Add(p, amount.Value);
                    }
                }

            }

            result.Add((wbName, readResult));
        }
        return result.OrderBy(x => x.Item1);
    }

    public IEnumerable<(string, Dictionary<Product, double>)> ReadProducts(string[] files)
    {
        HashSet<string> openedFiles = RhSolutionsAddIn.Excel.Workbooks
            .Cast<Workbook>()
            .Select(wb => wb.FullName)
            .ToHashSet();
        _progressBar = new("Открываю исходные файлы...", files.Length);
        List<Worksheet> worksheets = new();

        _application.ScreenUpdating = false;
        foreach (string file in files)
        {
            Workbook wb = _application.Workbooks.Open(file);
            worksheets.Add(wb.ActiveSheet);
            _progressBar.Update();
        }
        _application.ScreenUpdating = true;
        var result = ReadProducts(worksheets);
        foreach (var ws in worksheets)
        {
            string file = (string)ws.Parent.FullName;
            if (!openedFiles.Contains(file))
            {
                ws.Parent.Close(SaveChanges: false);
            }
        }
        return result;
    }

    public void Dispose()
    {
        _progressBar?.Dispose();
    }
}