diff options
| -rw-r--r-- | RehauSku.Assist.sln (renamed from src/RehauSku.Assist.sln) | 2 | ||||
| -rw-r--r-- | src/Forms/SettingsForm.Designer.cs | 46 | ||||
| -rw-r--r-- | src/Forms/SettingsForm.cs | 21 | ||||
| -rw-r--r-- | src/Forms/SettingsForm.resx | 120 | ||||
| -rw-r--r-- | src/PriceListTools/AbstractPriceListTool.cs | 60 | ||||
| -rw-r--r-- | src/PriceListTools/CombineTool.cs | 123 | ||||
| -rw-r--r-- | src/PriceListTools/ConjoinTool.cs | 9 | ||||
| -rw-r--r-- | src/PriceListTools/ExportTool.cs | 78 | ||||
| -rw-r--r-- | src/PriceListTools/IConjoinTool.cs | 8 | ||||
| -rw-r--r-- | src/PriceListTools/MergeTool.cs | 108 | ||||
| -rw-r--r-- | src/PriceListTools/PriceList.cs | 137 | ||||
| -rw-r--r-- | src/PriceListTools/PriceListSheet.cs | 64 | ||||
| -rw-r--r-- | src/PriceListTools/PriceListUtil.cs | 41 | ||||
| -rw-r--r-- | src/RehauSku.Assist.csproj | 11 | ||||
| -rw-r--r-- | src/Ribbon/RibbonController.cs | 44 |
15 files changed, 303 insertions, 569 deletions
diff --git a/src/RehauSku.Assist.sln b/RehauSku.Assist.sln index 1fe96c1..440314a 100644 --- a/src/RehauSku.Assist.sln +++ b/RehauSku.Assist.sln @@ -3,7 +3,7 @@ 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}" +Project("{FAE04EC0-301F-11D3-BF4B-00C04F79EFBC}") = "RehauSku.Assist", "src\RehauSku.Assist.csproj", "{18A2FF67-0E46-4A86-B872-29F2B3F23ADF}" EndProject Global GlobalSection(SolutionConfigurationPlatforms) = preSolution 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..04d11af --- /dev/null +++ b/src/PriceListTools/AbstractPriceListTool.cs @@ -0,0 +1,60 @@ +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(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 index c536936..303146c 100644 --- a/src/PriceListTools/CombineTool.cs +++ b/src/PriceListTools/CombineTool.cs @@ -1,95 +1,74 @@ -using ExcelDna.Integration; -using Microsoft.Office.Interop.Excel; +using Microsoft.Office.Interop.Excel; using System; -using System.Collections.Generic; -using System.IO; -using System.Linq; namespace RehauSku.PriceListTools { - class CombineTool : ConjoinTool, IDisposable, IConjoinTool + internal class CombineTool : AbstractPriceListTool, IDisposable { - private Dictionary<string, double>[] SkuAmount { get; set; } - private string[] FileNames { get; set; } - - public CombineTool() - { - ExcelApp = (Application)ExcelDnaUtil.Application; - } - - public void CollectSkuAmount(string[] files) + public override void FillPriceList() { - FileNames = files.Select(x => Path.GetFileNameWithoutExtension(x)).ToArray(); - SkuAmount = new Dictionary<string, double>[files.Length]; + PriceListSheet offer = NewPriceList.OfferSheet; + offer.Sheet.Activate(); - ExcelApp.ScreenUpdating = false; + int exportedValues = 0; + int exportedLists = 0; - for (int i = 0; i < files.Length; i++) + foreach (var priceList in sourcePriceLists) { - Workbook wb = ExcelApp.Workbooks.Open(files[i]); - - try - { - PriceList priceList = new PriceList(wb); - SkuAmount[i] = new Dictionary<string, double>(); - SkuAmount[i].AddValuesFromPriceList(priceList); - } - - catch (Exception ex) - { - System.Windows.Forms.MessageBox.Show - ($"{wb.Name} не является файлом прайс-листа \n\n {ex.Message}", - "Неверный файл прайс-листа!", - System.Windows.Forms.MessageBoxButtons.OK, - System.Windows.Forms.MessageBoxIcon.Error); - } - - finally + foreach (var sheet in priceList.Sheets) { - wb.Close(); + 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}"; + } } } - ExcelApp.ScreenUpdating = true; - } - - public void ExportToFile(string exportFile) - { - if (SkuAmount.Sum(d => d.Count) < 1) - { - return; - } - - Workbook wb = ExcelApp.Workbooks.Open(exportFile); - PriceList priceList; - - try - { - priceList = new PriceList(wb); - priceList.FillWithValues(SkuAmount, FileNames); - } + AutoFilter filter = offer.Sheet.AutoFilter; + int firstFilterColumn = filter.Range.Column; - catch (Exception ex) - { - System.Windows.Forms.MessageBox.Show - ($"{RegistryUtil.PriceListPath} не является файлом прайс-листа \n\n {ex.Message}", - "Неверный файл прайс-листа!", - System.Windows.Forms.MessageBoxButtons.OK, - System.Windows.Forms.MessageBoxIcon.Error); + filter.Range.AutoFilter(offer.amountColumnNumber - firstFilterColumn + 1 + exportedLists, "<>"); + offer.Sheet.Range["A1"].Activate(); - wb.Close(); - } + offer.Sheet.Application.StatusBar = $"Экспортировано {exportedValues} строк из {sourcePriceLists.Count} файлов"; } public void Dispose() { - //Dispose(true); GC.SuppressFinalize(this); } - - //protected virtual void Dispose(bool disposing) - //{ - - //} } } diff --git a/src/PriceListTools/ConjoinTool.cs b/src/PriceListTools/ConjoinTool.cs deleted file mode 100644 index 19ad653..0000000 --- a/src/PriceListTools/ConjoinTool.cs +++ /dev/null @@ -1,9 +0,0 @@ -using Microsoft.Office.Interop.Excel; - -namespace RehauSku.PriceListTools -{ - internal class ConjoinTool - { - protected Application ExcelApp; - } -}
\ No newline at end of file diff --git a/src/PriceListTools/ExportTool.cs b/src/PriceListTools/ExportTool.cs index e549a00..fbafbdd 100644 --- a/src/PriceListTools/ExportTool.cs +++ b/src/PriceListTools/ExportTool.cs @@ -6,28 +6,25 @@ 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(string[] files) { - return Selection != null && - Selection.Columns.Count == 2; + if (Selection != null && Selection.Columns.Count == 2) + FillSkuAmountDict(); + else throw new Exception("Неверный диапазон"); } - private void _FillSkuAmountDict() + private void FillSkuAmountDict() { object[,] cells = Selection.Value2; SkuAmount = new Dictionary<string, double>(); @@ -72,44 +69,53 @@ 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.FillWithValues(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(); + offer.Sheet.Application.StatusBar = $"Экспортировано {exportedValues} строк из {SkuAmount.Count}"; } public void Dispose() { - Dispose(true); GC.SuppressFinalize(this); } - - protected virtual void Dispose(bool disposing) - { - - } } } diff --git a/src/PriceListTools/IConjoinTool.cs b/src/PriceListTools/IConjoinTool.cs deleted file mode 100644 index 3107a16..0000000 --- a/src/PriceListTools/IConjoinTool.cs +++ /dev/null @@ -1,8 +0,0 @@ -namespace RehauSku.PriceListTools -{ - internal interface IConjoinTool - { - void CollectSkuAmount(string[] files); - void ExportToFile(string exportFile); - } -}
\ No newline at end of file diff --git a/src/PriceListTools/MergeTool.cs b/src/PriceListTools/MergeTool.cs index 2dbe61d..4f5522c 100644 --- a/src/PriceListTools/MergeTool.cs +++ b/src/PriceListTools/MergeTool.cs @@ -1,87 +1,63 @@ -using ExcelDna.Integration; -using Microsoft.Office.Interop.Excel; +using Microsoft.Office.Interop.Excel; using System; -using System.Collections.Generic; namespace RehauSku.PriceListTools { - class MergeTool : ConjoinTool, IDisposable, IConjoinTool + internal class MergeTool : AbstractPriceListTool, IDisposable { - private Dictionary<string, double> SkuAmount { get; set; } - - public MergeTool() + public override void FillPriceList() { - ExcelApp = (Application)ExcelDnaUtil.Application; - SkuAmount = new Dictionary<string, double>(); - } + PriceListSheet offer = NewPriceList.OfferSheet; + offer.Sheet.Activate(); - public void CollectSkuAmount(string[] files) - { - ExcelApp.ScreenUpdating = false; - foreach (string file in files) - { - Workbook wb = ExcelApp.Workbooks.Open(file); - - try - { - PriceList priceList = new PriceList(wb); - SkuAmount.AddValuesFromPriceList(priceList); - } + int exportedValues = 0; - catch (Exception ex) - { - System.Windows.Forms.MessageBox.Show - ($"{wb.Name} не является файлом прайс-листа \n\n {ex.Message}", - "Неверный файл прайс-листа!", - System.Windows.Forms.MessageBoxButtons.OK, - System.Windows.Forms.MessageBoxIcon.Error); - } - - finally + foreach (var priceList in sourcePriceLists) + { + foreach (var sheet in priceList.Sheets) { - wb.Close(); + if (sheet.SkuAmount.Count == 0) + continue; + + 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 + { + Range sumCell = offer.Sheet.Cells[cell.Row, offer.amountColumnNumber]; + + if (sumCell.Value2 == null) + sumCell.Value2 = kvp.Value; + else + sumCell.Value2 += kvp.Value; + + exportedValues++; + } + } } } - ExcelApp.ScreenUpdating = true; - } - - public void ExportToFile(string exportFile) - { - if (SkuAmount.Count < 1) - { - return; - } - Workbook wb = ExcelApp.Workbooks.Open(exportFile); - PriceList priceList; + AutoFilter filter = offer.Sheet.AutoFilter; + int firstFilterColumn = filter.Range.Column; - try - { - priceList = new PriceList(wb); - priceList.FillWithValues(SkuAmount); - } - - catch (Exception ex) - { - System.Windows.Forms.MessageBox.Show - ($"{RegistryUtil.PriceListPath} не является файлом прайс-листа \n\n {ex.Message}", - "Неверный файл прайс-листа!", - System.Windows.Forms.MessageBoxButtons.OK, - System.Windows.Forms.MessageBoxIcon.Error); - - wb.Close(); - } + filter.Range.AutoFilter(offer.amountColumnNumber - firstFilterColumn + 1, "<>"); + offer.Sheet.Range["A1"].Activate(); + offer.Sheet.Application.StatusBar = $"Экспортировано {exportedValues} строк из {sourcePriceLists.Count} файлов"; } 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 c39b0ba..bc11a17 100644 --- a/src/PriceListTools/PriceList.cs +++ b/src/PriceListTools/PriceList.cs @@ -1,146 +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 = "КП"; 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 void FillWithValues(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); - } + PriceListSheet priceListSheet = new PriceListSheet(worksheet); - else - { - ws.Cells[cell.Row, amountColumn].Value = kvp.Value; - exportedValues++; - } + if (priceListSheet.FillSkuAmount()) + Sheets.Add(priceListSheet); } - AutoFilter filter = ws.AutoFilter; - int firstFilterColumn = filter.Range.Column; - - 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 void FillWithValues(Dictionary<string, double>[] values, string[] filenames) + public static string CreateNewFile() { - Worksheet ws = OfferSheet.sheet; - ws.Activate(); - - int amountColumn = OfferSheet.amountColumn.Value; - int skuColumn = OfferSheet.skuColumn.Value; - int headerColumn = OfferSheet.headerRow.Value; - - int exportedValues = 0; - - for (int i = 0; i < values.Length; i++) - { - ws.Columns[amountColumn] - .EntireColumn - .Insert(XlInsertShiftDirection.xlShiftToRight, XlInsertFormatOrigin.xlFormatFromRightOrBelow); - - foreach (var kvp in values[i]) - { - 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].Value2 = kvp.Value; - Range sumCell = ws.Cells[cell.Row, amountColumn + i + 1]; - - if (sumCell.Value2 == null) - sumCell.Value2 = kvp.Value; - else - sumCell.Value2 += kvp.Value; - - exportedValues++; - } - } - - ws.Cells[headerColumn, amountColumn].Value2 = filenames[i]; - } - - AutoFilter filter = ws.AutoFilter; - int firstFilterColumn = filter.Range.Column; - - filter.Range.AutoFilter(amountColumn - firstFilterColumn + 1 + values.Length, "<>"); - ws.Range["A1"].Activate(); - ws.Application.StatusBar = $"Экспортировано {exportedValues} строк из {values.Sum(x => x.Count)}"; - } - - public class PriceListSheet - { - public readonly Worksheet sheet; - public readonly int? headerRow; - - public readonly int? skuColumn; - public readonly int? amountColumn; - - public object[,] skuCells; - public object[,] amountCells; - - 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; - } + 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..e10913b --- /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) + { + Sheet.Application.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 3a04cc9..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 AddValuesFromPriceList(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 1f85926..2fcae24 100644 --- a/src/RehauSku.Assist.csproj +++ b/src/RehauSku.Assist.csproj @@ -107,11 +107,10 @@ <Compile Include="Assistant\RequestModifier.cs" /> <Compile Include="Assistant\SkuExtensions.cs" /> <Compile Include="PriceListTools\CombineTool.cs" /> - <Compile Include="PriceListTools\ConjoinTool.cs" /> - <Compile Include="PriceListTools\IConjoinTool.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" /> @@ -121,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/Ribbon/RibbonController.cs b/src/Ribbon/RibbonController.cs index ad27bc2..d0eb590 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 { @@ -24,7 +25,7 @@ namespace RehauSku.Ribbon </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> @@ -39,9 +40,10 @@ namespace RehauSku.Ribbon using (MergeTool mergeTool = new MergeTool()) { string[] files = Dialog.GetMultiplyFiles(); - mergeTool.CollectSkuAmount(files); - string exportFile = PriceListUtil.CreateNewExportFile(); - mergeTool.ExportToFile(exportFile); + mergeTool.GetSource(files); + string exportFile = PriceList.CreateNewFile(); + mergeTool.OpenNewPrice(exportFile); + mergeTool.FillPriceList(); } } @@ -50,30 +52,34 @@ namespace RehauSku.Ribbon using (CombineTool combineTool = new CombineTool()) { string[] files = Dialog.GetMultiplyFiles(); - combineTool.CollectSkuAmount(files); - string exportFile = PriceListUtil.CreateNewExportFile(); - combineTool.ExportToFile(exportFile); + combineTool.GetSource(files); + string exportFile = PriceList.CreateNewFile(); + combineTool.OpenNewPrice(exportFile); + combineTool.FillPriceList(); } } public void OnExportPressed(IRibbonControl control) { - using (ExportTool exportTool = new ExportTool()) + try { - if (!exportTool.IsRangeValid()) + using (ExportTool exportTool = new ExportTool()) { - MessageBox.Show("Выделен неверный диапазон!", - "Неверный диапазон", - MessageBoxButtons.OK, - MessageBoxIcon.Information); - return; - } - - else - { - exportTool.ExportToNewFile(); + exportTool.GetSource(null); + string exportFile = PriceList.CreateNewFile(); + exportTool.OpenNewPrice(exportFile); + exportTool.FillPriceList(); } } + catch (Exception ex) + { + MessageBox.Show(ex.Message, + "Ошибка", + MessageBoxButtons.OK, + MessageBoxIcon.Information); + return; + } + } public void OnSetPricePressed(IRibbonControl control) |