From 913de67a023b877f5c7ab5cd2dfa0517ae48915a Mon Sep 17 00:00:00 2001 From: Sergey Chebotar Date: Sat, 29 Jan 2022 17:47:57 +0300 Subject: Fix missing positions filling into amount columns during joining sheets --- src/PriceListTools/PriceListTool.cs | 14 +++++++++----- 1 file changed, 9 insertions(+), 5 deletions(-) (limited to 'src/PriceListTools/PriceListTool.cs') diff --git a/src/PriceListTools/PriceListTool.cs b/src/PriceListTools/PriceListTool.cs index f6231a5..09d4dac 100644 --- a/src/PriceListTools/PriceListTool.cs +++ b/src/PriceListTools/PriceListTool.cs @@ -65,7 +65,7 @@ namespace RehauSku.PriceListTools if (Missing.Count > 0) { - FillMissing(); + FillMissing(columns); MessageBox.Show ($"{Missing.Count} артикулов отсутствует в таблице заказов {RegistryUtil.PriceListPath}\n" + $"Под основной таблицей составлен список не найденных артикулов", @@ -159,7 +159,7 @@ namespace RehauSku.PriceListTools Missing.Remove(positionAmount); } - protected private void FillMissing() + protected private void FillMissing(int[] columns) { int startRow = TargetFile.Sheet.AutoFilter.Range.Row + @@ -170,17 +170,21 @@ namespace RehauSku.PriceListTools Range group = TargetFile.Sheet.Cells[startRow + i, TargetFile.groupCell.Column]; Range sku = TargetFile.Sheet.Cells[startRow + i, TargetFile.skuCell.Column]; Range name = TargetFile.Sheet.Cells[startRow + i, TargetFile.nameCell.Column]; - Range amount = TargetFile.Sheet.Cells[startRow + i, TargetFile.amountCell.Column]; group.Value2 = Missing[i].Key.Group; sku.Value2 = Missing[i].Key.Sku; name.Value2 = Missing[i].Key.Name; - amount.Value2 = Missing[i].Value; group.ClearFormats(); sku.ClearFormats(); name.ClearFormats(); - amount.ClearFormats(); + + foreach (int column in columns) + { + Range amount = TargetFile.Sheet.Cells[startRow + i, column]; + amount.Value2 = Missing[i].Value; + amount.ClearFormats(); + } } } -- cgit v1.2.3 From cfb15d1279f604cc7387e1e23680ba8a63c9f110 Mon Sep 17 00:00:00 2001 From: Sergey Chebotar Date: Mon, 31 Jan 2022 17:54:18 +0300 Subject: Fix Export Tool to use Price List Tool base methods --- src/PriceListTools/PriceListTool.cs | 4 ++++ 1 file changed, 4 insertions(+) (limited to 'src/PriceListTools/PriceListTool.cs') diff --git a/src/PriceListTools/PriceListTool.cs b/src/PriceListTools/PriceListTool.cs index 09d4dac..f87aa16 100644 --- a/src/PriceListTools/PriceListTool.cs +++ b/src/PriceListTools/PriceListTool.cs @@ -89,6 +89,8 @@ namespace RehauSku.PriceListTools while (foundCell != null && foundCellGroup != positionAmount.Key.Group) { + if (positionAmount.Key.Group == null) break; + foundCell = TargetFile.skuCell.EntireColumn.FindNext(foundCell); foundCellGroup = TargetFile.Sheet.Cells[foundCell.Row, TargetFile.groupCell.Column].Value2.ToString(); } @@ -132,6 +134,8 @@ namespace RehauSku.PriceListTools while (foundCell != null && foundCellGroup != positionAmount.Key.Group) { + if (positionAmount.Key.Group == null) break; + foundCell = TargetFile.skuCell.EntireColumn.FindNext(foundCell); foundCellGroup = TargetFile.Sheet.Cells[foundCell.Row, TargetFile.groupCell.Column].Value2.ToString(); } -- cgit v1.2.3 From 7e5020ec253df1d0d32304ba72cdaa62937bb26b Mon Sep 17 00:00:00 2001 From: Sergey Chebotar Date: Tue, 1 Feb 2022 20:32:29 +0300 Subject: Refactoring, Not fount table formatting etc --- src/PriceListTools/PriceListTool.cs | 183 +++++++++++++++--------------------- 1 file changed, 77 insertions(+), 106 deletions(-) (limited to 'src/PriceListTools/PriceListTool.cs') diff --git a/src/PriceListTools/PriceListTool.cs b/src/PriceListTools/PriceListTool.cs index f87aa16..0a82a41 100644 --- a/src/PriceListTools/PriceListTool.cs +++ b/src/PriceListTools/PriceListTool.cs @@ -11,7 +11,6 @@ namespace RehauSku.PriceListTools { protected private Application ExcelApp = (Application)ExcelDnaUtil.Application; protected private Target TargetFile; - protected private List> Missing; public void OpenNewPrice() { @@ -34,162 +33,134 @@ namespace RehauSku.PriceListTools } } - protected private void FillColumnsWithDictionary(IEnumerable> dictionary, params int[] columns) + protected private void FillColumnsWithDictionary(KeyValuePair positionAmount, params int[] columns) { - Missing = new List>(); + int? row = GetPositionRow(positionAmount.Key.Sku, positionAmount.Key.Group, TargetFile.skuCell.Column); - foreach (var positionAmount in dictionary) + if (row != null) { - FillPositionAmountToColumns(positionAmount, columns); - } - - if (Missing.Count > 0) - { - DialogResult result = - MessageBox.Show - ($"{Missing.Count} артикулов отсутствует в таблице заказов {RegistryUtil.PriceListPath} Попробовать найти новый вариант?", - "Отсутствует позиция в конечной таблице заказов", - MessageBoxButtons.YesNo, - MessageBoxIcon.Information); - - if (result == DialogResult.Yes) + foreach (int column in columns) { - var lookUp = new List>(Missing); + Range sumCell = TargetFile.Sheet.Cells[row, column]; + + if (sumCell.Value2 == null) + { + sumCell.Value2 = positionAmount.Value; + } - foreach (var missingPosition in lookUp) + else { - TryFillVariantlessSkuToColumns(missingPosition, columns); + sumCell.Value2 += positionAmount.Value; } } } - if (Missing.Count > 0) + else { - FillMissing(columns); - MessageBox.Show - ($"{Missing.Count} артикулов отсутствует в таблице заказов {RegistryUtil.PriceListPath}\n" + - $"Под основной таблицей составлен список не найденных артикулов", - "Отсутствует позиция в конечной таблице заказов", - MessageBoxButtons.OK, - MessageBoxIcon.Information); - } - } - - protected private void FillPositionAmountToColumns(KeyValuePair positionAmount, int[] columns) - { - Range foundCell = TargetFile.skuCell.EntireColumn.Find(positionAmount.Key.Sku); + string sku = positionAmount.Key.Sku.Substring(1, 6); - if (foundCell == null) - { - Missing.Add(positionAmount); - return; - } + row = GetPositionRow(sku, positionAmount.Key.Group, TargetFile.skuCell.Column); - string foundCellGroup = TargetFile.Sheet.Cells[foundCell.Row, TargetFile.groupCell.Column].Value2.ToString(); + if (row != null) + { + foreach (int column in columns) + { + Range amountCell = TargetFile.Sheet.Cells[row, column]; - while (foundCell != null && foundCellGroup != positionAmount.Key.Group) - { - if (positionAmount.Key.Group == null) break; - - foundCell = TargetFile.skuCell.EntireColumn.FindNext(foundCell); - foundCellGroup = TargetFile.Sheet.Cells[foundCell.Row, TargetFile.groupCell.Column].Value2.ToString(); - } + if (amountCell.Value2 == null) + { + amountCell.Value2 = positionAmount.Value; + } - if (foundCell == null) - { - Missing.Add(positionAmount); - } + else + { + amountCell.Value2 += positionAmount.Value; + } - else - { - foreach (var column in columns) - { - Range sumCell = TargetFile.Sheet.Cells[foundCell.Row, column]; - - if (sumCell.Value2 == null) - { - sumCell.Value2 = positionAmount.Value; + Range oldSkuCell = TargetFile.Sheet.Cells[row, TargetFile.oldSkuCell.Column]; + oldSkuCell.Value2 = positionAmount.Key.Sku; } + } - else - { - sumCell.Value2 += positionAmount.Value; - } + else + { + FillMissing(positionAmount, columns); } } } - protected private void TryFillVariantlessSkuToColumns(KeyValuePair positionAmount, int[] columns) + protected private void FillMissing(KeyValuePair positionAmount, params int[] columns) { - string sku = positionAmount.Key.Sku.Substring(1, 6); - - Range foundCell = TargetFile.skuCell.EntireColumn.Find(sku); + Range foundCell = TargetFile.oldSkuCell.EntireColumn.Find(positionAmount.Key.Sku); + int row; if (foundCell == null) { - return; - } + row = TargetFile.Sheet.Cells[TargetFile.Sheet.Rows.Count, TargetFile.skuCell.Column] + .End[XlDirection.xlUp] + .Row + 1; - string foundCellGroup = TargetFile.Sheet.Cells[foundCell.Row, TargetFile.groupCell.Column].Value2.ToString(); + TargetFile.Sheet.Rows[row] + .EntireRow + .Insert(XlInsertShiftDirection.xlShiftDown, XlInsertFormatOrigin.xlFormatFromLeftOrAbove); - while (foundCell != null && foundCellGroup != positionAmount.Key.Group) - { - if (positionAmount.Key.Group == null) break; + Range previous = TargetFile.Sheet.Rows[row - 1]; + Range current = TargetFile.Sheet.Rows[row]; - foundCell = TargetFile.skuCell.EntireColumn.FindNext(foundCell); - foundCellGroup = TargetFile.Sheet.Cells[foundCell.Row, TargetFile.groupCell.Column].Value2.ToString(); + 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 = "Не найден"; } - if (foundCell == null) + else { - return; + row = foundCell.Row; } - foreach (var column in columns) + foreach (int column in columns) { - Range sumCell = TargetFile.Sheet.Cells[foundCell.Row, column]; - - if (sumCell.Value2 == null) + if (TargetFile.Sheet.Cells[row, column].Value2 == null) { - sumCell.Value2 = positionAmount.Value; + TargetFile.Sheet.Cells[row, column].Value2 = positionAmount.Value; } else { - sumCell.Value2 += positionAmount.Value; + TargetFile.Sheet.Cells[row, column].Value2 += positionAmount.Value; } } - - Missing.Remove(positionAmount); } - protected private void FillMissing(int[] columns) + protected private int? GetPositionRow(string sku, string group, int column) { - int startRow = - TargetFile.Sheet.AutoFilter.Range.Row + - TargetFile.Sheet.AutoFilter.Range.Rows.Count + 5; + int? row = null; + Range foundCell = TargetFile.Sheet.Columns[column].Find(sku); + string foundGroupValue; - for (int i = 0; i < Missing.Count; i++) - { - Range group = TargetFile.Sheet.Cells[startRow + i, TargetFile.groupCell.Column]; - Range sku = TargetFile.Sheet.Cells[startRow + i, TargetFile.skuCell.Column]; - Range name = TargetFile.Sheet.Cells[startRow + i, TargetFile.nameCell.Column]; + if (foundCell == null) return null; - group.Value2 = Missing[i].Key.Group; - sku.Value2 = Missing[i].Key.Sku; - name.Value2 = Missing[i].Key.Name; + else + { + row = foundCell.Row; + foundGroupValue = TargetFile.Sheet.Cells[foundCell.Row, TargetFile.groupCell.Column].Value2.ToString(); + } - group.ClearFormats(); - sku.ClearFormats(); - name.ClearFormats(); + if (string.IsNullOrEmpty(group) || group.Equals(foundGroupValue)) + return row; - foreach (int column in columns) + else + while (true) { - Range amount = TargetFile.Sheet.Cells[startRow + i, column]; - amount.Value2 = Missing[i].Value; - amount.ClearFormats(); + 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() -- cgit v1.2.3