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
|
using System.Data;
using ClosedXML.Excel;
using RhSolutions.SkuParser.Abstractions;
using RhSolutions.SkuParser.Models;
namespace RhSolutions.SkuParser.Api.Services;
public class BsExcelParser : ISkuParser
{
private readonly IConfiguration configuration;
private readonly ILogger<BsExcelParser> logger;
private const int rowsLookupCount = 20;
private const decimal vat = 1.2M;
public BsExcelParser(IConfiguration configuration, ILogger<BsExcelParser> logger)
{
this.configuration = configuration;
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 headers = configuration.GetSection("Headers");
ws.AutoFilter.Clear();
var cells = configuration.GetSection("Headers")
.GetChildren()
.ToDictionary(x => x.Path, x => ws.Range(1, 1, rowsLookupCount, ws.LastCellUsed().Address.ColumnNumber)
.Search(x.Value)
.FirstOrDefault())
.OrderBy(x => x.Value?.Address.ColumnNumber ?? int.MaxValue);
if (cells == null || cells.Any(x => x.Value == null))
{
throw new DataException($"В рабочей книге отсуствует таблица с необходимыми заголовками: {file.Name}");
}
var firstCell = cells.First().Value;
var lastCell = cells.Last().Value?.WorksheetColumn().LastCellUsed();
var table = ws.Range(firstCell, lastCell).AsTable();
var rows = table.DataRange.Rows();
Dictionary<Product, double> result = new();
foreach (var row in rows.Where(r => r.Field(headers["Quantity"]).Value.IsNumber))
{
Product product = new()
{
Sku = row.Field(headers["Sku"]).GetString(),
ProductLine = row.Field(headers["ProductLine"]).GetString() switch
{
"RAUTITAN" => ProductLine.RAUTITAN,
"RAUTHERM S" => ProductLine.RAUTHERMS,
_ => null
},
Name = row.Field(headers["Name"]).GetString(),
Price = decimal.TryParse(row.Field(headers["Price"]).GetString(), out decimal value)
? Math.Round(value * vat, 2)
: null
};
double quantity = double.TryParse(row.Field(headers["Quantity"]).GetString(), out double q) ? q : 0;
if (result.ContainsKey(product))
{
result[product] += quantity;
}
else
{
result.Add(product, 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;
}
}
|
