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

190 lines
6.1 KiB
C#
Raw Permalink Normal View History

2023-04-06 08:29:39 +03:00
using System.IO;
#if !NET472
using System.Runtime.Versioning;
using RhSolutions.Tools;
#endif
namespace RhSolutions.Services;
#if !NET472
[SupportedOSPlatform("windows")]
#endif
2023-04-20 09:37:07 +03:00
public class ExcelReader : IReader, IDisposable
2023-04-06 08:29:39 +03:00
{
private ProgressBar _progressBar;
private readonly Dictionary<string, string> headers;
private readonly Application _application;
2023-04-20 09:37:07 +03:00
public ExcelReader(Application application, IAddInConfiguration configuration)
2023-04-06 08:29:39 +03:00
{
_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))
2023-04-06 08:29:39 +03:00
{
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 = $"Распознанный артикул"
};
2023-04-06 08:29:39 +03:00
if (readResult.ContainsKey(product))
{
readResult[product] += currentAmount.Value;
}
else
{
readResult.Add(product, currentAmount.Value);
}
}
return readResult;
}
2024-05-27 23:39:43 +03:00
public IEnumerable<(string, Dictionary<Product, double>)>
2023-04-06 08:29:39 +03:00
ReadProducts(IEnumerable<Worksheet> worksheets)
{
List<(string, Dictionary<Product, double>)> result = new();
foreach (Worksheet worksheet in worksheets)
{
if (!worksheet.IsValidSource())
2023-04-06 08:29:39 +03:00
{
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"]);
2023-07-25 08:44:36 +03:00
var lastRowIndex = worksheet.Cells[worksheet.Rows.Count, skuCell.Column]
2023-04-06 08:29:39 +03:00
.End[XlDirection.xlUp].Row;
Dictionary<Product, double> readResult = new();
2023-07-25 08:44:36 +03:00
for (int row = amountCell.Row + 1; row <= lastRowIndex; row++)
2023-04-06 08:29:39 +03:00
{
2023-07-25 08:44:36 +03:00
double? amount = worksheet.Cells[row, amountCell.Column].Value2 as double?;
2023-04-06 08:29:39 +03:00
if (amount != null && amount.Value != 0)
{
2023-07-25 08:44:36 +03:00
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
2023-04-20 07:18:16 +03:00
{
2023-04-20 09:39:25 +03:00
"м" => Measure.M,
"шт" => Measure.P,
"м2" => Measure.M2,
"кг" => Measure.Kg,
_ => Measure.P,
};
2023-04-20 07:18:16 +03:00
if (productLine == null || name == null || sku == null)
2023-04-06 08:29:39 +03:00
continue;
if (!ProductSku.TryParse(sku.ToString(), out _))
2023-04-06 08:29:39 +03:00
continue;
2023-05-12 09:54:28 +03:00
Product p = new(sku.ToString())
{
ProductLines = new List<string>() { productLine.ToString() },
2023-08-04 15:41:40 +03:00
Name = name.ToString().Split('\n').First(),
2023-04-20 07:18:16 +03:00
ProductMeasure = productMeasure
2023-04-06 08:29:39 +03:00
};
if (readResult.ContainsKey(p))
{
readResult[p] += amount.Value;
}
else
{
readResult.Add(p, amount.Value);
}
}
}
result.Add((wbName, readResult));
}
2024-05-27 23:39:43 +03:00
return result.OrderBy(x => x.Item1);
2023-04-06 08:29:39 +03:00
}
2024-05-27 23:39:43 +03:00
public IEnumerable<(string, Dictionary<Product, double>)> ReadProducts(string[] files)
2023-04-06 08:29:39 +03:00
{
HashSet<string> openedFiles = RhSolutionsAddIn.Excel.Workbooks
.Cast<Workbook>()
.Select(wb => wb.FullName)
.ToHashSet();
2023-04-06 08:29:39 +03:00
_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);
}
2023-04-06 08:29:39 +03:00
}
return result;
}
public void Dispose()
{
2023-04-07 07:25:03 +03:00
_progressBar?.Dispose();
2023-04-06 08:29:39 +03:00
}
}