aboutsummaryrefslogtreecommitdiff
path: root/src
diff options
context:
space:
mode:
Diffstat (limited to 'src')
-rw-r--r--src/AddIn/AddIn.cs3
-rw-r--r--src/AddIn/RegistryUtil.cs44
-rw-r--r--src/Forms/Dialog.cs18
-rw-r--r--src/Forms/SettingsForm.Designer.cs46
-rw-r--r--src/Forms/SettingsForm.cs21
-rw-r--r--src/Forms/SettingsForm.resx120
-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
-rw-r--r--src/RehauSku.Assist.csproj10
-rw-r--r--src/RehauSku.Assist.sln25
-rw-r--r--src/Ribbon/RibbonController.cs60
16 files changed, 401 insertions, 476 deletions
diff --git a/src/AddIn/AddIn.cs b/src/AddIn/AddIn.cs
index 67cdcc8..93d8aec 100644
--- a/src/AddIn/AddIn.cs
+++ b/src/AddIn/AddIn.cs
@@ -1,6 +1,7 @@
using ExcelDna.Integration;
using ExcelDna.IntelliSense;
using ExcelDna.Registration;
+using Microsoft.Office.Interop.Excel;
using System.Net.Http;
using System.Runtime.Caching;
@@ -20,6 +21,7 @@ namespace RehauSku
{
public static HttpClient httpClient;
public static MemoryCache memoryCache;
+ public static Application Excel;
public void AutoOpen()
{
@@ -28,6 +30,7 @@ namespace RehauSku
RegisterFunctions();
IntelliSenseServer.Install();
RegistryUtil.Initialize();
+ Excel = (Application)ExcelDnaUtil.Application;
}
public void AutoClose()
diff --git a/src/AddIn/RegistryUtil.cs b/src/AddIn/RegistryUtil.cs
index 40d0ec2..ceee2fe 100644
--- a/src/AddIn/RegistryUtil.cs
+++ b/src/AddIn/RegistryUtil.cs
@@ -2,56 +2,56 @@
using System.IO;
using RehauSku.Forms;
using System.Windows.Forms;
+using ExcelDna.Integration;
namespace RehauSku
{
static class RegistryUtil
{
- private static string _priceListPath;
- private static int? _storeResponseOrder;
- private static RegistryKey _RootKey { get; set; }
+ private static string priceListPath;
+ private static int? storeResponseOrder;
+ private static RegistryKey RootKey { get; set; }
public static void Initialize()
{
- _RootKey = Registry.CurrentUser.CreateSubKey(@"SOFTWARE\REHAU\SkuAssist");
- _priceListPath = _RootKey.GetValue("PriceListPath") as string;
- _storeResponseOrder = _RootKey.GetValue("StoreResponseOrder") as int?;
+ RootKey = Registry.CurrentUser.CreateSubKey(@"SOFTWARE\REHAU\SkuAssist");
+ priceListPath = RootKey.GetValue("PriceListPath") as string;
+ storeResponseOrder = RootKey.GetValue("StoreResponseOrder") as int?;
}
public static void Uninitialize()
{
- _RootKey.Close();
-
+ RootKey.Close();
}
public static bool IsPriceListPathEmpty()
{
- return string.IsNullOrEmpty(_priceListPath);
+ return string.IsNullOrEmpty(priceListPath);
}
public static string PriceListPath
{
get
{
- if (IsPriceListPathEmpty() || !File.Exists(_priceListPath))
+ if (IsPriceListPathEmpty() || !File.Exists(priceListPath))
{
- MessageBox.Show("Прайс-лист отсутствует или неверный файл прайс-листа", "Укажите файл прайс-листа", MessageBoxButtons.OK, MessageBoxIcon.Warning);
+ //MessageBox.Show("Прайс-лист отсутствует или неверный файл прайс-листа", "Укажите файл прайс-листа", MessageBoxButtons.OK, MessageBoxIcon.Warning);
string fileName = Dialog.GetFilePath();
- _priceListPath = fileName;
- _RootKey.SetValue("PriceListPath", fileName);
- return _priceListPath;
+ priceListPath = fileName;
+ RootKey.SetValue("PriceListPath", fileName);
+ return priceListPath;
}
else
{
- return _priceListPath;
+ return priceListPath;
}
}
set
{
- _priceListPath = value;
- _RootKey.SetValue("PriceListPath", value);
+ priceListPath = value;
+ RootKey.SetValue("PriceListPath", value);
}
}
@@ -59,16 +59,16 @@ namespace RehauSku
{
get
{
- if (_storeResponseOrder == null)
+ if (storeResponseOrder == null)
{
- _RootKey.SetValue("StoreResponseOrder", (int)ResponseOrder.Default);
- _storeResponseOrder = (int)ResponseOrder.Default;
- return (ResponseOrder)_storeResponseOrder.Value;
+ RootKey.SetValue("StoreResponseOrder", (int)ResponseOrder.Default);
+ storeResponseOrder = (int)ResponseOrder.Default;
+ return (ResponseOrder)storeResponseOrder.Value;
}
else
{
- return (ResponseOrder)_storeResponseOrder.Value;
+ return (ResponseOrder)storeResponseOrder.Value;
}
}
}
diff --git a/src/Forms/Dialog.cs b/src/Forms/Dialog.cs
index 170cc81..cc1c29a 100644
--- a/src/Forms/Dialog.cs
+++ b/src/Forms/Dialog.cs
@@ -1,4 +1,5 @@
-using System.Collections.Generic;
+using Microsoft.Office.Interop.Excel;
+using System.Collections.Generic;
using System.Windows.Forms;
namespace RehauSku.Forms
@@ -42,5 +43,20 @@ namespace RehauSku.Forms
return fileNames.ToArray();
}
+
+ public static void SaveWorkbookAs()
+ {
+ Workbook wb = AddIn.Excel.ActiveWorkbook;
+ string currentFilename = wb.FullName;
+ string fileFilter = "Файлы Excel (*.xls;*.xlsx;*.xlsm),*.xls;*.xlsx;*.xlsm";
+
+ object fileName = AddIn.Excel.GetSaveAsFilename(currentFilename, fileFilter);
+
+ if (fileName.GetType() == typeof(string))
+ wb.SaveAs(fileName);
+
+ else
+ wb.Close(false);
+ }
}
}
diff --git a/src/Forms/SettingsForm.Designer.cs b/src/Forms/SettingsForm.Designer.cs
deleted file mode 100644
index 669406e..0000000
--- a/src/Forms/SettingsForm.Designer.cs
+++ /dev/null
@@ -1,46 +0,0 @@
-namespace RehauSku.Forms
-{
- partial class SettingsForm
- {
- /// <summary>
- /// Required designer variable.
- /// </summary>
- private System.ComponentModel.IContainer components = null;
-
- /// <summary>
- /// Clean up any resources being used.
- /// </summary>
- /// <param name="disposing">true if managed resources should be disposed; otherwise, false.</param>
- protected override void Dispose(bool disposing)
- {
- if (disposing && (components != null))
- {
- components.Dispose();
- }
- base.Dispose(disposing);
- }
-
- #region Windows Form Designer generated code
-
- /// <summary>
- /// Required method for Designer support - do not modify
- /// the contents of this method with the code editor.
- /// </summary>
- private void InitializeComponent()
- {
- this.SuspendLayout();
- //
- // SettingsForm
- //
- this.AutoScaleDimensions = new System.Drawing.SizeF(8F, 16F);
- this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
- this.ClientSize = new System.Drawing.Size(800, 450);
- this.Name = "SettingsForm";
- this.Text = "SettingsForm";
- this.ResumeLayout(false);
-
- }
-
- #endregion
- }
-} \ No newline at end of file
diff --git a/src/Forms/SettingsForm.cs b/src/Forms/SettingsForm.cs
deleted file mode 100644
index 4dffadb..0000000
--- a/src/Forms/SettingsForm.cs
+++ /dev/null
@@ -1,21 +0,0 @@
-using System;
-using System.Collections.Generic;
-using System.ComponentModel;
-using System.Data;
-using System.Drawing;
-using System.Linq;
-using System.Text;
-using System.Threading.Tasks;
-using System.Windows.Forms;
-
-namespace RehauSku.Forms
-{
- public partial class SettingsForm : Form
- {
- public SettingsForm()
- {
- InitializeComponent();
- }
-
- }
-}
diff --git a/src/Forms/SettingsForm.resx b/src/Forms/SettingsForm.resx
deleted file mode 100644
index 1af7de1..0000000
--- a/src/Forms/SettingsForm.resx
+++ /dev/null
@@ -1,120 +0,0 @@
-<?xml version="1.0" encoding="utf-8"?>
-<root>
- <!--
- Microsoft ResX Schema
-
- Version 2.0
-
- The primary goals of this format is to allow a simple XML format
- that is mostly human readable. The generation and parsing of the
- various data types are done through the TypeConverter classes
- associated with the data types.
-
- Example:
-
- ... ado.net/XML headers & schema ...
- <resheader name="resmimetype">text/microsoft-resx</resheader>
- <resheader name="version">2.0</resheader>
- <resheader name="reader">System.Resources.ResXResourceReader, System.Windows.Forms, ...</resheader>
- <resheader name="writer">System.Resources.ResXResourceWriter, System.Windows.Forms, ...</resheader>
- <data name="Name1"><value>this is my long string</value><comment>this is a comment</comment></data>
- <data name="Color1" type="System.Drawing.Color, System.Drawing">Blue</data>
- <data name="Bitmap1" mimetype="application/x-microsoft.net.object.binary.base64">
- <value>[base64 mime encoded serialized .NET Framework object]</value>
- </data>
- <data name="Icon1" type="System.Drawing.Icon, System.Drawing" mimetype="application/x-microsoft.net.object.bytearray.base64">
- <value>[base64 mime encoded string representing a byte array form of the .NET Framework object]</value>
- <comment>This is a comment</comment>
- </data>
-
- There are any number of "resheader" rows that contain simple
- name/value pairs.
-
- Each data row contains a name, and value. The row also contains a
- type or mimetype. Type corresponds to a .NET class that support
- text/value conversion through the TypeConverter architecture.
- Classes that don't support this are serialized and stored with the
- mimetype set.
-
- The mimetype is used for serialized objects, and tells the
- ResXResourceReader how to depersist the object. This is currently not
- extensible. For a given mimetype the value must be set accordingly:
-
- Note - application/x-microsoft.net.object.binary.base64 is the format
- that the ResXResourceWriter will generate, however the reader can
- read any of the formats listed below.
-
- mimetype: application/x-microsoft.net.object.binary.base64
- value : The object must be serialized with
- : System.Runtime.Serialization.Formatters.Binary.BinaryFormatter
- : and then encoded with base64 encoding.
-
- mimetype: application/x-microsoft.net.object.soap.base64
- value : The object must be serialized with
- : System.Runtime.Serialization.Formatters.Soap.SoapFormatter
- : and then encoded with base64 encoding.
-
- mimetype: application/x-microsoft.net.object.bytearray.base64
- value : The object must be serialized into a byte array
- : using a System.ComponentModel.TypeConverter
- : and then encoded with base64 encoding.
- -->
- <xsd:schema id="root" xmlns="" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
- <xsd:import namespace="http://www.w3.org/XML/1998/namespace" />
- <xsd:element name="root" msdata:IsDataSet="true">
- <xsd:complexType>
- <xsd:choice maxOccurs="unbounded">
- <xsd:element name="metadata">
- <xsd:complexType>
- <xsd:sequence>
- <xsd:element name="value" type="xsd:string" minOccurs="0" />
- </xsd:sequence>
- <xsd:attribute name="name" use="required" type="xsd:string" />
- <xsd:attribute name="type" type="xsd:string" />
- <xsd:attribute name="mimetype" type="xsd:string" />
- <xsd:attribute ref="xml:space" />
- </xsd:complexType>
- </xsd:element>
- <xsd:element name="assembly">
- <xsd:complexType>
- <xsd:attribute name="alias" type="xsd:string" />
- <xsd:attribute name="name" type="xsd:string" />
- </xsd:complexType>
- </xsd:element>
- <xsd:element name="data">
- <xsd:complexType>
- <xsd:sequence>
- <xsd:element name="value" type="xsd:string" minOccurs="0" msdata:Ordinal="1" />
- <xsd:element name="comment" type="xsd:string" minOccurs="0" msdata:Ordinal="2" />
- </xsd:sequence>
- <xsd:attribute name="name" type="xsd:string" use="required" msdata:Ordinal="1" />
- <xsd:attribute name="type" type="xsd:string" msdata:Ordinal="3" />
- <xsd:attribute name="mimetype" type="xsd:string" msdata:Ordinal="4" />
- <xsd:attribute ref="xml:space" />
- </xsd:complexType>
- </xsd:element>
- <xsd:element name="resheader">
- <xsd:complexType>
- <xsd:sequence>
- <xsd:element name="value" type="xsd:string" minOccurs="0" msdata:Ordinal="1" />
- </xsd:sequence>
- <xsd:attribute name="name" type="xsd:string" use="required" />
- </xsd:complexType>
- </xsd:element>
- </xsd:choice>
- </xsd:complexType>
- </xsd:element>
- </xsd:schema>
- <resheader name="resmimetype">
- <value>text/microsoft-resx</value>
- </resheader>
- <resheader name="version">
- <value>2.0</value>
- </resheader>
- <resheader name="reader">
- <value>System.Resources.ResXResourceReader, System.Windows.Forms, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089</value>
- </resheader>
- <resheader name="writer">
- <value>System.Resources.ResXResourceWriter, System.Windows.Forms, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089</value>
- </resheader>
-</root> \ No newline at end of file
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);
- }
- }
- }
- }
-}
-
diff --git a/src/RehauSku.Assist.csproj b/src/RehauSku.Assist.csproj
index a977125..2fcae24 100644
--- a/src/RehauSku.Assist.csproj
+++ b/src/RehauSku.Assist.csproj
@@ -106,9 +106,11 @@
<Compile Include="Assistant\ParseUtil.cs" />
<Compile Include="Assistant\RequestModifier.cs" />
<Compile Include="Assistant\SkuExtensions.cs" />
+ <Compile Include="PriceListTools\CombineTool.cs" />
+ <Compile Include="PriceListTools\AbstractPriceListTool.cs" />
<Compile Include="PriceListTools\MergeTool.cs" />
<Compile Include="PriceListTools\PriceList.cs" />
- <Compile Include="PriceListTools\PriceListUtil.cs" />
+ <Compile Include="PriceListTools\PriceListSheet.cs" />
<Compile Include="Ribbon\RibbonController.cs" />
<Compile Include="Assistant\HttpClientUtil.cs" />
<Compile Include="Assistant\StoreResponse.cs" />
@@ -118,12 +120,6 @@
<Compile Include="AddIn\Functions.cs" />
<Compile Include="Properties\AssemblyInfo.cs" />
<Compile Include="Assistant\SkuAssist.cs" />
- <Compile Include="Forms\SettingsForm.cs">
- <SubType>Form</SubType>
- </Compile>
- <Compile Include="Forms\SettingsForm.Designer.cs">
- <DependentUpon>SettingsForm.cs</DependentUpon>
- </Compile>
</ItemGroup>
<ItemGroup>
<None Include="app.config" />
diff --git a/src/RehauSku.Assist.sln b/src/RehauSku.Assist.sln
deleted file mode 100644
index 1fe96c1..0000000
--- a/src/RehauSku.Assist.sln
+++ /dev/null
@@ -1,25 +0,0 @@
-
-Microsoft Visual Studio Solution File, Format Version 12.00
-# Visual Studio Version 17
-VisualStudioVersion = 17.0.32014.148
-MinimumVisualStudioVersion = 10.0.40219.1
-Project("{FAE04EC0-301F-11D3-BF4B-00C04F79EFBC}") = "RehauSku.Assist", "RehauSku.Assist.csproj", "{18A2FF67-0E46-4A86-B872-29F2B3F23ADF}"
-EndProject
-Global
- GlobalSection(SolutionConfigurationPlatforms) = preSolution
- Debug|Any CPU = Debug|Any CPU
- Release|Any CPU = Release|Any CPU
- EndGlobalSection
- GlobalSection(ProjectConfigurationPlatforms) = postSolution
- {18A2FF67-0E46-4A86-B872-29F2B3F23ADF}.Debug|Any CPU.ActiveCfg = Debug|Any CPU
- {18A2FF67-0E46-4A86-B872-29F2B3F23ADF}.Debug|Any CPU.Build.0 = Debug|Any CPU
- {18A2FF67-0E46-4A86-B872-29F2B3F23ADF}.Release|Any CPU.ActiveCfg = Release|Any CPU
- {18A2FF67-0E46-4A86-B872-29F2B3F23ADF}.Release|Any CPU.Build.0 = Release|Any CPU
- EndGlobalSection
- GlobalSection(SolutionProperties) = preSolution
- HideSolutionNode = FALSE
- EndGlobalSection
- GlobalSection(ExtensibilityGlobals) = postSolution
- SolutionGuid = {3CF61093-14A6-4A2D-AC13-53EBA2253BAA}
- EndGlobalSection
-EndGlobal
diff --git a/src/Ribbon/RibbonController.cs b/src/Ribbon/RibbonController.cs
index df6f327..ed59541 100644
--- a/src/Ribbon/RibbonController.cs
+++ b/src/Ribbon/RibbonController.cs
@@ -3,6 +3,7 @@ using System.Windows.Forms;
using ExcelDna.Integration.CustomUI;
using RehauSku.PriceListTools;
using RehauSku.Forms;
+using System;
namespace RehauSku.Ribbon
{
@@ -17,11 +18,14 @@ namespace RehauSku.Ribbon
<tabs>
<tab id='rau' label='REHAU'>
<group id='priceList' label='Прайс-лист'>
- <button id='exportToPrice' label='Экспорт в новый файл' size='normal' imageMso='PivotExportToExcel' onAction='OnExportPressed'/>
- <button id='mergeFiles' label='Объединить' size='normal' imageMso='Copy' onAction='OnMergePressed'/>
+ <button id='exportToPrice' label='Экспорт в новый файл' size='normal' imageMso='PivotExportToExcel' onAction='OnExportPressed'/>
+ <menu id='conjoinMenu' label='Объединить' imageMso='Copy'>
+ <button id='mergeFiles' label='Сложить' onAction='OnMergePressed'/>
+ <button id='combineFiles' label='По колонкам' onAction='OnCombinePressed'/>
+ </menu>
</group>
<group id='rausettings' label='Настройки'>
- <button id='setPriceList' label='Файл прайс-листа' size='normal' imageMso='CurrentViewSettings' onAction='OnSetPricePressed'/>
+ <button id='setPriceList' label='Файл прайс-листа' size='large' imageMso='CurrentViewSettings' onAction='OnSetPricePressed'/>
</group>
</tab>
</tabs>
@@ -29,35 +33,59 @@ namespace RehauSku.Ribbon
</customUI>";
}
+ // <dropDown id = 'dd1' label = 'Drop dynamic' getItemCount = 'fncGetItemCountDrop' getItemLabel = 'fncGetItemLabelDrop' onAction = 'fncOnActionDrop'/>
+
public void OnMergePressed(IRibbonControl control)
{
using (MergeTool mergeTool = new MergeTool())
{
string[] files = Dialog.GetMultiplyFiles();
- mergeTool.AddSkuAmountToDict(files);
- string exportFile = PriceListUtil.CreateNewExportFile();
- mergeTool.ExportToNewFile(exportFile);
+ if (files.Length != 0)
+ {
+ mergeTool.GetSource(files);
+ string exportFile = RegistryUtil.PriceListPath;
+ mergeTool.OpenNewPrice(exportFile);
+ mergeTool.FillPriceList();
+ }
}
}
- public void OnExportPressed(IRibbonControl control)
+ public void OnCombinePressed(IRibbonControl control)
{
- using (ExportTool exportTool = new ExportTool())
+ using (CombineTool combineTool = new CombineTool())
{
- if (!exportTool.IsRangeValid())
+ string[] files = Dialog.GetMultiplyFiles();
+ if (files.Length != 0)
{
- MessageBox.Show("Выделен неверный диапазон!",
- "Неверный диапазон",
- MessageBoxButtons.OK,
- MessageBoxIcon.Information);
- return;
+ combineTool.GetSource(files);
+ string exportFile = RegistryUtil.PriceListPath;
+ combineTool.OpenNewPrice(exportFile);
+ combineTool.FillPriceList();
}
+ }
+ }
- else
+ public void OnExportPressed(IRibbonControl control)
+ {
+ try
+ {
+ using (ExportTool exportTool = new ExportTool())
{
- exportTool.ExportToNewFile();
+ exportTool.GetSource();
+ string exportFile = RegistryUtil.PriceListPath;
+ exportTool.OpenNewPrice(exportFile);
+ exportTool.FillPriceList();
}
}
+ catch (Exception ex)
+ {
+ MessageBox.Show(ex.Message,
+ "Ошибка",
+ MessageBoxButtons.OK,
+ MessageBoxIcon.Information);
+ return;
+ }
+
}
public void OnSetPricePressed(IRibbonControl control)
generated by cgit v1.2.3 (git 2.25.1) at 2025-04-20 01:49:19 +0000