From cb5fee18918b5a29ccff23a0fb74bb13151f2e42 Mon Sep 17 00:00:00 2001 From: Sergey Chebotar Date: Wed, 2 Feb 2022 18:02:17 +0300 Subject: Rename Abstract tool --- src/PriceListTools/AbstractTool.cs | 175 ++++++++++++++++++++++++++++++++++++ src/PriceListTools/CombineTool.cs | 2 +- src/PriceListTools/ConvertTool.cs | 2 +- src/PriceListTools/ExportTool.cs | 2 +- src/PriceListTools/MergeTool.cs | 2 +- src/PriceListTools/PriceListTool.cs | 175 ------------------------------------ 6 files changed, 179 insertions(+), 179 deletions(-) create mode 100644 src/PriceListTools/AbstractTool.cs delete mode 100644 src/PriceListTools/PriceListTool.cs (limited to 'src') diff --git a/src/PriceListTools/AbstractTool.cs b/src/PriceListTools/AbstractTool.cs new file mode 100644 index 0000000..77dc63c --- /dev/null +++ b/src/PriceListTools/AbstractTool.cs @@ -0,0 +1,175 @@ +using ExcelDna.Integration; +using Microsoft.Office.Interop.Excel; +using System; +using System.Collections.Generic; +using System.Windows.Forms; +using Application = Microsoft.Office.Interop.Excel.Application; + +namespace RehauSku.PriceListTools +{ + internal abstract class AbstractTool + { + protected private Application ExcelApp = (Application)ExcelDnaUtil.Application; + protected private Target TargetFile; + + public void OpenNewPrice() + { + Workbook wb = ExcelApp.Workbooks.Open(RegistryUtil.PriceListPath); + + try + { + TargetFile = new Target(wb); + } + + catch (Exception ex) + { + MessageBox.Show + (ex.Message, + "Ошибка открытия шаблонного прайс-листа", + MessageBoxButtons.OK, + MessageBoxIcon.Information); + wb.Close(); + throw ex; + } + } + + protected private void FillColumnsWithDictionary(KeyValuePair positionAmount, params int[] columns) + { + int? row = GetPositionRow(positionAmount.Key.Sku, positionAmount.Key.Group, TargetFile.skuCell.Column); + + if (row != null) + { + foreach (int column in columns) + { + Range sumCell = TargetFile.Sheet.Cells[row, column]; + + if (sumCell.Value2 == null) + { + sumCell.Value2 = positionAmount.Value; + } + + else + { + sumCell.Value2 += positionAmount.Value; + } + } + } + + else + { + string sku = positionAmount.Key.Sku.Substring(1, 6); + + row = GetPositionRow(sku, positionAmount.Key.Group, TargetFile.skuCell.Column); + + if (row != null) + { + foreach (int column in columns) + { + Range amountCell = TargetFile.Sheet.Cells[row, column]; + + if (amountCell.Value2 == null) + { + amountCell.Value2 = positionAmount.Value; + } + + else + { + amountCell.Value2 += positionAmount.Value; + } + + Range oldSkuCell = TargetFile.Sheet.Cells[row, TargetFile.oldSkuCell.Column]; + oldSkuCell.Value2 = positionAmount.Key.Sku; + } + } + + else + { + FillMissing(positionAmount, columns); + } + } + } + + protected private void FillMissing(KeyValuePair positionAmount, params int[] columns) + { + Range foundCell = TargetFile.oldSkuCell.EntireColumn.Find(positionAmount.Key.Sku); + int row; + + if (foundCell == null) + { + row = TargetFile.Sheet.Cells[TargetFile.Sheet.Rows.Count, TargetFile.skuCell.Column] + .End[XlDirection.xlUp] + .Row + 1; + + TargetFile.Sheet.Rows[row] + .EntireRow + .Insert(XlInsertShiftDirection.xlShiftDown, XlInsertFormatOrigin.xlFormatFromLeftOrAbove); + + Range previous = TargetFile.Sheet.Rows[row - 1]; + Range current = TargetFile.Sheet.Rows[row]; + + previous.Copy(current); + current.ClearContents(); + + TargetFile.Sheet.Cells[row, TargetFile.groupCell.Column].Value2 = positionAmount.Key.Group; + TargetFile.Sheet.Cells[row, TargetFile.oldSkuCell.Column].Value2 = positionAmount.Key.Sku; + TargetFile.Sheet.Cells[row, TargetFile.nameCell.Column].Value2 = positionAmount.Key.Name; + TargetFile.Sheet.Cells[row, TargetFile.skuCell.Column].Value2 = "Не найден"; + } + + else + { + row = foundCell.Row; + } + + foreach (int column in columns) + { + if (TargetFile.Sheet.Cells[row, column].Value2 == null) + { + TargetFile.Sheet.Cells[row, column].Value2 = positionAmount.Value; + } + + else + { + TargetFile.Sheet.Cells[row, column].Value2 += positionAmount.Value; + } + } + } + + protected private int? GetPositionRow(string sku, string group, int column) + { + int? row = null; + Range foundCell = TargetFile.Sheet.Columns[column].Find(sku); + string foundGroupValue; + + if (foundCell == null) return null; + + else + { + row = foundCell.Row; + foundGroupValue = TargetFile.Sheet.Cells[foundCell.Row, TargetFile.groupCell.Column].Value2.ToString(); + } + + if (string.IsNullOrEmpty(group) || group.Equals(foundGroupValue)) + return row; + + else + while (true) + { + foundCell = TargetFile.skuCell.EntireColumn.FindNext(foundCell); + if (foundCell == null) return row; + + foundGroupValue = TargetFile.Sheet.Cells[foundCell.Row, TargetFile.groupCell.Column].Value2.ToString(); + if (group.Equals(foundGroupValue)) return foundCell.Row; + } + } + + protected private void FilterByAmount() + { + AutoFilter filter = TargetFile.Sheet.AutoFilter; + int startColumn = filter.Range.Column; + + filter.Range.AutoFilter(TargetFile.amountCell.Column - startColumn + 1, "<>"); + TargetFile.Sheet.Range["A1"].Activate(); + } + } +} \ No newline at end of file diff --git a/src/PriceListTools/CombineTool.cs b/src/PriceListTools/CombineTool.cs index cb47379..3222dae 100644 --- a/src/PriceListTools/CombineTool.cs +++ b/src/PriceListTools/CombineTool.cs @@ -5,7 +5,7 @@ using System.Linq; namespace RehauSku.PriceListTools { - internal class CombineTool : PriceListTool + internal class CombineTool : AbstractTool { public List SourceFiles; diff --git a/src/PriceListTools/ConvertTool.cs b/src/PriceListTools/ConvertTool.cs index d10d65e..7308be9 100644 --- a/src/PriceListTools/ConvertTool.cs +++ b/src/PriceListTools/ConvertTool.cs @@ -3,7 +3,7 @@ using System; namespace RehauSku.PriceListTools { - internal class ConvertTool : PriceListTool + internal class ConvertTool : AbstractTool { private Source Current; diff --git a/src/PriceListTools/ExportTool.cs b/src/PriceListTools/ExportTool.cs index 568145d..562c1b2 100644 --- a/src/PriceListTools/ExportTool.cs +++ b/src/PriceListTools/ExportTool.cs @@ -6,7 +6,7 @@ using RehauSku.Interface; namespace RehauSku.PriceListTools { - internal class ExportTool : PriceListTool + internal class ExportTool : AbstractTool { private Dictionary PositionAmount; private Range Selection; diff --git a/src/PriceListTools/MergeTool.cs b/src/PriceListTools/MergeTool.cs index a51b9b7..d31cc8a 100644 --- a/src/PriceListTools/MergeTool.cs +++ b/src/PriceListTools/MergeTool.cs @@ -4,7 +4,7 @@ using System.Linq; namespace RehauSku.PriceListTools { - internal class MergeTool : PriceListTool + internal class MergeTool : AbstractTool { public List SourceFiles; diff --git a/src/PriceListTools/PriceListTool.cs b/src/PriceListTools/PriceListTool.cs deleted file mode 100644 index 0a82a41..0000000 --- a/src/PriceListTools/PriceListTool.cs +++ /dev/null @@ -1,175 +0,0 @@ -using ExcelDna.Integration; -using Microsoft.Office.Interop.Excel; -using System; -using System.Collections.Generic; -using System.Windows.Forms; -using Application = Microsoft.Office.Interop.Excel.Application; - -namespace RehauSku.PriceListTools -{ - internal abstract class PriceListTool - { - protected private Application ExcelApp = (Application)ExcelDnaUtil.Application; - protected private Target TargetFile; - - public void OpenNewPrice() - { - Workbook wb = ExcelApp.Workbooks.Open(RegistryUtil.PriceListPath); - - try - { - TargetFile = new Target(wb); - } - - catch (Exception ex) - { - MessageBox.Show - (ex.Message, - "Ошибка открытия шаблонного прайс-листа", - MessageBoxButtons.OK, - MessageBoxIcon.Information); - wb.Close(); - throw ex; - } - } - - protected private void FillColumnsWithDictionary(KeyValuePair positionAmount, params int[] columns) - { - int? row = GetPositionRow(positionAmount.Key.Sku, positionAmount.Key.Group, TargetFile.skuCell.Column); - - if (row != null) - { - foreach (int column in columns) - { - Range sumCell = TargetFile.Sheet.Cells[row, column]; - - if (sumCell.Value2 == null) - { - sumCell.Value2 = positionAmount.Value; - } - - else - { - sumCell.Value2 += positionAmount.Value; - } - } - } - - else - { - string sku = positionAmount.Key.Sku.Substring(1, 6); - - row = GetPositionRow(sku, positionAmount.Key.Group, TargetFile.skuCell.Column); - - if (row != null) - { - foreach (int column in columns) - { - Range amountCell = TargetFile.Sheet.Cells[row, column]; - - if (amountCell.Value2 == null) - { - amountCell.Value2 = positionAmount.Value; - } - - else - { - amountCell.Value2 += positionAmount.Value; - } - - Range oldSkuCell = TargetFile.Sheet.Cells[row, TargetFile.oldSkuCell.Column]; - oldSkuCell.Value2 = positionAmount.Key.Sku; - } - } - - else - { - FillMissing(positionAmount, columns); - } - } - } - - protected private void FillMissing(KeyValuePair positionAmount, params int[] columns) - { - Range foundCell = TargetFile.oldSkuCell.EntireColumn.Find(positionAmount.Key.Sku); - int row; - - if (foundCell == null) - { - row = TargetFile.Sheet.Cells[TargetFile.Sheet.Rows.Count, TargetFile.skuCell.Column] - .End[XlDirection.xlUp] - .Row + 1; - - TargetFile.Sheet.Rows[row] - .EntireRow - .Insert(XlInsertShiftDirection.xlShiftDown, XlInsertFormatOrigin.xlFormatFromLeftOrAbove); - - Range previous = TargetFile.Sheet.Rows[row - 1]; - Range current = TargetFile.Sheet.Rows[row]; - - previous.Copy(current); - current.ClearContents(); - - TargetFile.Sheet.Cells[row, TargetFile.groupCell.Column].Value2 = positionAmount.Key.Group; - TargetFile.Sheet.Cells[row, TargetFile.oldSkuCell.Column].Value2 = positionAmount.Key.Sku; - TargetFile.Sheet.Cells[row, TargetFile.nameCell.Column].Value2 = positionAmount.Key.Name; - TargetFile.Sheet.Cells[row, TargetFile.skuCell.Column].Value2 = "Не найден"; - } - - else - { - row = foundCell.Row; - } - - foreach (int column in columns) - { - if (TargetFile.Sheet.Cells[row, column].Value2 == null) - { - TargetFile.Sheet.Cells[row, column].Value2 = positionAmount.Value; - } - - else - { - TargetFile.Sheet.Cells[row, column].Value2 += positionAmount.Value; - } - } - } - - protected private int? GetPositionRow(string sku, string group, int column) - { - int? row = null; - Range foundCell = TargetFile.Sheet.Columns[column].Find(sku); - string foundGroupValue; - - if (foundCell == null) return null; - - else - { - row = foundCell.Row; - foundGroupValue = TargetFile.Sheet.Cells[foundCell.Row, TargetFile.groupCell.Column].Value2.ToString(); - } - - if (string.IsNullOrEmpty(group) || group.Equals(foundGroupValue)) - return row; - - else - while (true) - { - foundCell = TargetFile.skuCell.EntireColumn.FindNext(foundCell); - if (foundCell == null) return row; - - foundGroupValue = TargetFile.Sheet.Cells[foundCell.Row, TargetFile.groupCell.Column].Value2.ToString(); - if (group.Equals(foundGroupValue)) return foundCell.Row; - } - } - - protected private void FilterByAmount() - { - AutoFilter filter = TargetFile.Sheet.AutoFilter; - int startColumn = filter.Range.Column; - - filter.Range.AutoFilter(TargetFile.amountCell.Column - startColumn + 1, "<>"); - TargetFile.Sheet.Range["A1"].Activate(); - } - } -} \ No newline at end of file -- cgit v1.2.3