aboutsummaryrefslogtreecommitdiff
path: root/src/Controllers
diff options
context:
space:
mode:
Diffstat (limited to 'src/Controllers')
-rw-r--r--src/Controllers/CombineTool.cs56
-rw-r--r--src/Controllers/ConvertTool.cs30
-rw-r--r--src/Controllers/ExportTool.cs99
-rw-r--r--src/Controllers/MergeTool.cs46
-rw-r--r--src/Controllers/RibbonController.cs137
-rw-r--r--src/Controllers/ToolBase.cs189
6 files changed, 557 insertions, 0 deletions
diff --git a/src/Controllers/CombineTool.cs b/src/Controllers/CombineTool.cs
new file mode 100644
index 0000000..75c0f51
--- /dev/null
+++ b/src/Controllers/CombineTool.cs
@@ -0,0 +1,56 @@
+using Microsoft.Office.Interop.Excel;
+using RhSolutions.Models;
+using System;
+using System.Collections.Generic;
+using System.Linq;
+using Dialog = RhSolutions.Models.Dialog;
+
+namespace RhSolutions.Controllers
+{
+ internal class CombineTool : ToolBase
+ {
+ private List<SourcePriceList> SourceFiles { get; set; }
+
+ public CombineTool()
+ {
+ string[] files = Dialog.GetMultiplyFiles();
+
+ if (files != null)
+ {
+ SourceFiles = SourcePriceList.GetSourceLists(files);
+ }
+
+ else
+ {
+ throw new Exception("Не выбраны файлы");
+ }
+ }
+
+ public override void FillTarget()
+ {
+ using (ProgressBar = new ProgressBar("Заполняю строки...", SourceFiles.Sum(file => file.PositionAmount.Count)))
+ using (ResultBar = new ResultBar())
+ {
+ foreach (SourcePriceList source in SourceFiles)
+ {
+ TargetFile.Sheet.Columns[TargetFile.AmountCell.Column]
+ .EntireColumn
+ .Insert(XlInsertShiftDirection.xlShiftToRight, XlInsertFormatOrigin.xlFormatFromRightOrBelow);
+
+ Range newColumnHeader = TargetFile.Sheet.Cells[TargetFile.AmountCell.Row, TargetFile.AmountCell.Column - 1];
+ newColumnHeader.Value2 = $"{source.Name}";
+ newColumnHeader.WrapText = true;
+
+ foreach (var kvp in source.PositionAmount)
+ {
+ FillPositionAmountToColumns(kvp, TargetFile.AmountCell.Column - 1, TargetFile.AmountCell.Column);
+ ProgressBar.Update();
+ }
+ }
+
+ FilterByAmount();
+ ResultBar.Update();
+ }
+ }
+ }
+}
diff --git a/src/Controllers/ConvertTool.cs b/src/Controllers/ConvertTool.cs
new file mode 100644
index 0000000..5b2cd4d
--- /dev/null
+++ b/src/Controllers/ConvertTool.cs
@@ -0,0 +1,30 @@
+using RhSolutions.Models;
+
+namespace RhSolutions.Controllers
+{
+ internal class ConvertTool : ToolBase
+ {
+ private SourcePriceList Current { get; set; }
+
+ public ConvertTool()
+ {
+ Current = new SourcePriceList(ExcelApp.ActiveWorkbook);
+ }
+
+ public override void FillTarget()
+ {
+ using (ProgressBar = new ProgressBar("Заполняю строки...", Current.PositionAmount.Count))
+ using (ResultBar = new ResultBar())
+ {
+ foreach (var kvp in Current.PositionAmount)
+ {
+ FillPositionAmountToColumns(kvp, TargetFile.AmountCell.Column);
+ ProgressBar.Update();
+ }
+
+ FilterByAmount();
+ ResultBar.Update();
+ }
+ }
+ }
+} \ No newline at end of file
diff --git a/src/Controllers/ExportTool.cs b/src/Controllers/ExportTool.cs
new file mode 100644
index 0000000..6d8c348
--- /dev/null
+++ b/src/Controllers/ExportTool.cs
@@ -0,0 +1,99 @@
+using Microsoft.Office.Interop.Excel;
+using System;
+using System.Collections.Generic;
+using RhSolutions.Models;
+
+namespace RhSolutions.Controllers
+{
+ internal class ExportTool : ToolBase
+ {
+ private Dictionary<Product, double> PositionAmount;
+ private readonly Range Selection;
+
+ public ExportTool()
+ {
+ Selection = ExcelApp.Selection;
+ GetSelected();
+
+ if (PositionAmount.Count == 0)
+ {
+ throw new Exception("В выделенном диапазоне не найдены позиции для экспорта");
+ }
+ }
+
+ public override void FillTarget()
+ {
+ using (ProgressBar = new ProgressBar("Заполняю строки...", PositionAmount.Count))
+ using (ResultBar = new ResultBar())
+ {
+ foreach (var kvp in PositionAmount)
+ {
+ FillPositionAmountToColumns(kvp, TargetFile.AmountCell.Column);
+ ProgressBar.Update();
+ }
+
+ FilterByAmount();
+ ResultBar.Update();
+ }
+ }
+
+ private void GetSelected()
+ {
+ object[,] cells = Selection.Value2;
+ PositionAmount = new Dictionary<Product, double>();
+
+ int rowsCount = Selection.Rows.Count;
+
+ for (int row = 1; row <= rowsCount; row++)
+ {
+ if (cells[row, 1] == null || cells[row, 2] == null)
+ continue;
+
+ string sku = null;
+ double? amount = null;
+
+ for (int column = 1; column <= 2; column++)
+ {
+ object current = cells[row, column];
+
+ if (Sku.TryParse(current.ToString(), out Sku rauSku))
+ {
+ sku = rauSku.ToString();
+ }
+
+ else if (current.GetType() == typeof(string)
+ && double.TryParse(current.ToString(), out _))
+ {
+ amount = double.Parse((string)current);
+ }
+
+ else if (current.GetType() == typeof(double))
+ {
+ amount = (double)current;
+ }
+ }
+
+ if (sku == null || amount == null)
+ {
+ continue;
+ }
+
+ Product position = new Product
+ {
+ ProductSku = sku
+ };
+
+ if (PositionAmount.ContainsKey(position))
+ {
+ PositionAmount[position] += amount.Value;
+ }
+
+ else
+ {
+ PositionAmount.Add(position, amount.Value);
+ }
+ }
+ }
+ }
+}
+
diff --git a/src/Controllers/MergeTool.cs b/src/Controllers/MergeTool.cs
new file mode 100644
index 0000000..dec8ff7
--- /dev/null
+++ b/src/Controllers/MergeTool.cs
@@ -0,0 +1,46 @@
+using RhSolutions.Models;
+using System;
+using System.Collections.Generic;
+using System.Linq;
+
+namespace RhSolutions.Controllers
+{
+ internal class MergeTool : ToolBase
+ {
+ private List<SourcePriceList> SourceFiles { get; set; }
+
+ public MergeTool()
+ {
+ string[] files = Dialog.GetMultiplyFiles();
+
+ if (files != null)
+ {
+ SourceFiles = SourcePriceList.GetSourceLists(files);
+ }
+
+ else
+ {
+ throw new Exception("Не выбраны файлы");
+ }
+ }
+
+ public override void FillTarget()
+ {
+ using (ProgressBar = new ProgressBar("Заполняю строки...", SourceFiles.Sum(x => x.PositionAmount.Count)))
+ using (ResultBar = new ResultBar())
+ {
+ foreach (SourcePriceList source in SourceFiles)
+ {
+ foreach (var kvp in source.PositionAmount)
+ {
+ FillPositionAmountToColumns(kvp, TargetFile.AmountCell.Column);
+ ProgressBar.Update();
+ }
+ }
+
+ FilterByAmount();
+ ResultBar.Update();
+ }
+ }
+ }
+}
diff --git a/src/Controllers/RibbonController.cs b/src/Controllers/RibbonController.cs
new file mode 100644
index 0000000..87c62e1
--- /dev/null
+++ b/src/Controllers/RibbonController.cs
@@ -0,0 +1,137 @@
+using ExcelDna.Integration.CustomUI;
+using Microsoft.Office.Interop.Excel;
+using RhSolutions.Services;
+using System;
+using System.IO;
+using System.Reflection;
+using System.Runtime.InteropServices;
+using System.Windows.Forms;
+
+namespace RhSolutions.Controllers
+{
+ [ComVisible(true)]
+ public class RibbonController : ExcelRibbon
+ {
+ private static IRibbonUI ribbonUi;
+
+ public override string GetCustomUI(string RibbonID)
+ {
+ return @"
+ <customUI onLoad='RibbonLoad' xmlns='http://schemas.microsoft.com/office/2006/01/customui'>
+ <ribbon>
+ <tabs>
+ <tab id='rau' label='RhSolutions'>
+ <group id='priceList' label='Прайс-лист'>
+ <button id='export' getEnabled='GetExportEnabled' label='Экспорт в новый файл' size='normal' imageMso='PivotExportToExcel' onAction='OnToolPressed'/>
+ <button id='convert' getEnabled='GetConvertEnabled' label='Актуализировать' size='normal' imageMso='FileUpdate' onAction='OnToolPressed'/>
+ <menu id='conjoinMenu' label='Объединить' imageMso='Copy'>
+ <button id='merge' label='Сложить' onAction='OnToolPressed'/>
+ <button id='combine' label='По колонкам' onAction='OnToolPressed'/>
+ </menu>
+ </group>
+ <group id='rausettings' getLabel='GetVersionLabel'>
+ <button id='setPriceList' getLabel='GetPriceListPathLabel' size='large' imageMso='TableExcelSpreadsheetInsert' onAction='OnSetPricePressed'/>
+ </group>
+ </tab>
+ </tabs>
+ </ribbon>
+ </customUI>";
+ }
+
+ public void RibbonLoad(IRibbonUI sender)
+ {
+ ribbonUi = sender;
+ }
+
+ public static void RefreshControl(string id)
+ {
+ if (ribbonUi != null)
+ {
+ ribbonUi.InvalidateControl(id);
+ }
+ }
+ public void OnSetPricePressed(IRibbonControl control)
+ {
+ string path = Models.Dialog.GetFilePath();
+
+ if (!string.IsNullOrEmpty(path))
+ {
+ RegistryUtil.PriceListPath = path;
+ }
+ }
+
+ public void OnToolPressed(IRibbonControl control)
+ {
+ try
+ {
+ ToolBase tool;
+ switch (control.Id)
+ {
+ case "export":
+ tool = new ExportTool();
+ break;
+ case "convert":
+ tool = new ConvertTool();
+ break;
+ case "merge":
+ tool = new MergeTool();
+ break;
+ case "combine":
+ tool = new CombineTool();
+ break;
+ default:
+ throw new Exception("Неизвестный инструмент");
+ }
+
+ tool.OpenNewPrice();
+ tool.FillTarget();
+ }
+
+ catch (Exception exception)
+ {
+ MessageBox.Show(exception.Message,
+ "Ошибка",
+ MessageBoxButtons.OK,
+ MessageBoxIcon.Information);
+ RhSolutionsAddIn.Excel.StatusBar = false;
+ return;
+ }
+ }
+
+ public bool GetConvertEnabled(IRibbonControl control)
+ {
+ if (RhSolutionsAddIn.Excel.ActiveWorkbook == null)
+ return false;
+
+ else
+ {
+ Worksheet worksheet = RhSolutionsAddIn.Excel.ActiveWorkbook.ActiveSheet;
+ return worksheet.IsRehauSource();
+ }
+ }
+
+ public bool GetExportEnabled(IRibbonControl control)
+ {
+ if (RhSolutionsAddIn.Excel.ActiveWorkbook == null)
+ return false;
+
+ else
+ {
+ Range selection = RhSolutionsAddIn.Excel.Selection;
+ return selection.Columns.Count == 2;
+ }
+ }
+
+ public string GetVersionLabel(IRibbonControl control)
+ {
+ string version = Assembly.GetExecutingAssembly().GetName().Version.ToString();
+ return $"v{version}";
+ }
+
+ public string GetPriceListPathLabel(IRibbonControl control)
+ {
+ string name = RegistryUtil.GetPriceListName();
+ return string.IsNullOrEmpty(name) ? "Нет файла шаблона!" : name;
+ }
+ }
+}
diff --git a/src/Controllers/ToolBase.cs b/src/Controllers/ToolBase.cs
new file mode 100644
index 0000000..87eb9d9
--- /dev/null
+++ b/src/Controllers/ToolBase.cs
@@ -0,0 +1,189 @@
+using Microsoft.Office.Interop.Excel;
+using RhSolutions.Models;
+using RhSolutions.Services;
+using System;
+using System.Collections.Generic;
+using System.Linq;
+
+namespace RhSolutions.Controllers
+{
+ internal abstract class ToolBase
+ {
+ protected Application ExcelApp = RhSolutionsAddIn.Excel;
+ protected TargetPriceList TargetFile { get; set; }
+ protected ResultBar ResultBar { get; set; }
+ protected ProgressBar ProgressBar { get; set; }
+
+ public abstract void FillTarget();
+
+ public void OpenNewPrice()
+ {
+ if (ExcelApp.Workbooks
+ .Cast<Workbook>()
+ .FirstOrDefault(w => w.FullName == RegistryUtil.PriceListPath) != null)
+ {
+ throw new ArgumentException("Шаблонный файл редактируется в другом месте");
+ }
+
+ Workbook wb = ExcelApp.Workbooks.Open(RegistryUtil.PriceListPath, null, true);
+
+ try
+ {
+ TargetFile = new TargetPriceList(wb);
+ }
+
+ catch (Exception exception)
+ {
+ if (wb != null)
+ {
+ wb.Close();
+ }
+
+ throw exception;
+ }
+ }
+
+ protected void FillPositionAmountToColumns(KeyValuePair<Product, double> positionAmount, params int[] columns)
+ {
+ Range worksheetCells = TargetFile.Sheet.Cells;
+ Range skuColumn = TargetFile.SkuCell.EntireColumn;
+ Range oldSkuColumn = TargetFile.OldSkuCell.EntireColumn;
+
+ int? row = GetPositionRow(skuColumn, positionAmount.Key.ProductSku, positionAmount.Key.ProductLine);
+
+ if (row != null)
+ {
+ foreach (int column in columns)
+ {
+ Range cell = worksheetCells[row, column];
+ cell.AddValue(positionAmount.Value);
+ }
+
+ ResultBar.IncrementSuccess();
+ return;
+ }
+
+ if (TargetFile.OldSkuCell != null)
+ {
+ row = GetPositionRow(oldSkuColumn, positionAmount.Key.ProductSku, positionAmount.Key.ProductLine);
+
+ if (row != null)
+ {
+ foreach (int column in columns)
+ {
+ Range cell = worksheetCells[row, column];
+ cell.AddValue(positionAmount.Value);
+ }
+
+ ResultBar.IncrementReplaced();
+ return;
+ }
+ }
+
+ string sku = positionAmount.Key.ProductSku.Substring(1, 6);
+ row = GetPositionRow(skuColumn, sku, positionAmount.Key.ProductLine);
+
+ if (row != null)
+ {
+ foreach (int column in columns)
+ {
+ Range cell = worksheetCells[row, column];
+ cell.AddValue(positionAmount.Value);
+ }
+
+ ResultBar.IncrementReplaced();
+ return;
+ }
+
+ FillMissing(positionAmount, columns);
+ ResultBar.IncrementNotFound();
+ }
+
+ protected void FillMissing(KeyValuePair<Product, double> positionAmount, params int[] columns)
+ {
+ Range worksheetCells = TargetFile.Sheet.Cells;
+ Range worksheetRows = TargetFile.Sheet.Rows;
+ int skuColumn = TargetFile.SkuCell.Column;
+ int groupColumn = TargetFile.GroupCell.Column;
+ int nameColumn = TargetFile.NameCell.Column;
+
+ int row = worksheetCells[worksheetRows.Count, skuColumn]
+ .End[XlDirection.xlUp]
+ .Row + 1;
+
+ worksheetRows[row]
+ .EntireRow
+ .Insert(XlInsertShiftDirection.xlShiftDown, XlInsertFormatOrigin.xlFormatFromLeftOrAbove);
+
+ Range previous = worksheetRows[row - 1];
+ Range current = worksheetRows[row];
+
+ previous.Copy(current);
+ current.ClearContents();
+
+ worksheetCells[row, groupColumn].Value2 = positionAmount.Key.ProductLine;
+ worksheetCells[row, nameColumn].Value2 = positionAmount.Key.Name;
+
+ if (TargetFile.OldSkuCell != null)
+ {
+ worksheetCells[row, skuColumn].Value2 = "Не найден";
+ worksheetCells[row, TargetFile.OldSkuCell.Column].Value2 = positionAmount.Key.ProductSku;
+ }
+
+ else
+ {
+ worksheetCells[row, skuColumn].Value2 = positionAmount.Key.ProductSku;
+ }
+
+ foreach (int column in columns)
+ {
+ Range cell = worksheetCells[row, column];
+ cell.AddValue(positionAmount.Value);
+ }
+ }
+
+ protected int? GetPositionRow(Range range, string sku, string group)
+ {
+ Range found = range.Find(sku);
+ string foundGroupValue;
+
+ if (found == null)
+ {
+ return null;
+ }
+
+ int firstFoundRow = found.Row;
+
+ if (string.IsNullOrEmpty(group))
+ {
+ return found.Row;
+ }
+
+ while (true)
+ {
+ foundGroupValue = TargetFile.Sheet.Cells[found.Row, TargetFile.GroupCell.Column].Value2.ToString();
+
+ if (group.Equals(foundGroupValue))
+ {
+ return found.Row;
+ }
+
+ found = range.FindNext(found);
+
+ if (found.Row == firstFoundRow)
+ {
+ return null;
+ }
+ }
+ }
+
+ protected 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
generated by cgit v1.2.3 (git 2.25.1) at 2025-04-19 21:24:32 +0000