aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--src/AddIn/RegistryUtil.cs31
-rw-r--r--src/PriceListTools/CombineTool.cs6
-rw-r--r--src/PriceListTools/ConvertTool.cs6
-rw-r--r--src/PriceListTools/ExportTool.cs63
-rw-r--r--src/PriceListTools/MergeTool.cs7
-rw-r--r--src/PriceListTools/Position.cs3
-rw-r--r--src/PriceListTools/PriceListTool.cs183
-rw-r--r--src/PriceListTools/Source.cs21
-rw-r--r--src/PriceListTools/Target.cs18
-rw-r--r--src/Ribbon/RibbonController.cs9
10 files changed, 152 insertions, 195 deletions
diff --git a/src/AddIn/RegistryUtil.cs b/src/AddIn/RegistryUtil.cs
index ceee2fe..3ec6f6a 100644
--- a/src/AddIn/RegistryUtil.cs
+++ b/src/AddIn/RegistryUtil.cs
@@ -1,8 +1,8 @@
using Microsoft.Win32;
-using System.IO;
using RehauSku.Forms;
+using System;
+using System.IO;
using System.Windows.Forms;
-using ExcelDna.Integration;
namespace RehauSku
{
@@ -24,22 +24,27 @@ namespace RehauSku
RootKey.Close();
}
- public static bool IsPriceListPathEmpty()
- {
- return string.IsNullOrEmpty(priceListPath);
- }
-
public static string PriceListPath
{
get
{
- if (IsPriceListPathEmpty() || !File.Exists(priceListPath))
+ if (string.IsNullOrEmpty(priceListPath) || !File.Exists(priceListPath))
{
- //MessageBox.Show("Прайс-лист отсутствует или неверный файл прайс-листа", "Укажите файл прайс-листа", MessageBoxButtons.OK, MessageBoxIcon.Warning);
- string fileName = Dialog.GetFilePath();
- priceListPath = fileName;
- RootKey.SetValue("PriceListPath", fileName);
- return priceListPath;
+ DialogResult result = MessageBox.Show("Прайс-лист отсутствует или неверный файл шаблона прайс-листа. " +
+ "Укажите файл шаблона прайс-листа.",
+ "Нет файла шаблона",
+ MessageBoxButtons.OK, MessageBoxIcon.Warning);
+
+ if (result == DialogResult.OK)
+ {
+ string fileName = Dialog.GetFilePath();
+ priceListPath = fileName;
+ RootKey.SetValue("PriceListPath", fileName);
+ return priceListPath;
+ }
+
+ else
+ throw new Exception("Нет файла шаблона");
}
else
diff --git a/src/PriceListTools/CombineTool.cs b/src/PriceListTools/CombineTool.cs
index dc0f2af..da29e4d 100644
--- a/src/PriceListTools/CombineTool.cs
+++ b/src/PriceListTools/CombineTool.cs
@@ -9,8 +9,6 @@ namespace RehauSku.PriceListTools
public void FillTarget()
{
- ExcelApp.ScreenUpdating = false;
-
foreach (Source source in SourceFiles)
{
TargetFile.Sheet.Columns[TargetFile.amountCell.Column]
@@ -21,11 +19,11 @@ namespace RehauSku.PriceListTools
newColumnHeader.Value2 = $"{source.Name}";
newColumnHeader.WrapText = true;
- FillColumnsWithDictionary(source.PositionAmount, TargetFile.amountCell.Column - 1, TargetFile.amountCell.Column);
+ foreach(var kvp in source.PositionAmount)
+ FillColumnsWithDictionary(kvp, TargetFile.amountCell.Column - 1, TargetFile.amountCell.Column);
}
FilterByAmount();
- ExcelApp.ScreenUpdating = true;
Forms.Dialog.SaveWorkbookAs();
}
diff --git a/src/PriceListTools/ConvertTool.cs b/src/PriceListTools/ConvertTool.cs
index 48e93d2..f37bfe2 100644
--- a/src/PriceListTools/ConvertTool.cs
+++ b/src/PriceListTools/ConvertTool.cs
@@ -26,10 +26,10 @@ namespace RehauSku.PriceListTools
public void FillTarget()
{
- ExcelApp.ScreenUpdating = false;
- FillColumnsWithDictionary(Current.PositionAmount, TargetFile.amountCell.Column);
+ foreach (var kvp in Current.PositionAmount)
+ FillColumnsWithDictionary(kvp, TargetFile.amountCell.Column);
+
FilterByAmount();
- ExcelApp.ScreenUpdating = true;
Forms.Dialog.SaveWorkbookAs();
}
diff --git a/src/PriceListTools/ExportTool.cs b/src/PriceListTools/ExportTool.cs
index 8bf274a..bfb3d8a 100644
--- a/src/PriceListTools/ExportTool.cs
+++ b/src/PriceListTools/ExportTool.cs
@@ -7,7 +7,7 @@ namespace RehauSku.PriceListTools
{
internal class ExportTool : PriceListTool
{
- private Dictionary<string, double> SkuAmount { get; set; }
+ private Dictionary<Position, double> PositionAmount;
private Range Selection;
public void TryGetSelection()
@@ -22,59 +22,23 @@ namespace RehauSku.PriceListTools
public void FillTarget()
{
- ExcelApp.ScreenUpdating = false;
GetSelected();
- FillColumn(SkuAmount, TargetFile.amountCell.Column);
- FilterByAmount();
- ExcelApp.ScreenUpdating = true;
-
- Forms.Dialog.SaveWorkbookAs();
- }
- private void FillColumn(IEnumerable<KeyValuePair<string, double>> dictionary, int column)
- {
- List<KeyValuePair<string, double>> missing = new List<KeyValuePair<string, double>>();
-
- foreach (var kvp in dictionary)
+ foreach (var kvp in PositionAmount)
{
- Range cell = TargetFile.skuCell.EntireColumn.Find(kvp.Key);
-
- if (cell == null)
- {
- missing.Add(kvp);
- }
-
- else
- {
- Range sumCell = TargetFile.Sheet.Cells[cell.Row, column];
-
- if (sumCell.Value2 == null)
- {
- sumCell.Value2 = kvp.Value;
- }
-
- else
- {
- sumCell.Value2 += kvp.Value;
- }
- }
+ FillColumnsWithDictionary(kvp, TargetFile.amountCell.Column);
}
- if (missing.Count > 0)
- {
- System.Windows.Forms.MessageBox.Show
- ($"{missing.Count} артикулов отсутствует в таблице заказов {RegistryUtil.PriceListPath} Попробовать найти новый вариант?",
- "Отсутствует позиция в конечной таблице заказов",
- System.Windows.Forms.MessageBoxButtons.YesNo,
- System.Windows.Forms.MessageBoxIcon.Information);
- }
- }
+ FilterByAmount();
+ Forms.Dialog.SaveWorkbookAs();
+ }
private void GetSelected()
{
- object[,] cells = Selection.Value2;
- SkuAmount = new Dictionary<string, double>();
+ object[,] cells = Selection.Value2;
+ PositionAmount = new Dictionary<Position, double>();
+
int rowsCount = Selection.Rows.Count;
for (int row = 1; row <= rowsCount; row++)
@@ -111,13 +75,16 @@ namespace RehauSku.PriceListTools
continue;
}
- if (SkuAmount.ContainsKey(sku))
+ Position position = new Position(null, sku, null);
+
+ if (PositionAmount.ContainsKey(position))
{
- SkuAmount[sku] += amount.Value;
+ PositionAmount[position] += amount.Value;
}
+
else
{
- SkuAmount.Add(sku, amount.Value);
+ PositionAmount.Add(position, amount.Value);
}
}
}
diff --git a/src/PriceListTools/MergeTool.cs b/src/PriceListTools/MergeTool.cs
index 0d3e8eb..e196b3a 100644
--- a/src/PriceListTools/MergeTool.cs
+++ b/src/PriceListTools/MergeTool.cs
@@ -1,5 +1,4 @@
using System.Collections.Generic;
-using System.Linq;
namespace RehauSku.PriceListTools
{
@@ -9,15 +8,13 @@ namespace RehauSku.PriceListTools
public void FillTarget()
{
- ExcelApp.ScreenUpdating = false;
-
foreach (Source source in SourceFiles)
{
- FillColumnsWithDictionary(source.PositionAmount, TargetFile.amountCell.Column);
+ foreach (var kvp in source.PositionAmount)
+ FillColumnsWithDictionary(kvp, TargetFile.amountCell.Column);
}
FilterByAmount();
- ExcelApp.ScreenUpdating = true;
Forms.Dialog.SaveWorkbookAs();
}
diff --git a/src/PriceListTools/Position.cs b/src/PriceListTools/Position.cs
index 471aa59..0863642 100644
--- a/src/PriceListTools/Position.cs
+++ b/src/PriceListTools/Position.cs
@@ -13,5 +13,4 @@
Name = name;
}
}
-}
-
+} \ No newline at end of file
diff --git a/src/PriceListTools/PriceListTool.cs b/src/PriceListTools/PriceListTool.cs
index f6231a5..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<KeyValuePair<Position, double>> Missing;
public void OpenNewPrice()
{
@@ -34,154 +33,134 @@ namespace RehauSku.PriceListTools
}
}
- protected private void FillColumnsWithDictionary(IEnumerable<KeyValuePair<Position, double>> dictionary, params int[] columns)
+ protected private void FillColumnsWithDictionary(KeyValuePair<Position, double> positionAmount, params int[] columns)
{
- Missing = new List<KeyValuePair<Position, double>>();
+ 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<KeyValuePair<Position, double>>(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();
- MessageBox.Show
- ($"{Missing.Count} артикулов отсутствует в таблице заказов {RegistryUtil.PriceListPath}\n" +
- $"Под основной таблицей составлен список не найденных артикулов",
- "Отсутствует позиция в конечной таблице заказов",
- MessageBoxButtons.OK,
- MessageBoxIcon.Information);
- }
- }
-
- protected private void FillPositionAmountToColumns(KeyValuePair<Position, double> 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)
- {
- 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<Position, double> positionAmount, int[] columns)
+ protected private void FillMissing(KeyValuePair<Position, double> 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)
- {
- foundCell = TargetFile.skuCell.EntireColumn.FindNext(foundCell);
- foundCellGroup = TargetFile.Sheet.Cells[foundCell.Row, TargetFile.groupCell.Column].Value2.ToString();
+ 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 = "Не найден";
}
- 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()
+ 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;
+
+ if (foundCell == null) return null;
- for (int i = 0; i < Missing.Count; i++)
+ else
{
- 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();
+ 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()
diff --git a/src/PriceListTools/Source.cs b/src/PriceListTools/Source.cs
index 5013157..7cf56be 100644
--- a/src/PriceListTools/Source.cs
+++ b/src/PriceListTools/Source.cs
@@ -2,6 +2,7 @@
using Microsoft.Office.Interop.Excel;
using System;
using System.Collections.Generic;
+using System.Linq;
namespace RehauSku.PriceListTools
{
@@ -19,12 +20,15 @@ namespace RehauSku.PriceListTools
Sheet = workbook.ActiveSheet;
Name = workbook.Name;
- amountCell = Sheet.Cells.Find(amountHeader);
- skuCell = Sheet.Cells.Find(skuHeader);
- groupCell = Sheet.Cells.Find(groupHeader);
- nameCell = Sheet.Cells.Find(nameHeader);
+ Range[] cells = new []
+ {
+ amountCell = Sheet.Cells.Find(amountHeader),
+ skuCell = Sheet.Cells.Find(skuHeader),
+ groupCell = Sheet.Cells.Find(groupHeader),
+ nameCell = Sheet.Cells.Find(nameHeader)
+ };
- if (amountCell == null || skuCell == null || groupCell == null || nameCell == null)
+ if (cells.Any(x => x == null))
{
throw new ArgumentException($"Файл {Name} не распознан");
}
@@ -38,9 +42,9 @@ namespace RehauSku.PriceListTools
List<Source> sourceFiles = new List<Source>();
- ExcelApp.ScreenUpdating = false;
foreach (string file in files)
{
+ ExcelApp.ScreenUpdating = false;
Workbook wb = ExcelApp.Workbooks.Open(file);
try
{
@@ -57,8 +61,8 @@ namespace RehauSku.PriceListTools
System.Windows.Forms.MessageBoxIcon.Information);
wb.Close();
}
+ ExcelApp.ScreenUpdating = true;
}
- ExcelApp.ScreenUpdating = true;
return sourceFiles;
}
@@ -67,7 +71,7 @@ namespace RehauSku.PriceListTools
{
PositionAmount = new Dictionary<Position, double>();
- for (int row = amountCell.Row + 1; row < Sheet.AutoFilter.Range.Rows.Count; row++)
+ for (int row = amountCell.Row + 1; row <= Sheet.Cells[Sheet.Rows.Count, amountCell.Column].End[XlDirection.xlUp].Row; row++)
{
object amount = Sheet.Cells[row, amountCell.Column].Value2;
@@ -83,6 +87,7 @@ namespace RehauSku.PriceListTools
{
PositionAmount[p] += (double)amount;
}
+
else
{
PositionAmount.Add(p, (double)amount);
diff --git a/src/PriceListTools/Target.cs b/src/PriceListTools/Target.cs
index a7e87ec..996a092 100644
--- a/src/PriceListTools/Target.cs
+++ b/src/PriceListTools/Target.cs
@@ -1,21 +1,29 @@
using Microsoft.Office.Interop.Excel;
using System;
+using System.Linq;
namespace RehauSku.PriceListTools
{
internal class Target : PriceList
{
+ private const string oldSkuHeader = "Прежний материал";
+ public Range oldSkuCell { get; private set; }
+
public Target(Workbook workbook)
{
Sheet = workbook.ActiveSheet;
Name = workbook.FullName;
- amountCell = Sheet.Cells.Find(amountHeader);
- skuCell = Sheet.Cells.Find(skuHeader);
- groupCell = Sheet.Cells.Find(groupHeader);
- nameCell = Sheet.Cells.Find(nameHeader);
+ Range[] cells = new[]
+ {
+ amountCell = Sheet.Cells.Find(amountHeader),
+ skuCell = Sheet.Cells.Find(skuHeader),
+ groupCell = Sheet.Cells.Find(groupHeader),
+ nameCell = Sheet.Cells.Find(nameHeader),
+ oldSkuCell = Sheet.Cells.Find(oldSkuHeader)
+ };
- if (amountCell == null || skuCell == null || groupCell == null || nameCell == null)
+ if (cells.Any(x => x == null))
{
throw new ArgumentException($"Шаблон { Name } не является прайс-листом");
}
diff --git a/src/Ribbon/RibbonController.cs b/src/Ribbon/RibbonController.cs
index 9011a43..7a514bd 100644
--- a/src/Ribbon/RibbonController.cs
+++ b/src/Ribbon/RibbonController.cs
@@ -1,10 +1,9 @@
-using System.Runtime.InteropServices;
-using System.Windows.Forms;
-using ExcelDna.Integration.CustomUI;
-using RehauSku.PriceListTools;
+using ExcelDna.Integration.CustomUI;
using RehauSku.Forms;
+using RehauSku.PriceListTools;
using System;
-using System.Collections.Generic;
+using System.Runtime.InteropServices;
+using System.Windows.Forms;
namespace RehauSku.Ribbon
{
generated by cgit v1.2.3 (git 2.25.1) at 2025-04-20 23:51:31 +0000