From a542bfb7f4ceee8b49cf8fcadf64ffb72cc97da5 Mon Sep 17 00:00:00 2001 From: Serghei Cebotari Date: Tue, 14 Jan 2025 14:01:01 +0000 Subject: Common Parsing Method --- .../Services/CommonCsvParser.cs | 26 +++++++ .../Services/CommonExcelParser.cs | 80 ++++++++++++++++++++++ RhSolutions.SkuParser.Api/Services/CsvParser.cs | 24 ------- RhSolutions.SkuParser.Api/Services/ExcelParser.cs | 76 -------------------- RhSolutions.SkuParser.Api/Services/ISkuParser.cs | 7 -- 5 files changed, 106 insertions(+), 107 deletions(-) create mode 100644 RhSolutions.SkuParser.Api/Services/CommonCsvParser.cs create mode 100644 RhSolutions.SkuParser.Api/Services/CommonExcelParser.cs delete mode 100644 RhSolutions.SkuParser.Api/Services/CsvParser.cs delete mode 100644 RhSolutions.SkuParser.Api/Services/ExcelParser.cs delete mode 100644 RhSolutions.SkuParser.Api/Services/ISkuParser.cs (limited to 'RhSolutions.SkuParser.Api/Services') diff --git a/RhSolutions.SkuParser.Api/Services/CommonCsvParser.cs b/RhSolutions.SkuParser.Api/Services/CommonCsvParser.cs new file mode 100644 index 0000000..e88ba25 --- /dev/null +++ b/RhSolutions.SkuParser.Api/Services/CommonCsvParser.cs @@ -0,0 +1,26 @@ +using System.Globalization; +using CsvHelper; +using CsvHelper.Configuration; +using RhSolutions.SkuParser.Abstractions; +using RhSolutions.SkuParser.Models; + +namespace RhSolutions.SkuParser.Services; + +/// +/// Парсер артикулов и их количества из файлов *.csv +/// +public class CommonCsvParser : ISkuParser +{ + public Dictionary ParseProducts(IFormFile file) + { + using StreamReader reader = new(file.OpenReadStream()); + var config = new CsvConfiguration(CultureInfo.GetCultureInfo("ru-RU")) + { + HasHeaderRecord = false, + }; + using CsvReader csvReader = new(reader, config); + + return csvReader.GetRecords() + .ToDictionary(pq => new Product() { Sku = pq.Product.Sku }, pq => pq.Quantity); + } +} diff --git a/RhSolutions.SkuParser.Api/Services/CommonExcelParser.cs b/RhSolutions.SkuParser.Api/Services/CommonExcelParser.cs new file mode 100644 index 0000000..206200d --- /dev/null +++ b/RhSolutions.SkuParser.Api/Services/CommonExcelParser.cs @@ -0,0 +1,80 @@ +using ClosedXML.Excel; +using RhSolutions.SkuParser.Abstractions; +using RhSolutions.SkuParser.Models; + +namespace RhSolutions.SkuParser.Services; + +public class CommonExcelParser : ISkuParser +{ + public Dictionary ParseProducts(IFormFile file) + { + using XLWorkbook workbook = new(file.OpenReadStream()); + IXLWorksheet ws = 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 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); + } + } + } + + return result; + } +} diff --git a/RhSolutions.SkuParser.Api/Services/CsvParser.cs b/RhSolutions.SkuParser.Api/Services/CsvParser.cs deleted file mode 100644 index 2776721..0000000 --- a/RhSolutions.SkuParser.Api/Services/CsvParser.cs +++ /dev/null @@ -1,24 +0,0 @@ -using System.Globalization; -using CsvHelper; -using CsvHelper.Configuration; -using RhSolutions.SkuParser.Models; - -namespace RhSolutions.SkuParser.Services; - -/// -/// Парсер артикулов и их количества из файлов *.csv -/// -public class CsvParser : ISkuParser -{ - public IEnumerable ParseProducts(IFormFile file) - { - using StreamReader reader = new(file.OpenReadStream()); - var config = new CsvConfiguration(CultureInfo.GetCultureInfo("ru-RU")) - { - HasHeaderRecord = false, - }; - using CsvReader csvReader = new(reader, config); - - return csvReader.GetRecords().ToList(); - } -} diff --git a/RhSolutions.SkuParser.Api/Services/ExcelParser.cs b/RhSolutions.SkuParser.Api/Services/ExcelParser.cs deleted file mode 100644 index fec3885..0000000 --- a/RhSolutions.SkuParser.Api/Services/ExcelParser.cs +++ /dev/null @@ -1,76 +0,0 @@ -using ClosedXML.Excel; -using RhSolutions.SkuParser.Models; - -namespace RhSolutions.SkuParser.Services; - -public class ExcelParser : ISkuParser -{ - public IEnumerable ParseProducts(IFormFile file) - { - using XLWorkbook workbook = new(file.OpenReadStream()); - IXLWorksheet ws = 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}"); - } - - List 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)) - { - ProductQuantity pq = new() - { - Product = p!, - Quantity = quantity.GetNumber() - }; - result.Add(pq); - } - } - - return result; - } -} diff --git a/RhSolutions.SkuParser.Api/Services/ISkuParser.cs b/RhSolutions.SkuParser.Api/Services/ISkuParser.cs deleted file mode 100644 index 4329135..0000000 --- a/RhSolutions.SkuParser.Api/Services/ISkuParser.cs +++ /dev/null @@ -1,7 +0,0 @@ -using RhSolutions.SkuParser.Models; - -namespace RhSolutions.SkuParser.Services; -public interface ISkuParser -{ - public IEnumerable ParseProducts(IFormFile file); -} -- cgit v1.2.3