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/MergeTool.cs | 45 ++++++++++++++++++++--------------------- 1 file changed, 22 insertions(+), 23 deletions(-) (limited to 'src/PriceListTools/MergeTool.cs') diff --git a/src/PriceListTools/MergeTool.cs b/src/PriceListTools/MergeTool.cs index 493f8a8..4f5b727 100644 --- a/src/PriceListTools/MergeTool.cs +++ b/src/PriceListTools/MergeTool.cs @@ -7,42 +7,41 @@ namespace RehauSku.PriceListTools { public override void FillPriceList() { - PriceListSheet offer = NewPriceList.OfferSheet; + PriceListSheet offer = NewPriceList.Sheet; offer.Sheet.Activate(); int exportedValues = 0; foreach (var priceList in sourcePriceLists) { - foreach (var sheet in priceList.Sheets) + PriceListSheet sheet = priceList.Sheet; + + if (sheet.SkuAmount.Count == 0) + continue; + + foreach (var kvp in sheet.SkuAmount) { - if (sheet.SkuAmount.Count == 0) - continue; + Range cell = offer.Sheet.Columns[offer.skuColumnNumber].Find(kvp.Key); - foreach (var kvp in sheet.SkuAmount) + if (cell == null) { - Range cell = offer.Sheet.Columns[offer.skuColumnNumber].Find(kvp.Key); + System.Windows.Forms.MessageBox.Show + ($"Артикул {kvp.Key} отсутствует в таблице заказов {RegistryUtil.PriceListPath}", + "Отсутствует позиция в конечной таблице заказов", + System.Windows.Forms.MessageBoxButtons.OK, + System.Windows.Forms.MessageBoxIcon.Information); + } - 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 = offer.Sheet.Cells[cell.Row, offer.amountColumnNumber]; + if (sumCell.Value2 == null) + sumCell.Value2 = kvp.Value; else - { - Range sumCell = offer.Sheet.Cells[cell.Row, offer.amountColumnNumber]; - - if (sumCell.Value2 == null) - sumCell.Value2 = kvp.Value; - else - sumCell.Value2 += kvp.Value; + sumCell.Value2 += kvp.Value; - exportedValues++; - } + exportedValues++; } } } -- 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/MergeTool.cs | 6 +++--- 1 file changed, 3 insertions(+), 3 deletions(-) (limited to 'src/PriceListTools/MergeTool.cs') diff --git a/src/PriceListTools/MergeTool.cs b/src/PriceListTools/MergeTool.cs index 4f5b727..76df6ee 100644 --- a/src/PriceListTools/MergeTool.cs +++ b/src/PriceListTools/MergeTool.cs @@ -21,7 +21,7 @@ namespace RehauSku.PriceListTools foreach (var kvp in sheet.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) { @@ -34,7 +34,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; @@ -49,7 +49,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} строк из {sourcePriceLists.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/MergeTool.cs | 9 +++------ 1 file changed, 3 insertions(+), 6 deletions(-) (limited to 'src/PriceListTools/MergeTool.cs') diff --git a/src/PriceListTools/MergeTool.cs b/src/PriceListTools/MergeTool.cs index 76df6ee..57f49a0 100644 --- a/src/PriceListTools/MergeTool.cs +++ b/src/PriceListTools/MergeTool.cs @@ -7,15 +7,13 @@ namespace RehauSku.PriceListTools { public override void FillPriceList() { - PriceListSheet offer = NewPriceList.Sheet; + PriceList offer = NewPriceList; offer.Sheet.Activate(); int exportedValues = 0; - foreach (var priceList in sourcePriceLists) + foreach (var sheet in sourcePriceLists) { - PriceListSheet sheet = priceList.Sheet; - if (sheet.SkuAmount.Count == 0) continue; @@ -47,9 +45,8 @@ namespace RehauSku.PriceListTools } AutoFilter filter = offer.Sheet.AutoFilter; - int firstFilterColumn = filter.Range.Column; - filter.Range.AutoFilter(offer.amountCell.Column - firstFilterColumn + 1, "<>"); + filter.Range.AutoFilter(offer.amountCell.Column, "<>"); offer.Sheet.Range["A1"].Activate(); AddIn.Excel.StatusBar = $"Экспортировано {exportedValues} строк из {sourcePriceLists.Count} файлов"; -- 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/MergeTool.cs | 13 +++---------- 1 file changed, 3 insertions(+), 10 deletions(-) (limited to 'src/PriceListTools/MergeTool.cs') diff --git a/src/PriceListTools/MergeTool.cs b/src/PriceListTools/MergeTool.cs index 57f49a0..6440d4b 100644 --- a/src/PriceListTools/MergeTool.cs +++ b/src/PriceListTools/MergeTool.cs @@ -7,9 +7,6 @@ namespace RehauSku.PriceListTools { public override void FillPriceList() { - PriceList offer = NewPriceList; - offer.Sheet.Activate(); - int exportedValues = 0; foreach (var sheet in sourcePriceLists) @@ -19,7 +16,7 @@ namespace RehauSku.PriceListTools foreach (var kvp in sheet.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) { @@ -32,7 +29,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; @@ -44,12 +41,8 @@ namespace RehauSku.PriceListTools } } - AutoFilter filter = offer.Sheet.AutoFilter; - - filter.Range.AutoFilter(offer.amountCell.Column, "<>"); - offer.Sheet.Range["A1"].Activate(); + FilterByAmount(); AddIn.Excel.StatusBar = $"Экспортировано {exportedValues} строк из {sourcePriceLists.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/MergeTool.cs | 4 ++++ 1 file changed, 4 insertions(+) (limited to 'src/PriceListTools/MergeTool.cs') diff --git a/src/PriceListTools/MergeTool.cs b/src/PriceListTools/MergeTool.cs index 6440d4b..0e98b95 100644 --- a/src/PriceListTools/MergeTool.cs +++ b/src/PriceListTools/MergeTool.cs @@ -9,6 +9,8 @@ namespace RehauSku.PriceListTools { int exportedValues = 0; + ExcelApp.ScreenUpdating = false; + foreach (var sheet in sourcePriceLists) { if (sheet.SkuAmount.Count == 0) @@ -42,6 +44,8 @@ namespace RehauSku.PriceListTools } FilterByAmount(); + ExcelApp.ScreenUpdating = true; + AddIn.Excel.StatusBar = $"Экспортировано {exportedValues} строк из {sourcePriceLists.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/MergeTool.cs | 55 ++--------------------------------------- 1 file changed, 2 insertions(+), 53 deletions(-) (limited to 'src/PriceListTools/MergeTool.cs') diff --git a/src/PriceListTools/MergeTool.cs b/src/PriceListTools/MergeTool.cs index 0e98b95..51884dd 100644 --- a/src/PriceListTools/MergeTool.cs +++ b/src/PriceListTools/MergeTool.cs @@ -1,58 +1,7 @@ -using Microsoft.Office.Interop.Excel; -using System; - -namespace RehauSku.PriceListTools +namespace RehauSku.PriceListTools { - internal class MergeTool : AbstractPriceListTool, IDisposable + internal class MergeTool : PriceListTool { - public override void FillPriceList() - { - int exportedValues = 0; - - ExcelApp.ScreenUpdating = false; - - foreach (var sheet in sourcePriceLists) - { - if (sheet.SkuAmount.Count == 0) - continue; - - foreach (var kvp in sheet.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} строк из {sourcePriceLists.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/MergeTool.cs | 15 ++++++++++++++- 1 file changed, 14 insertions(+), 1 deletion(-) (limited to 'src/PriceListTools/MergeTool.cs') diff --git a/src/PriceListTools/MergeTool.cs b/src/PriceListTools/MergeTool.cs index 51884dd..20ace85 100644 --- a/src/PriceListTools/MergeTool.cs +++ b/src/PriceListTools/MergeTool.cs @@ -1,7 +1,20 @@ -namespace RehauSku.PriceListTools +using System.Collections.Generic; +using System.Linq; + +namespace RehauSku.PriceListTools { internal class MergeTool : PriceListTool { + public List SourceFiles; + + public void FillTarget() + { + ExcelApp.ScreenUpdating = false; + FillAmountColumn(SourceFiles.Select(x => x.SkuAmount).ToArray()); + FilterByAmount(); + ExcelApp.ScreenUpdating = true; + 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/MergeTool.cs | 7 ++++++- 1 file changed, 6 insertions(+), 1 deletion(-) (limited to 'src/PriceListTools/MergeTool.cs') diff --git a/src/PriceListTools/MergeTool.cs b/src/PriceListTools/MergeTool.cs index 20ace85..75804a0 100644 --- a/src/PriceListTools/MergeTool.cs +++ b/src/PriceListTools/MergeTool.cs @@ -10,7 +10,12 @@ namespace RehauSku.PriceListTools public void FillTarget() { ExcelApp.ScreenUpdating = false; - FillAmountColumn(SourceFiles.Select(x => x.SkuAmount).ToArray()); + + foreach (Source source in SourceFiles) + { + FillColumn(source.SkuAmount, TargetFile.amountCell.Column); + } + FilterByAmount(); ExcelApp.ScreenUpdating = true; -- 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/MergeTool.cs | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'src/PriceListTools/MergeTool.cs') diff --git a/src/PriceListTools/MergeTool.cs b/src/PriceListTools/MergeTool.cs index 75804a0..8d3e9ed 100644 --- a/src/PriceListTools/MergeTool.cs +++ b/src/PriceListTools/MergeTool.cs @@ -13,7 +13,7 @@ namespace RehauSku.PriceListTools foreach (Source source in SourceFiles) { - FillColumn(source.SkuAmount, TargetFile.amountCell.Column); + FillColumn(source.PositionAmount, TargetFile.amountCell.Column); } FilterByAmount(); -- cgit v1.2.3 From 60006126b97131e383a8fee137cf89a13672f042 Mon Sep 17 00:00:00 2001 From: Sergey Chebotar Date: Fri, 28 Jan 2022 18:15:13 +0300 Subject: Add not found positions to target file --- src/PriceListTools/MergeTool.cs | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'src/PriceListTools/MergeTool.cs') diff --git a/src/PriceListTools/MergeTool.cs b/src/PriceListTools/MergeTool.cs index 8d3e9ed..0d3e8eb 100644 --- a/src/PriceListTools/MergeTool.cs +++ b/src/PriceListTools/MergeTool.cs @@ -13,7 +13,7 @@ namespace RehauSku.PriceListTools foreach (Source source in SourceFiles) { - FillColumn(source.PositionAmount, TargetFile.amountCell.Column); + FillColumnsWithDictionary(source.PositionAmount, TargetFile.amountCell.Column); } FilterByAmount(); -- cgit v1.2.3