1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
|
using ClosedXML.Excel;
using Microsoft.Extensions.Logging;
using RhSolutions.SkuParser.Abstractions;
using RhSolutions.SkuParser.Models;
namespace RhSolutions.SkuParser.Services;
public class CommonExcelParser : ISkuParser
{
private readonly ILogger<CommonExcelParser> logger;
public CommonExcelParser(ILogger<CommonExcelParser> logger)
{
this.logger = logger;
}
public Dictionary<Product, double> ParseProducts(IFormFile file)
{
using XLWorkbook workbook = new(file.OpenReadStream());
IXLWorksheet ws = workbook.Worksheets.FirstOrDefault(ws => ws.TabActive) ?? workbook.Worksheet(1);
var leftTop = ws.FirstCellUsed()?.Address;
var rightBottom = ws.LastCellUsed()?.Address;
if (new object?[] { leftTop, rightBottom }.Any(x => x == null))
{
throw new ArgumentException($"Таблица пуста: {file.FileName}");
}
var lookupRange = ws.Range(leftTop, rightBottom).RangeUsed();
var columns = lookupRange.Columns();
var skuColumnQuantity = columns
.Select(column => new
{
Column = column,
Products = column.CellsUsed()
.Select(cell => !cell.HasFormula && Product.TryParse(cell.Value.ToString(), out Product? p) ? p : null)
})
.Select(c => new { c.Column, SkuCount = c.Products.Count(p => p != null) })
.Aggregate((l, r) => l.SkuCount > r.SkuCount ? l : r);
var skuColumn = skuColumnQuantity.SkuCount > 0 ? skuColumnQuantity.Column : null;
if (skuColumn == null)
{
throw new ArgumentException($"Столбец с артикулом не определен: {file.FileName}");
}
var quantityColumn = lookupRange.Columns().Skip(skuColumn.ColumnNumber())
.Select(column => new
{
Column = column,
IsColumnWithNumbers = column.CellsUsed()
.Count(cell => cell.Value.IsNumber == true) > column.CellsUsed().Count() / 4
})
.First(x => x.IsColumnWithNumbers)
.Column;
if (quantityColumn == null)
{
throw new ArgumentException($"Столбец с количеством не определен: {file.FileName}");
}
Dictionary<Product, double> result = new();
var rows = quantityColumn.CellsUsed().Select(x => x.Address.RowNumber);
foreach (var row in rows)
{
var quantity = quantityColumn.Cell(row).Value;
var sku = skuColumn.Cell(row).Value;
if (quantity.IsNumber
&& Product.TryParse(sku.ToString(), out Product? p)
&& p != null)
{
if (result.ContainsKey(p))
{
result[p] += (double)quantity;
}
else
{
result.Add(p, (double)quantity);
}
}
}
logger.LogInformation("Parsed {Count} products from file {FileName}.", result.Count, file.FileName);
foreach (var (product, quantity) in result)
{
logger.LogInformation("Product: {Sku}, Name: {Name}, Quantity: {Quantity}, Price: {Price}, ProductLine: {ProductLine}",
product.Sku, product.Name, quantity, product.Price, product.ProductLine);
}
return result;
}
}
|
