diff options
| -rw-r--r-- | src/PriceListTools/CombineTool.cs | 95 | ||||
| -rw-r--r-- | src/PriceListTools/ConjoinTool.cs | 9 | ||||
| -rw-r--r-- | src/PriceListTools/ExportTool.cs | 2 | ||||
| -rw-r--r-- | src/PriceListTools/IConjoinTool.cs | 8 | ||||
| -rw-r--r-- | src/PriceListTools/MergeTool.cs | 19 | ||||
| -rw-r--r-- | src/PriceListTools/PriceList.cs | 100 | ||||
| -rw-r--r-- | src/PriceListTools/PriceListUtil.cs | 2 | ||||
| -rw-r--r-- | src/RehauSku.Assist.csproj | 3 | ||||
| -rw-r--r-- | src/Ribbon/RibbonController.cs | 24 |
9 files changed, 219 insertions, 43 deletions
diff --git a/src/PriceListTools/CombineTool.cs b/src/PriceListTools/CombineTool.cs new file mode 100644 index 0000000..c536936 --- /dev/null +++ b/src/PriceListTools/CombineTool.cs @@ -0,0 +1,95 @@ +using ExcelDna.Integration; +using Microsoft.Office.Interop.Excel; +using System; +using System.Collections.Generic; +using System.IO; +using System.Linq; + +namespace RehauSku.PriceListTools +{ + class CombineTool : ConjoinTool, IDisposable, IConjoinTool + { + private Dictionary<string, double>[] SkuAmount { get; set; } + private string[] FileNames { get; set; } + + public CombineTool() + { + ExcelApp = (Application)ExcelDnaUtil.Application; + } + + public void CollectSkuAmount(string[] files) + { + FileNames = files.Select(x => Path.GetFileNameWithoutExtension(x)).ToArray(); + SkuAmount = new Dictionary<string, double>[files.Length]; + + ExcelApp.ScreenUpdating = false; + + for (int i = 0; i < files.Length; i++) + { + Workbook wb = ExcelApp.Workbooks.Open(files[i]); + + try + { + PriceList priceList = new PriceList(wb); + SkuAmount[i] = new Dictionary<string, double>(); + SkuAmount[i].AddValuesFromPriceList(priceList); + } + + catch (Exception ex) + { + System.Windows.Forms.MessageBox.Show + ($"{wb.Name} не является файлом прайс-листа \n\n {ex.Message}", + "Неверный файл прайс-листа!", + System.Windows.Forms.MessageBoxButtons.OK, + System.Windows.Forms.MessageBoxIcon.Error); + } + + finally + { + wb.Close(); + } + } + + ExcelApp.ScreenUpdating = true; + } + + public void ExportToFile(string exportFile) + { + if (SkuAmount.Sum(d => d.Count) < 1) + { + return; + } + + Workbook wb = ExcelApp.Workbooks.Open(exportFile); + PriceList priceList; + + try + { + priceList = new PriceList(wb); + priceList.FillWithValues(SkuAmount, FileNames); + } + + catch (Exception ex) + { + System.Windows.Forms.MessageBox.Show + ($"{RegistryUtil.PriceListPath} не является файлом прайс-листа \n\n {ex.Message}", + "Неверный файл прайс-листа!", + System.Windows.Forms.MessageBoxButtons.OK, + System.Windows.Forms.MessageBoxIcon.Error); + + wb.Close(); + } + } + + public void Dispose() + { + //Dispose(true); + GC.SuppressFinalize(this); + } + + //protected virtual void Dispose(bool disposing) + //{ + + //} + } +} diff --git a/src/PriceListTools/ConjoinTool.cs b/src/PriceListTools/ConjoinTool.cs new file mode 100644 index 0000000..19ad653 --- /dev/null +++ b/src/PriceListTools/ConjoinTool.cs @@ -0,0 +1,9 @@ +using Microsoft.Office.Interop.Excel; + +namespace RehauSku.PriceListTools +{ + internal class ConjoinTool + { + protected Application ExcelApp; + } +}
\ No newline at end of file diff --git a/src/PriceListTools/ExportTool.cs b/src/PriceListTools/ExportTool.cs index 61f6f0f..e549a00 100644 --- a/src/PriceListTools/ExportTool.cs +++ b/src/PriceListTools/ExportTool.cs @@ -85,7 +85,7 @@ namespace RehauSku.PriceListTools try { PriceList priceList = new PriceList(wb); - priceList.Fill(SkuAmount); + priceList.FillWithValues(SkuAmount); } catch(Exception ex) diff --git a/src/PriceListTools/IConjoinTool.cs b/src/PriceListTools/IConjoinTool.cs new file mode 100644 index 0000000..3107a16 --- /dev/null +++ b/src/PriceListTools/IConjoinTool.cs @@ -0,0 +1,8 @@ +namespace RehauSku.PriceListTools +{ + internal interface IConjoinTool + { + void CollectSkuAmount(string[] files); + void ExportToFile(string exportFile); + } +}
\ No newline at end of file diff --git a/src/PriceListTools/MergeTool.cs b/src/PriceListTools/MergeTool.cs index ddf74d2..2dbe61d 100644 --- a/src/PriceListTools/MergeTool.cs +++ b/src/PriceListTools/MergeTool.cs @@ -5,18 +5,17 @@ using System.Collections.Generic; namespace RehauSku.PriceListTools { - class MergeTool : IDisposable + class MergeTool : ConjoinTool, IDisposable, IConjoinTool { - private Application ExcelApp; - private Dictionary<string, double> SkuAmount { get; set; } + private Dictionary<string, double> SkuAmount { get; set; } public MergeTool() { - this.ExcelApp = (Application)ExcelDnaUtil.Application; - this.SkuAmount = new Dictionary<string, double>(); + ExcelApp = (Application)ExcelDnaUtil.Application; + SkuAmount = new Dictionary<string, double>(); } - public void AddSkuAmountToDict(string[] files) + public void CollectSkuAmount(string[] files) { ExcelApp.ScreenUpdating = false; foreach (string file in files) @@ -26,13 +25,13 @@ namespace RehauSku.PriceListTools try { PriceList priceList = new PriceList(wb); - SkuAmount.AddValues(priceList); + SkuAmount.AddValuesFromPriceList(priceList); } catch (Exception ex) { System.Windows.Forms.MessageBox.Show - ( $"{wb.Name} не является файлом прайс-листа \n\n {ex.Message}", + ($"{wb.Name} не является файлом прайс-листа \n\n {ex.Message}", "Неверный файл прайс-листа!", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error); @@ -46,7 +45,7 @@ namespace RehauSku.PriceListTools ExcelApp.ScreenUpdating = true; } - public void ExportToNewFile(string exportFile) + public void ExportToFile(string exportFile) { if (SkuAmount.Count < 1) { @@ -59,7 +58,7 @@ namespace RehauSku.PriceListTools try { priceList = new PriceList(wb); - priceList.Fill(SkuAmount); + priceList.FillWithValues(SkuAmount); } catch (Exception ex) diff --git a/src/PriceListTools/PriceList.cs b/src/PriceListTools/PriceList.cs index 35b3f7d..c39b0ba 100644 --- a/src/PriceListTools/PriceList.cs +++ b/src/PriceListTools/PriceList.cs @@ -1,5 +1,6 @@ using Microsoft.Office.Interop.Excel; using System.Collections.Generic; +using System.Linq; namespace RehauSku.PriceListTools { @@ -9,31 +10,25 @@ namespace RehauSku.PriceListTools public readonly PriceListSheet OfferSheet; public readonly PriceListSheet ActiveSheet; - private const string _amountHeader = "Кол-во"; - private const string _skuHeader = "Актуальный материал"; - private const string _offerSheetHeader = "КП"; + private const string amountHeader = "Кол-во"; + private const string skuHeader = "Актуальный материал"; + private const string offerSheetHeader = "КП"; public PriceList(Workbook workbook) { Workbook = workbook; - OfferSheet = new PriceListSheet(workbook.Sheets[_offerSheetHeader]); + OfferSheet = new PriceListSheet(workbook.Sheets[offerSheetHeader]); Worksheet active = workbook.ActiveSheet; - if (active.Name == _offerSheetHeader) + if (active.Name == offerSheetHeader) ActiveSheet = OfferSheet; else - ActiveSheet = new PriceListSheet(active); + ActiveSheet = new PriceListSheet(active); } - public bool IsValid() - { - return OfferSheet.IsValid() && - ActiveSheet.IsValid(); - } - - public void Fill(Dictionary<string, double> values) + public void FillWithValues(Dictionary<string, double> values) { Worksheet ws = OfferSheet.sheet; ws.Activate(); @@ -45,6 +40,7 @@ namespace RehauSku.PriceListTools foreach (KeyValuePair<string, double> kvp in values) { Range cell = ws.Columns[skuColumn].Find(kvp.Key); + if (cell == null) { System.Windows.Forms.MessageBox.Show @@ -53,6 +49,7 @@ namespace RehauSku.PriceListTools System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Information); } + else { ws.Cells[cell.Row, amountColumn].Value = kvp.Value; @@ -68,33 +65,82 @@ namespace RehauSku.PriceListTools ws.Application.StatusBar = $"Экспортировано {exportedValues} строк из {values.Count}"; } + public void FillWithValues(Dictionary<string, double>[] values, string[] filenames) + { + Worksheet ws = OfferSheet.sheet; + ws.Activate(); + + int amountColumn = OfferSheet.amountColumn.Value; + int skuColumn = OfferSheet.skuColumn.Value; + int headerColumn = OfferSheet.headerRow.Value; + + int exportedValues = 0; + + for (int i = 0; i < values.Length; i++) + { + ws.Columns[amountColumn] + .EntireColumn + .Insert(XlInsertShiftDirection.xlShiftToRight, XlInsertFormatOrigin.xlFormatFromRightOrBelow); + + foreach (var kvp in values[i]) + { + Range cell = ws.Columns[skuColumn].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 + { + ws.Cells[cell.Row, amountColumn].Value2 = kvp.Value; + Range sumCell = ws.Cells[cell.Row, amountColumn + i + 1]; + + if (sumCell.Value2 == null) + sumCell.Value2 = kvp.Value; + else + sumCell.Value2 += kvp.Value; + + exportedValues++; + } + } + + ws.Cells[headerColumn, amountColumn].Value2 = filenames[i]; + } + + AutoFilter filter = ws.AutoFilter; + int firstFilterColumn = filter.Range.Column; + + filter.Range.AutoFilter(amountColumn - firstFilterColumn + 1 + values.Length, "<>"); + ws.Range["A1"].Activate(); + ws.Application.StatusBar = $"Экспортировано {exportedValues} строк из {values.Sum(x => x.Count)}"; + } + public class PriceListSheet { public readonly Worksheet sheet; public readonly int? headerRow; - public readonly int? amountColumn; + public readonly int? skuColumn; - public object[,] amountCells; + public readonly int? amountColumn; + public object[,] skuCells; + public object[,] amountCells; public PriceListSheet(Worksheet sheet) { this.sheet = sheet; - headerRow = sheet.Cells.Find(_amountHeader).Row; - amountColumn = sheet.Cells.Find(_amountHeader).Column; - skuColumn = sheet.Cells.Find(_skuHeader).Column; + headerRow = sheet.Cells.Find(amountHeader).Row; + amountColumn = sheet.Cells.Find(amountHeader).Column; + skuColumn = sheet.Cells.Find(skuHeader).Column; amountCells = sheet.Columns[amountColumn].Value2; skuCells = sheet.Columns[skuColumn].Value2; - } - - public bool IsValid() - { - return sheet != null && - headerRow != null && - amountColumn != null && - skuColumn != null; - } + } } } } diff --git a/src/PriceListTools/PriceListUtil.cs b/src/PriceListTools/PriceListUtil.cs index 14797d9..3a04cc9 100644 --- a/src/PriceListTools/PriceListUtil.cs +++ b/src/PriceListTools/PriceListUtil.cs @@ -14,7 +14,7 @@ namespace RehauSku.PriceListTools return path; } - public static void AddValues(this Dictionary<string, double> SkuAmount, PriceList priceList) + public static void AddValuesFromPriceList(this Dictionary<string, double> SkuAmount, PriceList priceList) { object[,] amountCells = priceList.ActiveSheet.amountCells; object[,] skuCells = priceList.ActiveSheet.skuCells; diff --git a/src/RehauSku.Assist.csproj b/src/RehauSku.Assist.csproj index a977125..1f85926 100644 --- a/src/RehauSku.Assist.csproj +++ b/src/RehauSku.Assist.csproj @@ -106,6 +106,9 @@ <Compile Include="Assistant\ParseUtil.cs" /> <Compile Include="Assistant\RequestModifier.cs" /> <Compile Include="Assistant\SkuExtensions.cs" /> + <Compile Include="PriceListTools\CombineTool.cs" /> + <Compile Include="PriceListTools\ConjoinTool.cs" /> + <Compile Include="PriceListTools\IConjoinTool.cs" /> <Compile Include="PriceListTools\MergeTool.cs" /> <Compile Include="PriceListTools\PriceList.cs" /> <Compile Include="PriceListTools\PriceListUtil.cs" /> diff --git a/src/Ribbon/RibbonController.cs b/src/Ribbon/RibbonController.cs index df6f327..ad27bc2 100644 --- a/src/Ribbon/RibbonController.cs +++ b/src/Ribbon/RibbonController.cs @@ -17,8 +17,11 @@ namespace RehauSku.Ribbon <tabs> <tab id='rau' label='REHAU'> <group id='priceList' label='Прайс-лист'> - <button id='exportToPrice' label='Экспорт в новый файл' size='normal' imageMso='PivotExportToExcel' onAction='OnExportPressed'/> - <button id='mergeFiles' label='Объединить' size='normal' imageMso='Copy' onAction='OnMergePressed'/> + <button id='exportToPrice' label='Экспорт в новый файл' size='normal' imageMso='PivotExportToExcel' onAction='OnExportPressed'/> + <menu id='conjoinMenu' label='Объединить' imageMso='Copy'> + <button id='mergeFiles' label='Сложить' onAction='OnMergePressed'/> + <button id='combineFiles' label='По колонкам' onAction='OnCombinePressed'/> + </menu> </group> <group id='rausettings' label='Настройки'> <button id='setPriceList' label='Файл прайс-листа' size='normal' imageMso='CurrentViewSettings' onAction='OnSetPricePressed'/> @@ -29,14 +32,27 @@ namespace RehauSku.Ribbon </customUI>"; } + // <dropDown id = 'dd1' label = 'Drop dynamic' getItemCount = 'fncGetItemCountDrop' getItemLabel = 'fncGetItemLabelDrop' onAction = 'fncOnActionDrop'/> + public void OnMergePressed(IRibbonControl control) { using (MergeTool mergeTool = new MergeTool()) { string[] files = Dialog.GetMultiplyFiles(); - mergeTool.AddSkuAmountToDict(files); + mergeTool.CollectSkuAmount(files); + string exportFile = PriceListUtil.CreateNewExportFile(); + mergeTool.ExportToFile(exportFile); + } + } + + public void OnCombinePressed(IRibbonControl control) + { + using (CombineTool combineTool = new CombineTool()) + { + string[] files = Dialog.GetMultiplyFiles(); + combineTool.CollectSkuAmount(files); string exportFile = PriceListUtil.CreateNewExportFile(); - mergeTool.ExportToNewFile(exportFile); + combineTool.ExportToFile(exportFile); } } |