From cb5fee18918b5a29ccff23a0fb74bb13151f2e42 Mon Sep 17 00:00:00 2001 From: Sergey Chebotar Date: Wed, 2 Feb 2022 18:02:17 +0300 Subject: Rename Abstract tool --- src/PriceListTools/AbstractTool.cs | 175 +++++++++++++++++++++++++++++++++++++ 1 file changed, 175 insertions(+) create mode 100644 src/PriceListTools/AbstractTool.cs (limited to 'src/PriceListTools/AbstractTool.cs') diff --git a/src/PriceListTools/AbstractTool.cs b/src/PriceListTools/AbstractTool.cs new file mode 100644 index 0000000..77dc63c --- /dev/null +++ b/src/PriceListTools/AbstractTool.cs @@ -0,0 +1,175 @@ +using ExcelDna.Integration; +using Microsoft.Office.Interop.Excel; +using System; +using System.Collections.Generic; +using System.Windows.Forms; +using Application = Microsoft.Office.Interop.Excel.Application; + +namespace RehauSku.PriceListTools +{ + internal abstract class AbstractTool + { + protected private Application ExcelApp = (Application)ExcelDnaUtil.Application; + protected private Target TargetFile; + + public void OpenNewPrice() + { + Workbook wb = ExcelApp.Workbooks.Open(RegistryUtil.PriceListPath); + + try + { + TargetFile = new Target(wb); + } + + catch (Exception ex) + { + MessageBox.Show + (ex.Message, + "Ошибка открытия шаблонного прайс-листа", + MessageBoxButtons.OK, + MessageBoxIcon.Information); + wb.Close(); + throw ex; + } + } + + protected private void FillColumnsWithDictionary(KeyValuePair positionAmount, params int[] columns) + { + int? row = GetPositionRow(positionAmount.Key.Sku, positionAmount.Key.Group, TargetFile.skuCell.Column); + + if (row != null) + { + foreach (int column in columns) + { + Range sumCell = TargetFile.Sheet.Cells[row, column]; + + if (sumCell.Value2 == null) + { + sumCell.Value2 = positionAmount.Value; + } + + else + { + sumCell.Value2 += positionAmount.Value; + } + } + } + + else + { + string sku = positionAmount.Key.Sku.Substring(1, 6); + + row = GetPositionRow(sku, positionAmount.Key.Group, TargetFile.skuCell.Column); + + if (row != null) + { + foreach (int column in columns) + { + Range amountCell = TargetFile.Sheet.Cells[row, column]; + + if (amountCell.Value2 == null) + { + amountCell.Value2 = positionAmount.Value; + } + + else + { + amountCell.Value2 += positionAmount.Value; + } + + Range oldSkuCell = TargetFile.Sheet.Cells[row, TargetFile.oldSkuCell.Column]; + oldSkuCell.Value2 = positionAmount.Key.Sku; + } + } + + else + { + FillMissing(positionAmount, columns); + } + } + } + + protected private void FillMissing(KeyValuePair positionAmount, params int[] columns) + { + Range foundCell = TargetFile.oldSkuCell.EntireColumn.Find(positionAmount.Key.Sku); + int row; + + if (foundCell == null) + { + row = TargetFile.Sheet.Cells[TargetFile.Sheet.Rows.Count, TargetFile.skuCell.Column] + .End[XlDirection.xlUp] + .Row + 1; + + TargetFile.Sheet.Rows[row] + .EntireRow + .Insert(XlInsertShiftDirection.xlShiftDown, XlInsertFormatOrigin.xlFormatFromLeftOrAbove); + + Range previous = TargetFile.Sheet.Rows[row - 1]; + Range current = TargetFile.Sheet.Rows[row]; + + previous.Copy(current); + current.ClearContents(); + + TargetFile.Sheet.Cells[row, TargetFile.groupCell.Column].Value2 = positionAmount.Key.Group; + TargetFile.Sheet.Cells[row, TargetFile.oldSkuCell.Column].Value2 = positionAmount.Key.Sku; + TargetFile.Sheet.Cells[row, TargetFile.nameCell.Column].Value2 = positionAmount.Key.Name; + TargetFile.Sheet.Cells[row, TargetFile.skuCell.Column].Value2 = "Не найден"; + } + + else + { + row = foundCell.Row; + } + + foreach (int column in columns) + { + if (TargetFile.Sheet.Cells[row, column].Value2 == null) + { + TargetFile.Sheet.Cells[row, column].Value2 = positionAmount.Value; + } + + else + { + TargetFile.Sheet.Cells[row, column].Value2 += positionAmount.Value; + } + } + } + + protected private int? GetPositionRow(string sku, string group, int column) + { + int? row = null; + Range foundCell = TargetFile.Sheet.Columns[column].Find(sku); + string foundGroupValue; + + if (foundCell == null) return null; + + else + { + row = foundCell.Row; + foundGroupValue = TargetFile.Sheet.Cells[foundCell.Row, TargetFile.groupCell.Column].Value2.ToString(); + } + + if (string.IsNullOrEmpty(group) || group.Equals(foundGroupValue)) + return row; + + else + while (true) + { + foundCell = TargetFile.skuCell.EntireColumn.FindNext(foundCell); + if (foundCell == null) return row; + + foundGroupValue = TargetFile.Sheet.Cells[foundCell.Row, TargetFile.groupCell.Column].Value2.ToString(); + if (group.Equals(foundGroupValue)) return foundCell.Row; + } + } + + protected private void FilterByAmount() + { + AutoFilter filter = TargetFile.Sheet.AutoFilter; + int startColumn = filter.Range.Column; + + filter.Range.AutoFilter(TargetFile.amountCell.Column - startColumn + 1, "<>"); + TargetFile.Sheet.Range["A1"].Activate(); + } + } +} \ No newline at end of file -- cgit v1.2.3 From 1dfbfaa461479a14cd75c6fc5f6a5082e61c6371 Mon Sep 17 00:00:00 2001 From: Sergey Chebotar Date: Wed, 2 Feb 2022 18:05:49 +0300 Subject: Rename Abstract PriceList inheritors --- src/PriceListTools/AbstractTool.cs | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) (limited to 'src/PriceListTools/AbstractTool.cs') diff --git a/src/PriceListTools/AbstractTool.cs b/src/PriceListTools/AbstractTool.cs index 77dc63c..a86fd95 100644 --- a/src/PriceListTools/AbstractTool.cs +++ b/src/PriceListTools/AbstractTool.cs @@ -10,7 +10,7 @@ namespace RehauSku.PriceListTools internal abstract class AbstractTool { protected private Application ExcelApp = (Application)ExcelDnaUtil.Application; - protected private Target TargetFile; + protected private TargetPriceList TargetFile; public void OpenNewPrice() { @@ -18,7 +18,7 @@ namespace RehauSku.PriceListTools try { - TargetFile = new Target(wb); + TargetFile = new TargetPriceList(wb); } catch (Exception ex) -- cgit v1.2.3 From 99d1fb6740e93d7d81f0171885ac8f788b56dcfc Mon Sep 17 00:00:00 2001 From: Sergey Chebotar Date: Thu, 3 Feb 2022 21:44:24 +0300 Subject: Rename method --- src/PriceListTools/AbstractTool.cs | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'src/PriceListTools/AbstractTool.cs') diff --git a/src/PriceListTools/AbstractTool.cs b/src/PriceListTools/AbstractTool.cs index a86fd95..0d185f9 100644 --- a/src/PriceListTools/AbstractTool.cs +++ b/src/PriceListTools/AbstractTool.cs @@ -33,7 +33,7 @@ namespace RehauSku.PriceListTools } } - protected private void FillColumnsWithDictionary(KeyValuePair positionAmount, params int[] columns) + protected private void FillPositionAmountToColumns(KeyValuePair positionAmount, params int[] columns) { int? row = GetPositionRow(positionAmount.Key.Sku, positionAmount.Key.Group, TargetFile.skuCell.Column); -- cgit v1.2.3 From 6e889419e2658a3a80fa00582314f1428f6052e5 Mon Sep 17 00:00:00 2001 From: Sergey Chebotar Date: Fri, 4 Feb 2022 09:17:12 +0300 Subject: Add Result Statusbar message --- src/PriceListTools/AbstractTool.cs | 94 +++++++++++++++++++++++++------------- 1 file changed, 61 insertions(+), 33 deletions(-) (limited to 'src/PriceListTools/AbstractTool.cs') diff --git a/src/PriceListTools/AbstractTool.cs b/src/PriceListTools/AbstractTool.cs index 0d185f9..b4e34cc 100644 --- a/src/PriceListTools/AbstractTool.cs +++ b/src/PriceListTools/AbstractTool.cs @@ -1,9 +1,11 @@ using ExcelDna.Integration; using Microsoft.Office.Interop.Excel; +using RehauSku.Interface; using System; using System.Collections.Generic; using System.Windows.Forms; using Application = Microsoft.Office.Interop.Excel.Application; +using ProgressBar = RehauSku.Interface.ProgressBar; namespace RehauSku.PriceListTools { @@ -11,6 +13,8 @@ namespace RehauSku.PriceListTools { protected private Application ExcelApp = (Application)ExcelDnaUtil.Application; protected private TargetPriceList TargetFile; + protected private ResultBar ResultBar { get; set; } + protected private ProgressBar ProgressBar { get; set; } public void OpenNewPrice() { @@ -53,72 +57,96 @@ namespace RehauSku.PriceListTools sumCell.Value2 += positionAmount.Value; } } + + ResultBar.IncrementSuccess(); + return; } - else + if (TargetFile.oldSkuCell != null) { - string sku = positionAmount.Key.Sku.Substring(1, 6); + Range foundCell = TargetFile.oldSkuCell.EntireColumn.Find(positionAmount.Key.Sku); - row = GetPositionRow(sku, positionAmount.Key.Group, TargetFile.skuCell.Column); - - if (row != null) + if (foundCell != null) { + row = foundCell.Row; + foreach (int column in columns) { - Range amountCell = TargetFile.Sheet.Cells[row, column]; - - if (amountCell.Value2 == null) + if (TargetFile.Sheet.Cells[row, column].Value2 == null) { - amountCell.Value2 = positionAmount.Value; + TargetFile.Sheet.Cells[row, column].Value2 = positionAmount.Value; } else { - amountCell.Value2 += positionAmount.Value; + TargetFile.Sheet.Cells[row, column].Value2 += positionAmount.Value; } - - Range oldSkuCell = TargetFile.Sheet.Cells[row, TargetFile.oldSkuCell.Column]; - oldSkuCell.Value2 = positionAmount.Key.Sku; } + + ResultBar.IncrementReplaced(); + return; } + } - else + string sku = positionAmount.Key.Sku.Substring(1, 6); + row = GetPositionRow(sku, positionAmount.Key.Group, TargetFile.skuCell.Column); + + if (row != null) + { + foreach (int column in columns) { - FillMissing(positionAmount, columns); + Range amountCell = TargetFile.Sheet.Cells[row, column]; + + if (amountCell.Value2 == null) + { + amountCell.Value2 = positionAmount.Value; + } + + else + { + amountCell.Value2 += positionAmount.Value; + } } + + ResultBar.IncrementReplaced(); + return; + } + + else + { + FillMissing(positionAmount, columns); + ResultBar.IncrementNotFound(); } } protected private void FillMissing(KeyValuePair positionAmount, params int[] columns) { - Range foundCell = TargetFile.oldSkuCell.EntireColumn.Find(positionAmount.Key.Sku); - int row; + int row = TargetFile.Sheet.Cells[TargetFile.Sheet.Rows.Count, TargetFile.skuCell.Column] + .End[XlDirection.xlUp] + .Row + 1; - if (foundCell == null) - { - row = TargetFile.Sheet.Cells[TargetFile.Sheet.Rows.Count, TargetFile.skuCell.Column] - .End[XlDirection.xlUp] - .Row + 1; + TargetFile.Sheet.Rows[row] + .EntireRow + .Insert(XlInsertShiftDirection.xlShiftDown, XlInsertFormatOrigin.xlFormatFromLeftOrAbove); - TargetFile.Sheet.Rows[row] - .EntireRow - .Insert(XlInsertShiftDirection.xlShiftDown, XlInsertFormatOrigin.xlFormatFromLeftOrAbove); + Range previous = TargetFile.Sheet.Rows[row - 1]; + Range current = TargetFile.Sheet.Rows[row]; - Range previous = TargetFile.Sheet.Rows[row - 1]; - Range current = TargetFile.Sheet.Rows[row]; + previous.Copy(current); + current.ClearContents(); - previous.Copy(current); - current.ClearContents(); + TargetFile.Sheet.Cells[row, TargetFile.groupCell.Column].Value2 = positionAmount.Key.Group; + TargetFile.Sheet.Cells[row, TargetFile.nameCell.Column].Value2 = positionAmount.Key.Name; - TargetFile.Sheet.Cells[row, TargetFile.groupCell.Column].Value2 = positionAmount.Key.Group; - TargetFile.Sheet.Cells[row, TargetFile.oldSkuCell.Column].Value2 = positionAmount.Key.Sku; - TargetFile.Sheet.Cells[row, TargetFile.nameCell.Column].Value2 = positionAmount.Key.Name; + if (TargetFile.oldSkuCell != null) + { TargetFile.Sheet.Cells[row, TargetFile.skuCell.Column].Value2 = "Не найден"; + TargetFile.Sheet.Cells[row, TargetFile.oldSkuCell.Column].Value2 = positionAmount.Key.Sku; } else { - row = foundCell.Row; + TargetFile.Sheet.Cells[row, TargetFile.skuCell.Column].Value2 = positionAmount.Key.Sku; } foreach (int column in columns) -- cgit v1.2.3