aboutsummaryrefslogtreecommitdiff
path: root/src/PriceListTools
diff options
context:
space:
mode:
Diffstat (limited to 'src/PriceListTools')
-rw-r--r--src/PriceListTools/AbstractPriceListTool.cs65
-rw-r--r--src/PriceListTools/CombineTool.cs76
-rw-r--r--src/PriceListTools/ExportTool.cs84
-rw-r--r--src/PriceListTools/MergeTool.cs103
-rw-r--r--src/PriceListTools/PriceList.cs97
-rw-r--r--src/PriceListTools/PriceListSheet.cs64
-rw-r--r--src/PriceListTools/PriceListUtil.cs41
7 files changed, 312 insertions, 218 deletions
diff --git a/src/PriceListTools/AbstractPriceListTool.cs b/src/PriceListTools/AbstractPriceListTool.cs
new file mode 100644
index 0000000..1aef0be
--- /dev/null
+++ b/src/PriceListTools/AbstractPriceListTool.cs
@@ -0,0 +1,65 @@
+using ExcelDna.Integration;
+using Microsoft.Office.Interop.Excel;
+using System;
+using System.Collections.Generic;
+
+namespace RehauSku.PriceListTools
+{
+ internal abstract class AbstractPriceListTool
+ {
+ protected private Application ExcelApp;
+ protected private PriceList NewPriceList;
+ protected private List<PriceList> sourcePriceLists;
+
+ public AbstractPriceListTool()
+ {
+ ExcelApp = (Application)ExcelDnaUtil.Application;
+ sourcePriceLists = new List<PriceList>();
+ }
+
+ public void OpenNewPrice(string path)
+ {
+ Workbook wb = ExcelApp.Workbooks.Open(path);
+
+ try
+ {
+ NewPriceList = new PriceList(wb);
+
+ if (NewPriceList.Sheets.Count == 0)
+ throw new ArgumentException($"Не найдены листы с артикулами в {wb.Name}");
+
+ if (NewPriceList.OfferSheet == null)
+ throw new ArgumentException($"Нет листа для коммерческого предложения в {wb.Name}");
+ }
+
+ catch (Exception ex)
+ {
+ wb.Close();
+ throw ex;
+ }
+ }
+
+ public virtual void GetSource()
+ {
+ throw new NotImplementedException();
+ }
+
+ public virtual void GetSource(string[] files)
+ {
+ ExcelApp.ScreenUpdating = false;
+ foreach (string file in files)
+ {
+ Workbook wb = ExcelApp.Workbooks.Open(file);
+ PriceList priceList = new PriceList(wb);
+ sourcePriceLists.Add(priceList);
+ wb.Close();
+ }
+ ExcelApp.ScreenUpdating = true;
+ }
+
+ public virtual void FillPriceList()
+ {
+ throw new NotImplementedException();
+ }
+ }
+} \ No newline at end of file
diff --git a/src/PriceListTools/CombineTool.cs b/src/PriceListTools/CombineTool.cs
new file mode 100644
index 0000000..cf02059
--- /dev/null
+++ b/src/PriceListTools/CombineTool.cs
@@ -0,0 +1,76 @@
+using Microsoft.Office.Interop.Excel;
+using System;
+
+namespace RehauSku.PriceListTools
+{
+ internal class CombineTool : AbstractPriceListTool, IDisposable
+ {
+ public override void FillPriceList()
+ {
+ PriceListSheet offer = NewPriceList.OfferSheet;
+ offer.Sheet.Activate();
+
+ int exportedValues = 0;
+ int exportedLists = 0;
+
+ foreach (var priceList in sourcePriceLists)
+ {
+ foreach (var sheet in priceList.Sheets)
+ {
+ if (sheet.SkuAmount.Count == 0)
+ continue;
+
+ offer.Sheet.Columns[offer.amountColumnNumber]
+ .EntireColumn
+ .Insert(XlInsertShiftDirection.xlShiftToRight, XlInsertFormatOrigin.xlFormatFromRightOrBelow);
+
+ exportedLists++;
+
+ foreach (var kvp in sheet.SkuAmount)
+ {
+ Range cell = offer.Sheet.Columns[offer.skuColumnNumber].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
+ {
+ offer.Sheet.Cells[cell.Row, offer.amountColumnNumber].Value2 = kvp.Value;
+ Range sumCell = offer.Sheet.Cells[cell.Row, offer.amountColumnNumber + exportedLists];
+
+ if (sumCell.Value2 == null)
+ sumCell.Value2 = kvp.Value;
+ else
+ sumCell.Value2 += kvp.Value;
+
+ exportedValues++;
+ }
+
+ offer.Sheet.Cells[offer.headerRowNumber, offer.amountColumnNumber].Value2 = $"{priceList.Name}\n{sheet.Name}";
+ }
+ }
+ }
+
+ AutoFilter filter = offer.Sheet.AutoFilter;
+ int firstFilterColumn = filter.Range.Column;
+
+ filter.Range.AutoFilter(offer.amountColumnNumber - firstFilterColumn + 1 + exportedLists, "<>");
+ offer.Sheet.Range["A1"].Activate();
+
+ AddIn.Excel.StatusBar = $"Экспортировано {exportedValues} строк из {sourcePriceLists.Count} файлов";
+
+ Forms.Dialog.SaveWorkbookAs();
+ }
+
+ public void Dispose()
+ {
+ GC.SuppressFinalize(this);
+ }
+ }
+}
diff --git a/src/PriceListTools/ExportTool.cs b/src/PriceListTools/ExportTool.cs
index 61f6f0f..a93097d 100644
--- a/src/PriceListTools/ExportTool.cs
+++ b/src/PriceListTools/ExportTool.cs
@@ -6,28 +6,29 @@ using System.Collections.Generic;
namespace RehauSku.PriceListTools
{
- class ExportTool : IDisposable
+ internal class ExportTool : AbstractPriceListTool, IDisposable
{
- private Application ExcelApp;
private Dictionary<string, double> SkuAmount { get; set; }
- private Range Selection { get; set; }
+ private Range Selection;
public ExportTool()
{
- this.ExcelApp = (Application)ExcelDnaUtil.Application;
+ ExcelApp = (Application)ExcelDnaUtil.Application;
Selection = ExcelApp.Selection;
-
- if (IsRangeValid())
- _FillSkuAmountDict();
}
- public bool IsRangeValid()
+ public override void GetSource()
{
- return Selection != null &&
- Selection.Columns.Count == 2;
+ if (Selection != null && Selection.Columns.Count == 2)
+ FillSkuAmountDict();
+
+ else throw new Exception("Неверный диапазон");
}
- private void _FillSkuAmountDict()
+ public override void GetSource(string[] files)
+ => GetSource();
+
+ private void FillSkuAmountDict()
{
object[,] cells = Selection.Value2;
SkuAmount = new Dictionary<string, double>();
@@ -72,44 +73,55 @@ namespace RehauSku.PriceListTools
}
}
- public void ExportToNewFile()
+ public override void FillPriceList()
{
- if (SkuAmount.Count < 1)
- {
- return;
- }
+ if (SkuAmount.Count < 1) return;
- string exportFile = PriceListUtil.CreateNewExportFile();
- Workbook wb = ExcelApp.Workbooks.Open(exportFile);
+ PriceListSheet offer = NewPriceList.OfferSheet;
+ offer.Sheet.Activate();
- try
- {
- PriceList priceList = new PriceList(wb);
- priceList.Fill(SkuAmount);
- }
+ int exportedValues = 0;
- catch(Exception ex)
+ foreach (var kvp in SkuAmount)
{
- System.Windows.Forms.MessageBox.Show
- ($"{RegistryUtil.PriceListPath} не является файлом прайс-листа \n\n {ex.Message}",
- "Неверный файл прайс-листа!",
- System.Windows.Forms.MessageBoxButtons.OK,
- System.Windows.Forms.MessageBoxIcon.Error);
+ Range cell = offer.Sheet.Columns[offer.skuColumnNumber].Find(kvp.Key);
- wb.Close();
+ if (cell == null)
+ {
+ System.Windows.Forms.MessageBox.Show
+ ($"Артикул {kvp.Key} отсутствует в таблице заказов {RegistryUtil.PriceListPath}",
+ "Отсутствует позиция в конечной таблице заказов",
+ System.Windows.Forms.MessageBoxButtons.OK,
+ System.Windows.Forms.MessageBoxIcon.Information);
+ }
+
+ else
+ {
+ Range sumCell = offer.Sheet.Cells[cell.Row, offer.amountColumnNumber];
+
+ if (sumCell.Value2 == null)
+ sumCell.Value2 = kvp.Value;
+ else
+ sumCell.Value2 += kvp.Value;
+
+ exportedValues++;
+ }
}
+
+ AutoFilter filter = offer.Sheet.AutoFilter;
+ int firstFilterColumn = filter.Range.Column;
+
+ filter.Range.AutoFilter(offer.amountColumnNumber - firstFilterColumn + 1, "<>");
+ offer.Sheet.Range["A1"].Activate();
+ AddIn.Excel.StatusBar = $"Экспортировано {exportedValues} строк из {SkuAmount.Count}";
+
+ Forms.Dialog.SaveWorkbookAs();
}
public void Dispose()
{
- Dispose(true);
GC.SuppressFinalize(this);
}
-
- protected virtual void Dispose(bool disposing)
- {
-
- }
}
}
diff --git a/src/PriceListTools/MergeTool.cs b/src/PriceListTools/MergeTool.cs
index ddf74d2..493f8a8 100644
--- a/src/PriceListTools/MergeTool.cs
+++ b/src/PriceListTools/MergeTool.cs
@@ -1,88 +1,65 @@
-using ExcelDna.Integration;
-using Microsoft.Office.Interop.Excel;
+using Microsoft.Office.Interop.Excel;
using System;
-using System.Collections.Generic;
namespace RehauSku.PriceListTools
{
- class MergeTool : IDisposable
+ internal class MergeTool : AbstractPriceListTool, IDisposable
{
- private Application ExcelApp;
- private Dictionary<string, double> SkuAmount { get; set; }
-
- public MergeTool()
+ public override void FillPriceList()
{
- this.ExcelApp = (Application)ExcelDnaUtil.Application;
- this.SkuAmount = new Dictionary<string, double>();
- }
+ PriceListSheet offer = NewPriceList.OfferSheet;
+ offer.Sheet.Activate();
- public void AddSkuAmountToDict(string[] files)
- {
- ExcelApp.ScreenUpdating = false;
- foreach (string file in files)
- {
- Workbook wb = ExcelApp.Workbooks.Open(file);
+ int exportedValues = 0;
- try
+ foreach (var priceList in sourcePriceLists)
+ {
+ foreach (var sheet in priceList.Sheets)
{
- PriceList priceList = new PriceList(wb);
- SkuAmount.AddValues(priceList);
- }
+ if (sheet.SkuAmount.Count == 0)
+ continue;
- catch (Exception ex)
- {
- System.Windows.Forms.MessageBox.Show
- ( $"{wb.Name} не является файлом прайс-листа \n\n {ex.Message}",
- "Неверный файл прайс-листа!",
- System.Windows.Forms.MessageBoxButtons.OK,
- System.Windows.Forms.MessageBoxIcon.Error);
- }
+ foreach (var kvp in sheet.SkuAmount)
+ {
+ Range cell = offer.Sheet.Columns[offer.skuColumnNumber].Find(kvp.Key);
- finally
- {
- wb.Close();
- }
- }
- ExcelApp.ScreenUpdating = true;
- }
+ if (cell == null)
+ {
+ System.Windows.Forms.MessageBox.Show
+ ($"Артикул {kvp.Key} отсутствует в таблице заказов {RegistryUtil.PriceListPath}",
+ "Отсутствует позиция в конечной таблице заказов",
+ System.Windows.Forms.MessageBoxButtons.OK,
+ System.Windows.Forms.MessageBoxIcon.Information);
+ }
- public void ExportToNewFile(string exportFile)
- {
- if (SkuAmount.Count < 1)
- {
- return;
- }
+ else
+ {
+ Range sumCell = offer.Sheet.Cells[cell.Row, offer.amountColumnNumber];
- Workbook wb = ExcelApp.Workbooks.Open(exportFile);
- PriceList priceList;
+ if (sumCell.Value2 == null)
+ sumCell.Value2 = kvp.Value;
+ else
+ sumCell.Value2 += kvp.Value;
- try
- {
- priceList = new PriceList(wb);
- priceList.Fill(SkuAmount);
+ exportedValues++;
+ }
+ }
+ }
}
- catch (Exception ex)
- {
- System.Windows.Forms.MessageBox.Show
- ($"{RegistryUtil.PriceListPath} не является файлом прайс-листа \n\n {ex.Message}",
- "Неверный файл прайс-листа!",
- System.Windows.Forms.MessageBoxButtons.OK,
- System.Windows.Forms.MessageBoxIcon.Error);
+ AutoFilter filter = offer.Sheet.AutoFilter;
+ int firstFilterColumn = filter.Range.Column;
- wb.Close();
- }
+ filter.Range.AutoFilter(offer.amountColumnNumber - firstFilterColumn + 1, "<>");
+ offer.Sheet.Range["A1"].Activate();
+ AddIn.Excel.StatusBar = $"Экспортировано {exportedValues} строк из {sourcePriceLists.Count} файлов";
+
+ Forms.Dialog.SaveWorkbookAs();
}
public void Dispose()
{
- Dispose(true);
GC.SuppressFinalize(this);
}
-
- protected virtual void Dispose(bool disposing)
- {
-
- }
}
}
diff --git a/src/PriceListTools/PriceList.cs b/src/PriceListTools/PriceList.cs
index 35b3f7d..bc11a17 100644
--- a/src/PriceListTools/PriceList.cs
+++ b/src/PriceListTools/PriceList.cs
@@ -1,100 +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<PriceListSheet> Sheets { get; private set; }
- private const string _amountHeader = "Кол-во";
- private const string _skuHeader = "Актуальный материал";
- private const string _offerSheetHeader = "КП";
+ private const string offerSheetHeader = "КП";
public PriceList(Workbook workbook)
{
- Workbook = workbook;
- OfferSheet = new PriceListSheet(workbook.Sheets[_offerSheetHeader]);
+ Name = workbook.Name;
+ Sheets = new List<PriceListSheet>();
- Worksheet active = workbook.ActiveSheet;
-
- if (active.Name == _offerSheetHeader)
- ActiveSheet = OfferSheet;
-
- else
- ActiveSheet = new PriceListSheet(active);
- }
-
- public bool IsValid()
- {
- return OfferSheet.IsValid() &&
- ActiveSheet.IsValid();
- }
-
- public void Fill(Dictionary<string, double> values)
- {
- Worksheet ws = OfferSheet.sheet;
- ws.Activate();
-
- int amountColumn = OfferSheet.amountColumn.Value;
- int skuColumn = OfferSheet.skuColumn.Value;
- int exportedValues = 0;
-
- foreach (KeyValuePair<string, double> 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);
- }
- else
- {
- ws.Cells[cell.Row, amountColumn].Value = kvp.Value;
- exportedValues++;
- }
- }
+ PriceListSheet priceListSheet = new PriceListSheet(worksheet);
- AutoFilter filter = ws.AutoFilter;
- int firstFilterColumn = filter.Range.Column;
+ if (priceListSheet.FillSkuAmount())
+ Sheets.Add(priceListSheet);
+ }
- 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 class PriceListSheet
+ public static string CreateNewFile()
{
- public readonly Worksheet sheet;
- public readonly int? headerRow;
- public readonly int? amountColumn;
- public readonly int? skuColumn;
- public object[,] amountCells;
- public object[,] skuCells;
-
- 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;
- }
-
- public bool IsValid()
- {
- return sheet != null &&
- headerRow != null &&
- amountColumn != null &&
- skuColumn != null;
- }
+ File.Copy(RegistryUtil.PriceListPath, path);
+ return path;
}
}
}
diff --git a/src/PriceListTools/PriceListSheet.cs b/src/PriceListTools/PriceListSheet.cs
new file mode 100644
index 0000000..8a34c2f
--- /dev/null
+++ b/src/PriceListTools/PriceListSheet.cs
@@ -0,0 +1,64 @@
+using Microsoft.Office.Interop.Excel;
+using System.Collections.Generic;
+
+namespace RehauSku.PriceListTools
+{
+ internal class PriceListSheet
+ {
+ private const string amountHeader = "Кол-во";
+ private const string skuHeader = "Актуальный материал";
+
+ public readonly Worksheet Sheet;
+ public readonly string Name;
+ public Dictionary<string, double> SkuAmount { get; private set; }
+ public int headerRowNumber { get; private set; }
+ public int amountColumnNumber { get; private set; }
+ public int skuColumnNumber { get; private set; }
+
+ public PriceListSheet(Worksheet sheet)
+ {
+ Sheet = sheet;
+ Name = sheet.Name;
+ SkuAmount = new Dictionary<string, double>();
+
+ FillSkuAmount();
+ }
+
+ public bool FillSkuAmount()
+ {
+ Range amountCell = Sheet.Cells.Find(amountHeader);
+ Range skuCell = Sheet.Cells.Find(skuHeader);
+
+ if (amountCell == null || skuCell == null)
+ {
+ AddIn.Excel.StatusBar = $"Лист {Name} не распознан";
+ return false;
+ }
+
+ headerRowNumber = amountCell.Row;
+ skuColumnNumber = skuCell.Column;
+ amountColumnNumber = amountCell.Column;
+
+ object[,] amountColumn = Sheet.Columns[amountColumnNumber].Value2;
+ object[,] skuColumn = Sheet.Columns[skuColumnNumber].Value2;
+
+ for (int row = headerRowNumber + 1; row < amountColumn.GetLength(0); row++)
+ {
+ object amount = amountColumn[row, 1];
+ object sku = skuColumn[row, 1];
+
+ if (amount != null && (double)amount != 0)
+ {
+ if (SkuAmount.ContainsKey(sku.ToString()))
+ SkuAmount[sku.ToString()] += (double)amount;
+
+ else
+ SkuAmount.Add(sku.ToString(), (double)amount);
+ }
+ }
+ return true;
+ }
+ }
+
+}
+
diff --git a/src/PriceListTools/PriceListUtil.cs b/src/PriceListTools/PriceListUtil.cs
deleted file mode 100644
index 14797d9..0000000
--- a/src/PriceListTools/PriceListUtil.cs
+++ /dev/null
@@ -1,41 +0,0 @@
-using System.Collections.Generic;
-using System.IO;
-
-namespace RehauSku.PriceListTools
-{
- static class PriceListUtil
- {
- public static string CreateNewExportFile()
- {
- string fileExtension = Path.GetExtension(RegistryUtil.PriceListPath);
- string path = Path.GetTempFileName() + fileExtension;
-
- File.Copy(RegistryUtil.PriceListPath, path);
- return path;
- }
-
- public static void AddValues(this Dictionary<string, double> SkuAmount, PriceList priceList)
- {
- object[,] amountCells = priceList.ActiveSheet.amountCells;
- object[,] skuCells = priceList.ActiveSheet.skuCells;
-
- for (int row = priceList.ActiveSheet.headerRow.Value + 1; row < amountCells.GetLength(0); row++)
- {
- object amount = amountCells[row, 1];
- object sku = skuCells[row, 1];
-
- if (amount != null && (double)amount != 0)
- {
- if (SkuAmount.ContainsKey(sku.ToString()))
- {
- SkuAmount[sku.ToString()] += (double)amount;
- }
-
- else
- SkuAmount.Add(sku.ToString(), (double)amount);
- }
- }
- }
- }
-}
-
generated by cgit v1.2.3 (git 2.25.1) at 2025-04-21 05:46:06 +0000