From 4a2ca16d8b4aa34041adb558b2db91709908aff5 Mon Sep 17 00:00:00 2001 From: Sergey Chebotar Date: Wed, 26 Jan 2022 17:41:46 +0300 Subject: Watch only current sheets in files --- src/PriceListTools/ExportTool.cs | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'src/PriceListTools/ExportTool.cs') diff --git a/src/PriceListTools/ExportTool.cs b/src/PriceListTools/ExportTool.cs index a93097d..9bf165b 100644 --- a/src/PriceListTools/ExportTool.cs +++ b/src/PriceListTools/ExportTool.cs @@ -77,7 +77,7 @@ namespace RehauSku.PriceListTools { if (SkuAmount.Count < 1) return; - PriceListSheet offer = NewPriceList.OfferSheet; + PriceListSheet offer = NewPriceList.Sheet; offer.Sheet.Activate(); int exportedValues = 0; -- cgit v1.2.3 From c0139ca228bdca91df0a66201f59f8074c6a0db6 Mon Sep 17 00:00:00 2001 From: Sergey Chebotar Date: Wed, 26 Jan 2022 17:50:07 +0300 Subject: Remove unnecessary fields --- src/PriceListTools/ExportTool.cs | 6 +++--- 1 file changed, 3 insertions(+), 3 deletions(-) (limited to 'src/PriceListTools/ExportTool.cs') diff --git a/src/PriceListTools/ExportTool.cs b/src/PriceListTools/ExportTool.cs index 9bf165b..76a5c6f 100644 --- a/src/PriceListTools/ExportTool.cs +++ b/src/PriceListTools/ExportTool.cs @@ -84,7 +84,7 @@ namespace RehauSku.PriceListTools foreach (var kvp in SkuAmount) { - Range cell = offer.Sheet.Columns[offer.skuColumnNumber].Find(kvp.Key); + Range cell = offer.Sheet.Columns[offer.skuCell.Column].Find(kvp.Key); if (cell == null) { @@ -97,7 +97,7 @@ namespace RehauSku.PriceListTools else { - Range sumCell = offer.Sheet.Cells[cell.Row, offer.amountColumnNumber]; + Range sumCell = offer.Sheet.Cells[cell.Row, offer.amountCell.Column]; if (sumCell.Value2 == null) sumCell.Value2 = kvp.Value; @@ -111,7 +111,7 @@ namespace RehauSku.PriceListTools AutoFilter filter = offer.Sheet.AutoFilter; int firstFilterColumn = filter.Range.Column; - filter.Range.AutoFilter(offer.amountColumnNumber - firstFilterColumn + 1, "<>"); + filter.Range.AutoFilter(offer.amountCell.Column - firstFilterColumn + 1, "<>"); offer.Sheet.Range["A1"].Activate(); AddIn.Excel.StatusBar = $"Экспортировано {exportedValues} строк из {SkuAmount.Count}"; -- cgit v1.2.3 From 55bbd801a5593512921ccd4671a50069896affa0 Mon Sep 17 00:00:00 2001 From: Sergey Chebotar Date: Wed, 26 Jan 2022 18:17:44 +0300 Subject: Remove PriceList class --- src/PriceListTools/ExportTool.cs | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'src/PriceListTools/ExportTool.cs') diff --git a/src/PriceListTools/ExportTool.cs b/src/PriceListTools/ExportTool.cs index 76a5c6f..813df03 100644 --- a/src/PriceListTools/ExportTool.cs +++ b/src/PriceListTools/ExportTool.cs @@ -77,7 +77,7 @@ namespace RehauSku.PriceListTools { if (SkuAmount.Count < 1) return; - PriceListSheet offer = NewPriceList.Sheet; + PriceList offer = NewPriceList; offer.Sheet.Activate(); int exportedValues = 0; -- cgit v1.2.3 From 94e0c84ce1e62826d963c0809be1c4d242694444 Mon Sep 17 00:00:00 2001 From: Sergey Chebotar Date: Wed, 26 Jan 2022 18:37:24 +0300 Subject: Move Autofilter method to abstract class --- src/PriceListTools/ExportTool.cs | 10 ++-------- 1 file changed, 2 insertions(+), 8 deletions(-) (limited to 'src/PriceListTools/ExportTool.cs') diff --git a/src/PriceListTools/ExportTool.cs b/src/PriceListTools/ExportTool.cs index 813df03..5ff52d2 100644 --- a/src/PriceListTools/ExportTool.cs +++ b/src/PriceListTools/ExportTool.cs @@ -107,14 +107,8 @@ namespace RehauSku.PriceListTools exportedValues++; } } - - AutoFilter filter = offer.Sheet.AutoFilter; - int firstFilterColumn = filter.Range.Column; - - filter.Range.AutoFilter(offer.amountCell.Column - firstFilterColumn + 1, "<>"); - offer.Sheet.Range["A1"].Activate(); - AddIn.Excel.StatusBar = $"Экспортировано {exportedValues} строк из {SkuAmount.Count}"; - + FilterByAmount(); + AddIn.Excel.StatusBar = $"Экспортировано {exportedValues} строк из {SkuAmount.Count}"; Forms.Dialog.SaveWorkbookAs(); } -- cgit v1.2.3 From 233c91c71b5c68ed7c51f26731b491dac8423771 Mon Sep 17 00:00:00 2001 From: Sergey Chebotar Date: Wed, 26 Jan 2022 19:03:28 +0300 Subject: Exception message on wrong files --- src/PriceListTools/ExportTool.cs | 15 +++++++++------ 1 file changed, 9 insertions(+), 6 deletions(-) (limited to 'src/PriceListTools/ExportTool.cs') diff --git a/src/PriceListTools/ExportTool.cs b/src/PriceListTools/ExportTool.cs index 5ff52d2..2dc673b 100644 --- a/src/PriceListTools/ExportTool.cs +++ b/src/PriceListTools/ExportTool.cs @@ -75,16 +75,16 @@ namespace RehauSku.PriceListTools public override void FillPriceList() { - if (SkuAmount.Count < 1) return; - - PriceList offer = NewPriceList; - offer.Sheet.Activate(); + if (SkuAmount.Count < 1) + return; int exportedValues = 0; + ExcelApp.ScreenUpdating = false; + foreach (var kvp in SkuAmount) { - Range cell = offer.Sheet.Columns[offer.skuCell.Column].Find(kvp.Key); + Range cell = NewPriceList.Sheet.Columns[NewPriceList.skuCell.Column].Find(kvp.Key); if (cell == null) { @@ -97,7 +97,7 @@ namespace RehauSku.PriceListTools else { - Range sumCell = offer.Sheet.Cells[cell.Row, offer.amountCell.Column]; + Range sumCell = NewPriceList.Sheet.Cells[cell.Row, NewPriceList.amountCell.Column]; if (sumCell.Value2 == null) sumCell.Value2 = kvp.Value; @@ -107,7 +107,10 @@ namespace RehauSku.PriceListTools exportedValues++; } } + FilterByAmount(); + ExcelApp.ScreenUpdating = true; + AddIn.Excel.StatusBar = $"Экспортировано {exportedValues} строк из {SkuAmount.Count}"; Forms.Dialog.SaveWorkbookAs(); } -- cgit v1.2.3 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/ExportTool.cs | 11 +++-------- 1 file changed, 3 insertions(+), 8 deletions(-) (limited to 'src/PriceListTools/ExportTool.cs') diff --git a/src/PriceListTools/ExportTool.cs b/src/PriceListTools/ExportTool.cs index 2dc673b..b36fd13 100644 --- a/src/PriceListTools/ExportTool.cs +++ b/src/PriceListTools/ExportTool.cs @@ -6,7 +6,7 @@ using System.Collections.Generic; namespace RehauSku.PriceListTools { - internal class ExportTool : AbstractPriceListTool, IDisposable + internal class ExportTool : PriceListTool { private Dictionary SkuAmount { get; set; } private Range Selection; @@ -25,7 +25,7 @@ namespace RehauSku.PriceListTools else throw new Exception("Неверный диапазон"); } - public override void GetSource(string[] files) + public override void GetSourceLists(string[] files) => GetSource(); private void FillSkuAmountDict() @@ -73,7 +73,7 @@ namespace RehauSku.PriceListTools } } - public override void FillPriceList() + public override void FillTarget() { if (SkuAmount.Count < 1) return; @@ -114,11 +114,6 @@ namespace RehauSku.PriceListTools AddIn.Excel.StatusBar = $"Экспортировано {exportedValues} строк из {SkuAmount.Count}"; Forms.Dialog.SaveWorkbookAs(); } - - public void Dispose() - { - GC.SuppressFinalize(this); - } } } -- 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/ExportTool.cs | 69 +++++++++------------------------------- 1 file changed, 15 insertions(+), 54 deletions(-) (limited to 'src/PriceListTools/ExportTool.cs') diff --git a/src/PriceListTools/ExportTool.cs b/src/PriceListTools/ExportTool.cs index b36fd13..10d66b4 100644 --- a/src/PriceListTools/ExportTool.cs +++ b/src/PriceListTools/ExportTool.cs @@ -1,5 +1,4 @@ -using ExcelDna.Integration; -using Microsoft.Office.Interop.Excel; +using Microsoft.Office.Interop.Excel; using RehauSku.Assistant; using System; using System.Collections.Generic; @@ -11,24 +10,28 @@ namespace RehauSku.PriceListTools private Dictionary SkuAmount { get; set; } private Range Selection; - public ExportTool() + public void TryGetSelection() { - ExcelApp = (Application)ExcelDnaUtil.Application; Selection = ExcelApp.Selection; + + if (Selection == null || Selection.Columns.Count != 2) + { + throw new Exception("Неверный диапазон"); + } } - public override void GetSource() + public void FillTarget() { - if (Selection != null && Selection.Columns.Count == 2) - FillSkuAmountDict(); + ExcelApp.ScreenUpdating = false; + GetSelected(); + FillAmountColumn(new [] {SkuAmount}); + FilterByAmount(); + ExcelApp.ScreenUpdating = true; - else throw new Exception("Неверный диапазон"); + Forms.Dialog.SaveWorkbookAs(); } - public override void GetSourceLists(string[] files) - => GetSource(); - - private void FillSkuAmountDict() + private void GetSelected() { object[,] cells = Selection.Value2; SkuAmount = new Dictionary(); @@ -72,48 +75,6 @@ namespace RehauSku.PriceListTools SkuAmount.Add(sku, amount.Value); } } - - public override void FillTarget() - { - if (SkuAmount.Count < 1) - return; - - int exportedValues = 0; - - ExcelApp.ScreenUpdating = false; - - foreach (var kvp in SkuAmount) - { - Range cell = NewPriceList.Sheet.Columns[NewPriceList.skuCell.Column].Find(kvp.Key); - - if (cell == null) - { - System.Windows.Forms.MessageBox.Show - ($"Артикул {kvp.Key} отсутствует в таблице заказов {RegistryUtil.PriceListPath}", - "Отсутствует позиция в конечной таблице заказов", - System.Windows.Forms.MessageBoxButtons.OK, - System.Windows.Forms.MessageBoxIcon.Information); - } - - else - { - Range sumCell = NewPriceList.Sheet.Cells[cell.Row, NewPriceList.amountCell.Column]; - - if (sumCell.Value2 == null) - sumCell.Value2 = kvp.Value; - else - sumCell.Value2 += kvp.Value; - - exportedValues++; - } - } - - FilterByAmount(); - ExcelApp.ScreenUpdating = true; - - AddIn.Excel.StatusBar = $"Экспортировано {exportedValues} строк из {SkuAmount.Count}"; - Forms.Dialog.SaveWorkbookAs(); - } } } -- cgit v1.2.3 From 7bb0a82ffbd5c5123bfdbff6ba29f463ab9d1b46 Mon Sep 17 00:00:00 2001 From: Sergey Chebotar Date: Thu, 27 Jan 2022 20:43:19 +0300 Subject: Add FillColumn method --- src/PriceListTools/ExportTool.cs | 8 +++++++- 1 file changed, 7 insertions(+), 1 deletion(-) (limited to 'src/PriceListTools/ExportTool.cs') diff --git a/src/PriceListTools/ExportTool.cs b/src/PriceListTools/ExportTool.cs index 10d66b4..fc9e42d 100644 --- a/src/PriceListTools/ExportTool.cs +++ b/src/PriceListTools/ExportTool.cs @@ -24,7 +24,7 @@ namespace RehauSku.PriceListTools { ExcelApp.ScreenUpdating = false; GetSelected(); - FillAmountColumn(new [] {SkuAmount}); + FillColumn(SkuAmount, TargetFile.amountCell.Column); FilterByAmount(); ExcelApp.ScreenUpdating = true; @@ -67,12 +67,18 @@ namespace RehauSku.PriceListTools } if (sku == null || amount == null) + { continue; + } if (SkuAmount.ContainsKey(sku)) + { SkuAmount[sku] += amount.Value; + } else + { SkuAmount.Add(sku, amount.Value); + } } } } -- 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/ExportTool.cs | 40 ++++++++++++++++++++++++++++++++++++++++ 1 file changed, 40 insertions(+) (limited to 'src/PriceListTools/ExportTool.cs') diff --git a/src/PriceListTools/ExportTool.cs b/src/PriceListTools/ExportTool.cs index fc9e42d..8bf274a 100644 --- a/src/PriceListTools/ExportTool.cs +++ b/src/PriceListTools/ExportTool.cs @@ -31,6 +31,46 @@ namespace RehauSku.PriceListTools Forms.Dialog.SaveWorkbookAs(); } + private void FillColumn(IEnumerable> dictionary, int column) + { + List> missing = new List>(); + + foreach (var kvp in dictionary) + { + Range cell = TargetFile.skuCell.EntireColumn.Find(kvp.Key); + + if (cell == null) + { + missing.Add(kvp); + } + + else + { + Range sumCell = TargetFile.Sheet.Cells[cell.Row, column]; + + if (sumCell.Value2 == null) + { + sumCell.Value2 = kvp.Value; + } + + else + { + sumCell.Value2 += kvp.Value; + } + } + } + + if (missing.Count > 0) + { + System.Windows.Forms.MessageBox.Show + ($"{missing.Count} артикулов отсутствует в таблице заказов {RegistryUtil.PriceListPath} Попробовать найти новый вариант?", + "Отсутствует позиция в конечной таблице заказов", + System.Windows.Forms.MessageBoxButtons.YesNo, + System.Windows.Forms.MessageBoxIcon.Information); + } + } + + private void GetSelected() { object[,] cells = Selection.Value2; -- cgit v1.2.3