From 0513ac5ad7c7de498b794c27728c2c8ff306f941 Mon Sep 17 00:00:00 2001 From: Sergey Chebotar Date: Thu, 23 Dec 2021 15:31:23 +0300 Subject: Rename to ExportTool --- Source/AddIn/AddIn.cs | 1 + Source/AddIn/RegistryUtil.cs | 5 ++ Source/DataExport/ExportTool.cs | 129 ++++++++++++++++++++++++++++++++++++++ Source/DataExport/Exporter.cs | 117 ---------------------------------- Source/Ribbon/RibbonController.cs | 2 +- 5 files changed, 136 insertions(+), 118 deletions(-) create mode 100644 Source/DataExport/ExportTool.cs delete mode 100644 Source/DataExport/Exporter.cs (limited to 'Source') diff --git a/Source/AddIn/AddIn.cs b/Source/AddIn/AddIn.cs index 08b6dcf..014b607 100644 --- a/Source/AddIn/AddIn.cs +++ b/Source/AddIn/AddIn.cs @@ -28,6 +28,7 @@ namespace RehauSku public void AutoClose() { IntelliSenseServer.Uninstall(); + RegistryUtil.Uninitialize(); } void RegisterFunctions() diff --git a/Source/AddIn/RegistryUtil.cs b/Source/AddIn/RegistryUtil.cs index 19f48b8..ef1398e 100644 --- a/Source/AddIn/RegistryUtil.cs +++ b/Source/AddIn/RegistryUtil.cs @@ -16,6 +16,11 @@ namespace RehauSku _storeResponseOrder = _RootKey.GetValue("StoreResponseOrder") as int?; } + public static void Uninitialize() + { + _RootKey.Close(); + } + public static bool IsPriceListPathEmpty() { return string.IsNullOrEmpty(_priceListPath); diff --git a/Source/DataExport/ExportTool.cs b/Source/DataExport/ExportTool.cs new file mode 100644 index 0000000..8ea65cd --- /dev/null +++ b/Source/DataExport/ExportTool.cs @@ -0,0 +1,129 @@ +using ExcelDna.Integration; +using Microsoft.Office.Interop.Excel; +using System; +using System.Collections.Generic; +using System.IO; +using RehauSku.Assistant; + +namespace RehauSku.DataExport +{ + public class ExportTool : IDisposable + { + private Application xlApp; + private Dictionary SkuAmount { get; set; } + private Range Selection { get; set; } + private string CurrentFilePath { get; set; } + + public ExportTool() + { + this.xlApp = (Application)ExcelDnaUtil.Application; + this.CurrentFilePath = xlApp.ActiveWorkbook.FullName; + + _GetSelectedCells(); + } + + private void _GetSelectedCells() + { + Selection = xlApp.Selection; + } + + public bool IsRangeValid() + { + return Selection.Columns.Count == 2; + } + + private void FillSkuAmountDict() + { + object[,] cells = Selection.Value2; + SkuAmount = new Dictionary(); + int rowsCount = Selection.Rows.Count; + + for (int row = 1; row <= rowsCount; row++) + { + if (cells[row, 1] == null || cells[row, 2] == null) + continue; + + string sku = null; + double? amount = null; + + for (int column = 1; column <= 2; column++) + { + object current = cells[row, column]; + + if (current.GetType() == typeof(string) + && ((string)current).IsRehauSku()) + sku = (string)current; + + else if (current.GetType() == typeof(string) + && double.TryParse((string)current, out _)) + amount = double.Parse((string)current); + + else if (current.GetType() == typeof(double)) + amount = (double)current; + } + + if (sku == null || amount == null) + continue; + + if (SkuAmount.ContainsKey(sku)) + SkuAmount[sku] += amount.Value; + else + SkuAmount.Add(sku, amount.Value); + } + } + + public void FillNewPriceList() + { + const string amountHeader = "Кол-во"; + const string skuHeader = "Актуальный материал"; + + FillSkuAmountDict(); + string exportFile = _GetExportFullPath(); + File.Copy(RegistryUtil.PriceListPath, exportFile, true); + + Workbook wb = xlApp.Workbooks.Open(exportFile); + Worksheet ws = wb.Sheets["КП"]; + ws.Activate(); + + int amountColumn = ws.Cells.Find(amountHeader).Column; + int skuColumn = ws.Cells.Find(skuHeader).Column; + + foreach (KeyValuePair kvp in SkuAmount) + { + Range cell = ws.Columns[skuColumn].Find(kvp.Key); + ws.Cells[cell.Row, amountColumn].Value = kvp.Value; + } + + AutoFilter filter = ws.AutoFilter; + int firstFilterColumn = filter.Range.Column; + + filter.Range.AutoFilter(amountColumn - firstFilterColumn + 1, "<>"); + ws.Range["A1"].Activate(); + } + + private string _GetExportFullPath() + { + string fileExtension = Path.GetExtension(RegistryUtil.PriceListPath); + + return Path.GetTempFileName() + fileExtension; + } + + + public void Dispose() + { + Dispose(true); + GC.SuppressFinalize(this); + } + + protected virtual void Dispose(bool disposing) + { + + } + } + + class SelectionCheck + { + + } +} + diff --git a/Source/DataExport/Exporter.cs b/Source/DataExport/Exporter.cs deleted file mode 100644 index 95b3186..0000000 --- a/Source/DataExport/Exporter.cs +++ /dev/null @@ -1,117 +0,0 @@ -using ExcelDna.Integration; -using Microsoft.Office.Interop.Excel; -using System; -using System.Collections.Generic; -using System.IO; -using RehauSku.Assistant; - -namespace RehauSku.DataExport -{ - public class Exporter : IDisposable - { - private Application xlApp; - private Dictionary SkuAmount { get; set; } - private object[,] SelectedCells { get; set; } - private string CurrentFilePath { get; set; } - - public Exporter() - { - this.xlApp = (Application)ExcelDnaUtil.Application; - this.CurrentFilePath = xlApp.ActiveWorkbook.FullName; - - _GetSelectedCells(); - } - - private void _GetSelectedCells() - { - Range selection = xlApp.Selection; - this.SelectedCells = (object[,])selection.Value2; - } - - public bool IsRangeValid() - { - return SelectedCells != null && - SelectedCells.GetLength(1) == 2; - } - - private void FillSkuAmountDict() - { - SkuAmount = new Dictionary(); - int rowsCount = SelectedCells.GetLength(0); - - for (int row = 1; row <= rowsCount; row++) - { - if (SelectedCells[row, 1] == null || SelectedCells[row, 2] == null) - continue; - - string sku = null; - double? amount = null; - - for (int column = 1; column <= 2; column++) - { - object current = SelectedCells[row, column]; - - if (current.GetType() == typeof(string) - && ((string)current).IsRehauSku()) - sku = (string)current; - - else if (current.GetType() == typeof(string) - && double.TryParse((string)current, out _)) - amount = double.Parse((string)current); - - else if (current.GetType() == typeof(double)) - amount = (double)current; - } - - if (sku == null || amount == null) - continue; - - if (SkuAmount.ContainsKey(sku)) - SkuAmount[sku] += amount.Value; - else - SkuAmount.Add(sku, amount.Value); - } - } - - public void FillNewPriceList() - { - FillSkuAmountDict(); - string exportFile = _GetExportFullPath(); - File.Copy(RegistryUtil.PriceListPath, exportFile, true); - - Workbook wb = xlApp.Workbooks.Open(exportFile); - Worksheet ws = wb.ActiveSheet; - - int amountColumn = ws.Cells.Find("Кол-во").Column; - int skuColumn = ws.Cells.Find("Актуальный материал").Column; - - foreach (KeyValuePair kvp in SkuAmount) - { - Range cell = ws.Columns[skuColumn].Find(kvp.Key); - ws.Cells[cell.Row, amountColumn].Value = kvp.Value; - } - - ws.Cells.AutoFilter(7, "<>"); - } - - private string _GetExportFullPath() - { - string fileExtension = Path.GetExtension(RegistryUtil.PriceListPath); - - return Path.GetTempFileName() + fileExtension; - } - - - public void Dispose() - { - Dispose(true); - GC.SuppressFinalize(this); - } - - protected virtual void Dispose(bool disposing) - { - - } - } -} - diff --git a/Source/Ribbon/RibbonController.cs b/Source/Ribbon/RibbonController.cs index cfe4532..325b4c6 100644 --- a/Source/Ribbon/RibbonController.cs +++ b/Source/Ribbon/RibbonController.cs @@ -29,7 +29,7 @@ namespace RehauSku.Ribbon public void OnExportPressed(IRibbonControl control) { - using (Exporter dw = new Exporter()) + using (ExportTool dw = new ExportTool()) { if (!dw.IsRangeValid()) { -- cgit v1.2.3