From 72ac236b15603e84f18ec346749186b6cb2c2bdf Mon Sep 17 00:00:00 2001 From: Sergey Chebotar Date: Thu, 27 Jan 2022 10:22:30 +0300 Subject: Refactoring tolls classes --- src/PriceListTools/Source.cs | 52 ++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 52 insertions(+) create mode 100644 src/PriceListTools/Source.cs (limited to 'src/PriceListTools/Source.cs') diff --git a/src/PriceListTools/Source.cs b/src/PriceListTools/Source.cs new file mode 100644 index 0000000..40ac257 --- /dev/null +++ b/src/PriceListTools/Source.cs @@ -0,0 +1,52 @@ +using Microsoft.Office.Interop.Excel; +using System; +using System.Collections.Generic; + +namespace RehauSku.PriceListTools +{ + internal class Source : PriceList + { + public Dictionary SkuAmount { get; private set; } + + public Source(Workbook workbook) + { + Sheet = workbook.ActiveSheet; + Name = workbook.Name; + + amountCell = Sheet.Cells.Find(amountHeader); + skuCell = Sheet.Cells.Find(skuHeader); + groupCell = Sheet.Cells.Find(groupHeader); + + if (amountCell == null || skuCell == null || groupCell == null) + { + throw new ArgumentException($"Лист { Name } не распознан"); + } + + CreateAmountDict(); + } + + private void CreateAmountDict() + { + SkuAmount = new Dictionary(); + + object[,] amountColumn = Sheet.Columns[amountCell.Column].Value2; + object[,] skuColumn = Sheet.Columns[skuCell.Column].Value2; + + for (int row = amountCell.Row + 1; row < amountColumn.GetLength(0); row++) + { + object amount = amountColumn[row, 1]; + object sku = skuColumn[row, 1]; + + if (amount != null && (double)amount != 0) + { + if (SkuAmount.ContainsKey(sku.ToString())) + SkuAmount[sku.ToString()] += (double)amount; + + else + SkuAmount.Add(sku.ToString(), (double)amount); + } + } + } + } +} + -- cgit v1.2.3 From 935d48fc5fe264218b39b335e1fc5232af5dae61 Mon Sep 17 00:00:00 2001 From: Sergey Chebotar Date: Thu, 27 Jan 2022 17:34:03 +0300 Subject: Complete tools refactoring --- src/PriceListTools/Source.cs | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'src/PriceListTools/Source.cs') diff --git a/src/PriceListTools/Source.cs b/src/PriceListTools/Source.cs index 40ac257..92de551 100644 --- a/src/PriceListTools/Source.cs +++ b/src/PriceListTools/Source.cs @@ -19,7 +19,7 @@ namespace RehauSku.PriceListTools if (amountCell == null || skuCell == null || groupCell == null) { - throw new ArgumentException($"Лист { Name } не распознан"); + throw new ArgumentException($"Файл {Name} не распознан"); } CreateAmountDict(); -- cgit v1.2.3 From 711cc313e0eaed646c5058ef294de6d89770e352 Mon Sep 17 00:00:00 2001 From: Sergey Chebotar Date: Fri, 28 Jan 2022 11:41:35 +0300 Subject: Add complete position class --- src/PriceListTools/Source.cs | 46 ++++++++++++++++++++++++++++++++++---------- 1 file changed, 36 insertions(+), 10 deletions(-) (limited to 'src/PriceListTools/Source.cs') diff --git a/src/PriceListTools/Source.cs b/src/PriceListTools/Source.cs index 92de551..8c71130 100644 --- a/src/PriceListTools/Source.cs +++ b/src/PriceListTools/Source.cs @@ -6,7 +6,7 @@ namespace RehauSku.PriceListTools { internal class Source : PriceList { - public Dictionary SkuAmount { get; private set; } + public Dictionary PositionAmount { get; private set; } public Source(Workbook workbook) { @@ -16,37 +16,63 @@ namespace RehauSku.PriceListTools amountCell = Sheet.Cells.Find(amountHeader); skuCell = Sheet.Cells.Find(skuHeader); groupCell = Sheet.Cells.Find(groupHeader); + nameCell = Sheet.Cells.Find(nameHeader); - if (amountCell == null || skuCell == null || groupCell == null) + if (amountCell == null || skuCell == null || groupCell == null || nameCell == null) { throw new ArgumentException($"Файл {Name} не распознан"); } - CreateAmountDict(); + CreatePositionsDict(); } - private void CreateAmountDict() + private void CreatePositionsDict() { - SkuAmount = new Dictionary(); + PositionAmount = new Dictionary(); - object[,] amountColumn = Sheet.Columns[amountCell.Column].Value2; - object[,] skuColumn = Sheet.Columns[skuCell.Column].Value2; + var aColumn = amountCell.EntireColumn; + + object[,] amountColumn = amountCell.EntireColumn.Value2; + object[,] skuColumn = skuCell.EntireColumn.Value2; + object[,] nameColumn = nameCell.EntireColumn.Value2; + object[,] groupColumn = nameCell.EntireColumn.Value2; for (int row = amountCell.Row + 1; row < amountColumn.GetLength(0); row++) { object amount = amountColumn[row, 1]; + object group = groupColumn[row, 1]; + object name = nameColumn[row, 1]; object sku = skuColumn[row, 1]; if (amount != null && (double)amount != 0) { - if (SkuAmount.ContainsKey(sku.ToString())) - SkuAmount[sku.ToString()] += (double)amount; + Position p = new Position(group.ToString(), sku.ToString(), name.ToString()); + if (PositionAmount.ContainsKey(p)) + { + PositionAmount[p] += (double)amount; + } else - SkuAmount.Add(sku.ToString(), (double)amount); + { + PositionAmount.Add(p, (double)amount); + } } } } } + + public class Position + { + public string SkuGroup { get; private set; } + public string Sku { get; private set; } + public string Name { get; private set; } + + public Position(string group, string sku, string name) + { + SkuGroup = group; + Sku = sku; + Name = name; + } + } } -- cgit v1.2.3 From 952ca22316b4e4ac25004cd453baac1afac5adaf Mon Sep 17 00:00:00 2001 From: Sergey Chebotar Date: Fri, 28 Jan 2022 12:27:10 +0300 Subject: Implement filling positions to correct sku group --- src/PriceListTools/Source.cs | 16 +--------------- 1 file changed, 1 insertion(+), 15 deletions(-) (limited to 'src/PriceListTools/Source.cs') diff --git a/src/PriceListTools/Source.cs b/src/PriceListTools/Source.cs index 8c71130..fd64012 100644 --- a/src/PriceListTools/Source.cs +++ b/src/PriceListTools/Source.cs @@ -35,7 +35,7 @@ namespace RehauSku.PriceListTools object[,] amountColumn = amountCell.EntireColumn.Value2; object[,] skuColumn = skuCell.EntireColumn.Value2; object[,] nameColumn = nameCell.EntireColumn.Value2; - object[,] groupColumn = nameCell.EntireColumn.Value2; + object[,] groupColumn = groupCell.EntireColumn.Value2; for (int row = amountCell.Row + 1; row < amountColumn.GetLength(0); row++) { @@ -60,19 +60,5 @@ namespace RehauSku.PriceListTools } } } - - public class Position - { - public string SkuGroup { get; private set; } - public string Sku { get; private set; } - public string Name { get; private set; } - - public Position(string group, string sku, string name) - { - SkuGroup = group; - Sku = sku; - Name = name; - } - } } -- cgit v1.2.3 From 0621ceabad9ed23409e4a8d965190580d926c077 Mon Sep 17 00:00:00 2001 From: Sergey Chebotar Date: Fri, 28 Jan 2022 12:44:37 +0300 Subject: Throw Exception on null workbook --- src/PriceListTools/Source.cs | 5 +++++ 1 file changed, 5 insertions(+) (limited to 'src/PriceListTools/Source.cs') diff --git a/src/PriceListTools/Source.cs b/src/PriceListTools/Source.cs index fd64012..c3e2231 100644 --- a/src/PriceListTools/Source.cs +++ b/src/PriceListTools/Source.cs @@ -10,6 +10,11 @@ namespace RehauSku.PriceListTools public Source(Workbook workbook) { + if (workbook == null) + { + throw new ArgumentException($"Нет рабочего файла"); + } + Sheet = workbook.ActiveSheet; Name = workbook.Name; -- cgit v1.2.3 From 9bf9e3978d33cf4b81155bd2fd3c7bf9e04a931f Mon Sep 17 00:00:00 2001 From: Sergey Chebotar Date: Fri, 28 Jan 2022 14:35:20 +0300 Subject: Refactoring --- src/PriceListTools/Source.cs | 2 -- 1 file changed, 2 deletions(-) (limited to 'src/PriceListTools/Source.cs') diff --git a/src/PriceListTools/Source.cs b/src/PriceListTools/Source.cs index c3e2231..d811e36 100644 --- a/src/PriceListTools/Source.cs +++ b/src/PriceListTools/Source.cs @@ -35,8 +35,6 @@ namespace RehauSku.PriceListTools { PositionAmount = new Dictionary(); - var aColumn = amountCell.EntireColumn; - object[,] amountColumn = amountCell.EntireColumn.Value2; object[,] skuColumn = skuCell.EntireColumn.Value2; object[,] nameColumn = nameCell.EntireColumn.Value2; -- cgit v1.2.3 From 2b3937ac2f1832ef89d28565c43464397384b571 Mon Sep 17 00:00:00 2001 From: Sergey Chebotar Date: Fri, 28 Jan 2022 15:59:47 +0300 Subject: Refactoring --- src/PriceListTools/Source.cs | 16 ++++++---------- 1 file changed, 6 insertions(+), 10 deletions(-) (limited to 'src/PriceListTools/Source.cs') diff --git a/src/PriceListTools/Source.cs b/src/PriceListTools/Source.cs index d811e36..2f1e7ce 100644 --- a/src/PriceListTools/Source.cs +++ b/src/PriceListTools/Source.cs @@ -35,20 +35,16 @@ namespace RehauSku.PriceListTools { PositionAmount = new Dictionary(); - object[,] amountColumn = amountCell.EntireColumn.Value2; - object[,] skuColumn = skuCell.EntireColumn.Value2; - object[,] nameColumn = nameCell.EntireColumn.Value2; - object[,] groupColumn = groupCell.EntireColumn.Value2; - - for (int row = amountCell.Row + 1; row < amountColumn.GetLength(0); row++) + for (int row = amountCell.Row + 1; row < Sheet.AutoFilter.Range.Rows.Count; row++) { - object amount = amountColumn[row, 1]; - object group = groupColumn[row, 1]; - object name = nameColumn[row, 1]; - object sku = skuColumn[row, 1]; + object amount = Sheet.Cells[row, amountCell.Column].Value2; if (amount != null && (double)amount != 0) { + object group = Sheet.Cells[row, groupCell.Column].Value2; + object name = Sheet.Cells[row, nameCell.Column].Value2; + object sku = Sheet.Cells[row, skuCell.Column].Value2; + Position p = new Position(group.ToString(), sku.ToString(), name.ToString()); if (PositionAmount.ContainsKey(p)) -- cgit v1.2.3 From 42c06d00e3720b8ac263d3e76c6d4fb724dca970 Mon Sep 17 00:00:00 2001 From: Sergey Chebotar Date: Fri, 28 Jan 2022 16:19:39 +0300 Subject: Move GetSourceFiles to Source class --- src/PriceListTools/Source.cs | 34 +++++++++++++++++++++++++++++++++- 1 file changed, 33 insertions(+), 1 deletion(-) (limited to 'src/PriceListTools/Source.cs') diff --git a/src/PriceListTools/Source.cs b/src/PriceListTools/Source.cs index 2f1e7ce..5013157 100644 --- a/src/PriceListTools/Source.cs +++ b/src/PriceListTools/Source.cs @@ -1,4 +1,5 @@ -using Microsoft.Office.Interop.Excel; +using ExcelDna.Integration; +using Microsoft.Office.Interop.Excel; using System; using System.Collections.Generic; @@ -31,6 +32,37 @@ namespace RehauSku.PriceListTools CreatePositionsDict(); } + public static List GetSourceLists(string[] files) + { + var ExcelApp = (Application)ExcelDnaUtil.Application; + + List sourceFiles = new List(); + + ExcelApp.ScreenUpdating = false; + foreach (string file in files) + { + Workbook wb = ExcelApp.Workbooks.Open(file); + try + { + Source priceList = new Source(wb); + sourceFiles.Add(priceList); + wb.Close(); + } + catch (Exception ex) + { + System.Windows.Forms.MessageBox.Show + (ex.Message, + "Ошибка открытия исходного прайс-листа", + System.Windows.Forms.MessageBoxButtons.OK, + System.Windows.Forms.MessageBoxIcon.Information); + wb.Close(); + } + } + ExcelApp.ScreenUpdating = true; + + return sourceFiles; + } + private void CreatePositionsDict() { PositionAmount = new Dictionary(); -- cgit v1.2.3