From 8672fa8f6b28ee3e3cf0d00e05e1b9d3ab7a2bfd Mon Sep 17 00:00:00 2001 From: Sergey Chebotar Date: Fri, 7 Jan 2022 19:04:07 +0300 Subject: Add CombineTool for conjoining files into columns --- src/PriceListTools/PriceList.cs | 100 +++++++++++++++++++++++++++++----------- 1 file changed, 73 insertions(+), 27 deletions(-) (limited to 'src/PriceListTools/PriceList.cs') 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 values) + public void FillWithValues(Dictionary values) { Worksheet ws = OfferSheet.sheet; ws.Activate(); @@ -45,6 +40,7 @@ namespace RehauSku.PriceListTools foreach (KeyValuePair 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[] 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; - } + } } } } -- cgit v1.2.3 From 762127a4aa1ee393f19e08018830bc8ba023c050 Mon Sep 17 00:00:00 2001 From: Sergey Chebotar Date: Sun, 9 Jan 2022 10:37:25 +0300 Subject: Refactoring --- src/PriceListTools/PriceList.cs | 137 +++++----------------------------------- 1 file changed, 16 insertions(+), 121 deletions(-) (limited to 'src/PriceListTools/PriceList.cs') diff --git a/src/PriceListTools/PriceList.cs b/src/PriceListTools/PriceList.cs index c39b0ba..bc11a17 100644 --- a/src/PriceListTools/PriceList.cs +++ b/src/PriceListTools/PriceList.cs @@ -1,146 +1,41 @@ using Microsoft.Office.Interop.Excel; using System.Collections.Generic; +using System.IO; using System.Linq; namespace RehauSku.PriceListTools { - class PriceList + internal class PriceList { - public readonly Workbook Workbook; + public readonly string Name; public readonly PriceListSheet OfferSheet; - public readonly PriceListSheet ActiveSheet; + public List Sheets { get; private set; } - private const string amountHeader = "Кол-во"; - private const string skuHeader = "Актуальный материал"; private const string offerSheetHeader = "КП"; public PriceList(Workbook workbook) { - Workbook = workbook; - OfferSheet = new PriceListSheet(workbook.Sheets[offerSheetHeader]); + Name = workbook.Name; + Sheets = new List(); - Worksheet active = workbook.ActiveSheet; - - if (active.Name == offerSheetHeader) - ActiveSheet = OfferSheet; - - else - ActiveSheet = new PriceListSheet(active); - } - - public void FillWithValues(Dictionary values) - { - Worksheet ws = OfferSheet.sheet; - ws.Activate(); - - int amountColumn = OfferSheet.amountColumn.Value; - int skuColumn = OfferSheet.skuColumn.Value; - int exportedValues = 0; - - foreach (KeyValuePair kvp in values) + foreach (Worksheet worksheet in workbook.Sheets) { - 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); - } + PriceListSheet priceListSheet = new PriceListSheet(worksheet); - else - { - ws.Cells[cell.Row, amountColumn].Value = kvp.Value; - exportedValues++; - } + if (priceListSheet.FillSkuAmount()) + Sheets.Add(priceListSheet); } - AutoFilter filter = ws.AutoFilter; - int firstFilterColumn = filter.Range.Column; - - filter.Range.AutoFilter(amountColumn - firstFilterColumn + 1, "<>"); - ws.Range["A1"].Activate(); - ws.Application.StatusBar = $"Экспортировано {exportedValues} строк из {values.Count}"; + OfferSheet = Sheets.Where(s => s.Name == offerSheetHeader).FirstOrDefault(); } - public void FillWithValues(Dictionary[] values, string[] filenames) + public static string CreateNewFile() { - 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? skuColumn; - 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; + string fileExtension = Path.GetExtension(RegistryUtil.PriceListPath); + string path = Path.GetTempFileName() + fileExtension; - amountCells = sheet.Columns[amountColumn].Value2; - skuCells = sheet.Columns[skuColumn].Value2; - } + File.Copy(RegistryUtil.PriceListPath, path); + return path; } } } -- cgit v1.2.3