diff options
23 files changed, 405 insertions, 131 deletions
@@ -11,12 +11,12 @@ - Отображение артикула с помощью `=RAUSKU()` - Отображение цены с помощью формулы `=RAUPRICE()` - Экспорт массива ячеек вида "Артикул - Количество" в прайс-лист -- Актуализация прайс-листа +- Актуализация прайс-листа до последней версии - Объединение нескольких прайс-листов в один файл - Сложением всех позиций по артикулам - С разнесением данных по колонкам в конечном файле -*Для работы функций "Экспорт" и "Объединение" требуется указать путь к файлу пустого прайс-листа REHAU* +*Для работы функций "Экспорт", "Актуализация" и "Объединение" требуется указать путь к файлу пустого прайс-листа REHAU* ## Работа без установки 1. Запустить файл `RehauSku.Assist-AddIn-packed.xll` или `RehauSku.Assist-AddIn64-packed.xll` в зависимости от архитектуры приложения diff --git a/src/AddIn/AddIn.cs b/src/AddIn/AddIn.cs index 93d8aec..b532bfb 100644 --- a/src/AddIn/AddIn.cs +++ b/src/AddIn/AddIn.cs @@ -5,10 +5,9 @@ using Microsoft.Office.Interop.Excel; using System.Net.Http; using System.Runtime.Caching; - namespace RehauSku { - public enum ResponseOrder + enum ResponseOrder { Default, Relevance, @@ -17,7 +16,7 @@ namespace RehauSku Series } - public class AddIn : IExcelAddIn + class AddIn : IExcelAddIn { public static HttpClient httpClient; public static MemoryCache memoryCache; @@ -27,16 +26,18 @@ namespace RehauSku { httpClient = new HttpClient(); memoryCache = new MemoryCache("RehauSku"); + Excel = (Application)ExcelDnaUtil.Application; RegisterFunctions(); IntelliSenseServer.Install(); RegistryUtil.Initialize(); - Excel = (Application)ExcelDnaUtil.Application; + EventsUtil.Initialize(); } public void AutoClose() { IntelliSenseServer.Uninstall(); RegistryUtil.Uninitialize(); + EventsUtil.Uninitialize(); memoryCache.Dispose(); } diff --git a/src/AddIn/EventsUtil.cs b/src/AddIn/EventsUtil.cs new file mode 100644 index 0000000..102e12e --- /dev/null +++ b/src/AddIn/EventsUtil.cs @@ -0,0 +1,33 @@ +using Microsoft.Office.Interop.Excel; + +namespace RehauSku +{ + internal static class EventsUtil + { + private static Application Excel = AddIn.Excel; + + public static void Initialize() + { + Excel.SheetSelectionChange += RefreshExportButton; + Excel.SheetActivate += RefreshConvertButton; + Excel.WorkbookActivate += RefreshConvertButton; + } + + public static void Uninitialize() + { + Excel.SheetSelectionChange -= RefreshExportButton; + Excel.SheetActivate -= RefreshConvertButton; + Excel.WorkbookActivate -= RefreshConvertButton; + } + + private static void RefreshConvertButton(object sh) + { + Interface.RibbonController.RefreshControl("convertPrice"); + } + + private static void RefreshExportButton(object sh, Range target) + { + Interface.RibbonController.RefreshControl("exportToPrice"); + } + } +} diff --git a/src/AddIn/RegistryUtil.cs b/src/AddIn/RegistryUtil.cs index 3ec6f6a..5fe2eea 100644 --- a/src/AddIn/RegistryUtil.cs +++ b/src/AddIn/RegistryUtil.cs @@ -1,5 +1,5 @@ using Microsoft.Win32; -using RehauSku.Forms; +using RehauSku.Interface; using System; using System.IO; using System.Windows.Forms; @@ -38,6 +38,12 @@ namespace RehauSku if (result == DialogResult.OK) { string fileName = Dialog.GetFilePath(); + + if (string.IsNullOrEmpty(fileName)) + { + throw new Exception("Нет файла шаблона"); + } + priceListPath = fileName; RootKey.SetValue("PriceListPath", fileName); return priceListPath; diff --git a/src/Assistant/SkuExtensions.cs b/src/AddIn/SkuExtensions.cs index e39807b..c7fe2bc 100644 --- a/src/Assistant/SkuExtensions.cs +++ b/src/AddIn/SkuExtensions.cs @@ -1,6 +1,6 @@ using System.Text.RegularExpressions; -namespace RehauSku.Assistant +namespace RehauSku { static class SkuExtensions { diff --git a/src/AddIn/WorksheetExtensions.cs b/src/AddIn/WorksheetExtensions.cs new file mode 100644 index 0000000..51ce13a --- /dev/null +++ b/src/AddIn/WorksheetExtensions.cs @@ -0,0 +1,32 @@ +using Microsoft.Office.Interop.Excel; +using System.Linq; + +namespace RehauSku +{ + public static class WorksheetExtensions + { + private static string amountHeader = "Кол-во"; + private static string skuHeader = "Актуальный материал"; + private static string groupHeader = "Программа"; + private static string nameHeader = "Наименование"; + + public static bool IsRehauSource(this Worksheet worksheet) + { + Range amountCell; + Range skuCell; + Range groupCell; + Range nameCell; + + Range[] cells = new[] + { + amountCell = worksheet.Cells.Find(amountHeader), + skuCell = worksheet.Cells.Find(skuHeader), + groupCell = worksheet.Cells.Find(groupHeader), + nameCell = worksheet.Cells.Find(nameHeader) + }; + + return cells.All(x => x != null); + } + } +} + diff --git a/src/Assistant/RequestModifier.cs b/src/Assistant/RequestModifier.cs index 9f42e71..c2c3436 100644 --- a/src/Assistant/RequestModifier.cs +++ b/src/Assistant/RequestModifier.cs @@ -5,7 +5,7 @@ using System.Text.RegularExpressions; namespace RehauSku.Assistant { - public static class RequestModifier + static class RequestModifier { public static string CleanRequest(this string input) { diff --git a/src/Assistant/SkuAssist.cs b/src/Assistant/SkuAssist.cs index 6c68288..85a084c 100644 --- a/src/Assistant/SkuAssist.cs +++ b/src/Assistant/SkuAssist.cs @@ -2,7 +2,7 @@ namespace RehauSku.Assistant { - public enum ProductField + enum ProductField { Name, Id, diff --git a/src/Assistant/StoreResponse.cs b/src/Assistant/StoreResponse.cs index 8e1759d..1a9b1c5 100644 --- a/src/Assistant/StoreResponse.cs +++ b/src/Assistant/StoreResponse.cs @@ -2,17 +2,17 @@ namespace RehauSku.Assistant { - public class StoreResponce + class StoreResponce { public Ecommerce Ecommerce { get; set; } } - public class Ecommerce + class Ecommerce { public List<Product> Impressions { get; set; } } - public class Product : IProduct + class Product : IProduct { public string Id { get; set; } public string Name { get; set; } diff --git a/src/Interface/AbstractBar.cs b/src/Interface/AbstractBar.cs new file mode 100644 index 0000000..c5918a8 --- /dev/null +++ b/src/Interface/AbstractBar.cs @@ -0,0 +1,11 @@ +using Microsoft.Office.Interop.Excel; + +namespace RehauSku.Interface +{ + internal abstract class AbstractBar + { + protected Application Excel = AddIn.Excel; + + public abstract void Update(); + } +} diff --git a/src/Forms/Dialog.cs b/src/Interface/Dialog.cs index cc1c29a..23f65d7 100644 --- a/src/Forms/Dialog.cs +++ b/src/Interface/Dialog.cs @@ -2,31 +2,27 @@ using System.Collections.Generic; using System.Windows.Forms; -namespace RehauSku.Forms +namespace RehauSku.Interface { static class Dialog { public static string GetFilePath() { - string filePath = string.Empty; - using (OpenFileDialog dialog = new OpenFileDialog()) { dialog.Filter = "Файлы Excel (*.xls;*.xlsx;*.xlsm)|*.xls;*.xlsx;*.xlsm"; if (dialog.ShowDialog() == DialogResult.OK) { - filePath = dialog.FileName; + return dialog.FileName; } - } - return filePath; + else return string.Empty; + } } public static string[] GetMultiplyFiles() { - List<string> fileNames = new List<string>(); - using (OpenFileDialog dialog = new OpenFileDialog()) { dialog.Filter = "Файлы Excel (*.xls;*.xlsx;*.xlsm)|*.xls;*.xlsx;*.xlsm"; @@ -34,29 +30,33 @@ namespace RehauSku.Forms if (dialog.ShowDialog() == DialogResult.OK) { - foreach (string file in dialog.FileNames) - { - fileNames.Add(file); - } + return dialog.FileNames; } - } - return fileNames.ToArray(); + else return null; + } } public static void SaveWorkbookAs() { - Workbook wb = AddIn.Excel.ActiveWorkbook; - string currentFilename = wb.FullName; - string fileFilter = "Файлы Excel (*.xls;*.xlsx;*.xlsm),*.xls;*.xlsx;*.xlsm"; + Workbook workbook = AddIn.Excel.ActiveWorkbook; - object fileName = AddIn.Excel.GetSaveAsFilename(currentFilename, fileFilter); + using (SaveFileDialog dialog = new SaveFileDialog()) + { + dialog.FileName = workbook.Name; + dialog.Filter = "Файлы Excel (*.xls;*.xlsx;*.xlsm)|*.xls;*.xlsx;*.xlsm"; - if (fileName.GetType() == typeof(string)) - wb.SaveAs(fileName); + if (dialog.ShowDialog() == DialogResult.Cancel) + { + workbook.Close(false); + } - else - wb.Close(false); + else + { + string fileName = dialog.FileName; + workbook.SaveAs(fileName); + } + } } } } diff --git a/src/Interface/ProgressBar.cs b/src/Interface/ProgressBar.cs new file mode 100644 index 0000000..2e68e8b --- /dev/null +++ b/src/Interface/ProgressBar.cs @@ -0,0 +1,31 @@ +namespace RehauSku.Interface +{ + internal class ProgressBar : AbstractBar + { + private double CurrentProgress { get; set; } + private readonly double TaskWeight; + private readonly string Message; + + public ProgressBar(string message, int weight) + { + Message = message; + TaskWeight = weight; + CurrentProgress = 0; + } + + public override void Update() + { + double percent = (++CurrentProgress / TaskWeight) * 100; + + if (percent < 100) + { + Excel.StatusBar = $"{Message} Выполнено {percent.ToString("#.#")} %"; + } + + else + { + Excel.StatusBar = false; + } + } + } +} diff --git a/src/Interface/ResultBar.cs b/src/Interface/ResultBar.cs new file mode 100644 index 0000000..1b4d7f4 --- /dev/null +++ b/src/Interface/ResultBar.cs @@ -0,0 +1,44 @@ +using System.Text; + +namespace RehauSku.Interface +{ + internal class ResultBar : AbstractBar + { + private int Success { get; set; } + private int Replaced { get; set; } + private int NotFound { get; set; } + + public ResultBar() + { + Success = 0; + Replaced = 0; + NotFound = 0; + } + + public void IncrementSuccess() => Success++; + public void IncrementReplaced() => Replaced++; + public void IncrementNotFound() => NotFound++; + + public override void Update() + { + StringBuilder sb = new StringBuilder(); + + if (Success > 0) + { + sb.Append($"Успешно экспортировано {Success} артикулов. "); + } + + if (Replaced > 0) + { + sb.Append($"Заменено {Replaced} артикулов. "); + } + + if (NotFound > 0) + { + sb.Append($"Не найдено {NotFound} артикулов."); + } + + Excel.StatusBar = sb.ToString(); + } + } +} diff --git a/src/Ribbon/RibbonController.cs b/src/Interface/RibbonController.cs index 7a514bd..bc038d1 100644 --- a/src/Ribbon/RibbonController.cs +++ b/src/Interface/RibbonController.cs @@ -1,25 +1,27 @@ using ExcelDna.Integration.CustomUI; -using RehauSku.Forms; +using Microsoft.Office.Interop.Excel; using RehauSku.PriceListTools; using System; using System.Runtime.InteropServices; using System.Windows.Forms; -namespace RehauSku.Ribbon +namespace RehauSku.Interface { [ComVisible(true)] public class RibbonController : ExcelRibbon { + private static IRibbonUI ribbonUi; + public override string GetCustomUI(string RibbonID) { return @" - <customUI xmlns='http://schemas.microsoft.com/office/2006/01/customui'> + <customUI onLoad='RibbonLoad' xmlns='http://schemas.microsoft.com/office/2006/01/customui'> <ribbon> <tabs> <tab id='rau' label='REHAU'> <group id='priceList' label='Прайс-лист'> - <button id='exportToPrice' label='Экспорт в новый файл' size='normal' imageMso='PivotExportToExcel' onAction='OnExportPressed'/> - <button id='convertPrice' label='Актуализировать' size='normal' imageMso='FileUpdate' onAction='OnConvertPressed'/> + <button id='exportToPrice' getEnabled='GetExportEnabled' label='Экспорт в новый файл' size='normal' imageMso='PivotExportToExcel' onAction='OnExportPressed'/> + <button id='convertPrice' getEnabled='GetConvertEnabled' label='Актуализировать' size='normal' imageMso='FileUpdate' onAction='OnConvertPressed'/> <menu id='conjoinMenu' label='Объединить' imageMso='Copy'> <button id='mergeFiles' label='Сложить' onAction='OnMergePressed'/> <button id='combineFiles' label='По колонкам' onAction='OnCombinePressed'/> @@ -34,14 +36,27 @@ namespace RehauSku.Ribbon </customUI>"; } + public void RibbonLoad(IRibbonUI sender) + { + ribbonUi = sender; + } + + public static void RefreshControl(string id) + { + if (ribbonUi != null) + { + ribbonUi.InvalidateControl(id); + } + } + public void OnMergePressed(IRibbonControl control) { MergeTool mergeTool = new MergeTool(); string[] files = Dialog.GetMultiplyFiles(); - if (files.Length != 0) + if (files != null) { - mergeTool.SourceFiles = Source.GetSourceLists(files); + mergeTool.SourceFiles = SourcePriceList.GetSourceLists(files); mergeTool.OpenNewPrice(); mergeTool.FillTarget(); } @@ -52,20 +67,31 @@ namespace RehauSku.Ribbon CombineTool combineTool = new CombineTool(); string[] files = Dialog.GetMultiplyFiles(); - if (files.Length != 0) + if (files != null) { - combineTool.SourceFiles = Source.GetSourceLists(files); + combineTool.SourceFiles = SourcePriceList.GetSourceLists(files); combineTool.OpenNewPrice(); combineTool.FillTarget(); } } + public bool GetConvertEnabled(IRibbonControl control) + { + if (AddIn.Excel.ActiveWorkbook == null) + return false; + + else + { + Worksheet worksheet = AddIn.Excel.ActiveWorkbook.ActiveSheet; + return worksheet.IsRehauSource(); + } + } + public void OnExportPressed(IRibbonControl control) { try { ExportTool exportTool = new ExportTool(); - exportTool.TryGetSelection(); exportTool.OpenNewPrice(); exportTool.FillTarget(); } @@ -80,6 +106,18 @@ namespace RehauSku.Ribbon } } + public bool GetExportEnabled(IRibbonControl control) + { + if (AddIn.Excel.ActiveWorkbook == null) + return false; + + else + { + Range selection = AddIn.Excel.Selection; + return selection.Columns.Count == 2; + } + } + public void OnConvertPressed(IRibbonControl control) { ConvertTool convertTool = new ConvertTool(); @@ -92,7 +130,11 @@ namespace RehauSku.Ribbon public void OnSetPricePressed(IRibbonControl control) { string path = Dialog.GetFilePath(); - RegistryUtil.PriceListPath = path; + + if (!string.IsNullOrEmpty(path)) + { + RegistryUtil.PriceListPath = path; + } } } } diff --git a/src/PriceListTools/PriceList.cs b/src/PriceListTools/AbstractPriceList.cs index 65ff3df..06427a0 100644 --- a/src/PriceListTools/PriceList.cs +++ b/src/PriceListTools/AbstractPriceList.cs @@ -2,7 +2,7 @@ namespace RehauSku.PriceListTools { - internal class PriceList + internal abstract class AbstractPriceList { protected const string amountHeader = "Кол-во"; protected const string skuHeader = "Актуальный материал"; diff --git a/src/PriceListTools/PriceListTool.cs b/src/PriceListTools/AbstractTool.cs index 0a82a41..b4e34cc 100644 --- a/src/PriceListTools/PriceListTool.cs +++ b/src/PriceListTools/AbstractTool.cs @@ -1,16 +1,20 @@ using ExcelDna.Integration; using Microsoft.Office.Interop.Excel; +using RehauSku.Interface; using System; using System.Collections.Generic; using System.Windows.Forms; using Application = Microsoft.Office.Interop.Excel.Application; +using ProgressBar = RehauSku.Interface.ProgressBar; namespace RehauSku.PriceListTools { - internal abstract class PriceListTool + internal abstract class AbstractTool { protected private Application ExcelApp = (Application)ExcelDnaUtil.Application; - protected private Target TargetFile; + protected private TargetPriceList TargetFile; + protected private ResultBar ResultBar { get; set; } + protected private ProgressBar ProgressBar { get; set; } public void OpenNewPrice() { @@ -18,7 +22,7 @@ namespace RehauSku.PriceListTools try { - TargetFile = new Target(wb); + TargetFile = new TargetPriceList(wb); } catch (Exception ex) @@ -33,7 +37,7 @@ namespace RehauSku.PriceListTools } } - protected private void FillColumnsWithDictionary(KeyValuePair<Position, double> positionAmount, params int[] columns) + protected private void FillPositionAmountToColumns(KeyValuePair<Position, double> positionAmount, params int[] columns) { int? row = GetPositionRow(positionAmount.Key.Sku, positionAmount.Key.Group, TargetFile.skuCell.Column); @@ -53,72 +57,96 @@ namespace RehauSku.PriceListTools sumCell.Value2 += positionAmount.Value; } } + + ResultBar.IncrementSuccess(); + return; } - else + if (TargetFile.oldSkuCell != null) { - string sku = positionAmount.Key.Sku.Substring(1, 6); + Range foundCell = TargetFile.oldSkuCell.EntireColumn.Find(positionAmount.Key.Sku); - row = GetPositionRow(sku, positionAmount.Key.Group, TargetFile.skuCell.Column); - - if (row != null) + if (foundCell != null) { + row = foundCell.Row; + foreach (int column in columns) { - Range amountCell = TargetFile.Sheet.Cells[row, column]; - - if (amountCell.Value2 == null) + if (TargetFile.Sheet.Cells[row, column].Value2 == null) { - amountCell.Value2 = positionAmount.Value; + TargetFile.Sheet.Cells[row, column].Value2 = positionAmount.Value; } else { - amountCell.Value2 += positionAmount.Value; + TargetFile.Sheet.Cells[row, column].Value2 += positionAmount.Value; } - - Range oldSkuCell = TargetFile.Sheet.Cells[row, TargetFile.oldSkuCell.Column]; - oldSkuCell.Value2 = positionAmount.Key.Sku; } + + ResultBar.IncrementReplaced(); + return; } + } - else + string sku = positionAmount.Key.Sku.Substring(1, 6); + row = GetPositionRow(sku, positionAmount.Key.Group, TargetFile.skuCell.Column); + + if (row != null) + { + foreach (int column in columns) { - FillMissing(positionAmount, columns); + Range amountCell = TargetFile.Sheet.Cells[row, column]; + + if (amountCell.Value2 == null) + { + amountCell.Value2 = positionAmount.Value; + } + + else + { + amountCell.Value2 += positionAmount.Value; + } } + + ResultBar.IncrementReplaced(); + return; + } + + else + { + FillMissing(positionAmount, columns); + ResultBar.IncrementNotFound(); } } protected private void FillMissing(KeyValuePair<Position, double> positionAmount, params int[] columns) { - Range foundCell = TargetFile.oldSkuCell.EntireColumn.Find(positionAmount.Key.Sku); - int row; + int row = TargetFile.Sheet.Cells[TargetFile.Sheet.Rows.Count, TargetFile.skuCell.Column] + .End[XlDirection.xlUp] + .Row + 1; - if (foundCell == null) - { - row = TargetFile.Sheet.Cells[TargetFile.Sheet.Rows.Count, TargetFile.skuCell.Column] - .End[XlDirection.xlUp] - .Row + 1; + TargetFile.Sheet.Rows[row] + .EntireRow + .Insert(XlInsertShiftDirection.xlShiftDown, XlInsertFormatOrigin.xlFormatFromLeftOrAbove); - TargetFile.Sheet.Rows[row] - .EntireRow - .Insert(XlInsertShiftDirection.xlShiftDown, XlInsertFormatOrigin.xlFormatFromLeftOrAbove); + Range previous = TargetFile.Sheet.Rows[row - 1]; + Range current = TargetFile.Sheet.Rows[row]; - Range previous = TargetFile.Sheet.Rows[row - 1]; - Range current = TargetFile.Sheet.Rows[row]; + previous.Copy(current); + current.ClearContents(); - previous.Copy(current); - current.ClearContents(); + TargetFile.Sheet.Cells[row, TargetFile.groupCell.Column].Value2 = positionAmount.Key.Group; + TargetFile.Sheet.Cells[row, TargetFile.nameCell.Column].Value2 = positionAmount.Key.Name; - TargetFile.Sheet.Cells[row, TargetFile.groupCell.Column].Value2 = positionAmount.Key.Group; - TargetFile.Sheet.Cells[row, TargetFile.oldSkuCell.Column].Value2 = positionAmount.Key.Sku; - TargetFile.Sheet.Cells[row, TargetFile.nameCell.Column].Value2 = positionAmount.Key.Name; + if (TargetFile.oldSkuCell != null) + { TargetFile.Sheet.Cells[row, TargetFile.skuCell.Column].Value2 = "Не найден"; + TargetFile.Sheet.Cells[row, TargetFile.oldSkuCell.Column].Value2 = positionAmount.Key.Sku; } else { - row = foundCell.Row; + TargetFile.Sheet.Cells[row, TargetFile.skuCell.Column].Value2 = positionAmount.Key.Sku; } foreach (int column in columns) diff --git a/src/PriceListTools/CombineTool.cs b/src/PriceListTools/CombineTool.cs index da29e4d..af9378c 100644 --- a/src/PriceListTools/CombineTool.cs +++ b/src/PriceListTools/CombineTool.cs @@ -1,15 +1,20 @@ using Microsoft.Office.Interop.Excel; using System.Collections.Generic; +using RehauSku.Interface; +using System.Linq; namespace RehauSku.PriceListTools { - internal class CombineTool : PriceListTool + internal class CombineTool : AbstractTool { - public List<Source> SourceFiles; + public List<SourcePriceList> SourceFiles; public void FillTarget() { - foreach (Source source in SourceFiles) + ProgressBar = new ProgressBar("Заполняю строки...", SourceFiles.Sum(file => file.PositionAmount.Count)); + ResultBar = new ResultBar(); + + foreach (SourcePriceList source in SourceFiles) { TargetFile.Sheet.Columns[TargetFile.amountCell.Column] .EntireColumn @@ -19,13 +24,18 @@ namespace RehauSku.PriceListTools newColumnHeader.Value2 = $"{source.Name}"; newColumnHeader.WrapText = true; - foreach(var kvp in source.PositionAmount) - FillColumnsWithDictionary(kvp, TargetFile.amountCell.Column - 1, TargetFile.amountCell.Column); + foreach (var kvp in source.PositionAmount) + { + FillPositionAmountToColumns(kvp, TargetFile.amountCell.Column - 1, TargetFile.amountCell.Column); + ProgressBar.Update(); + } } FilterByAmount(); + ResultBar.Update(); - Forms.Dialog.SaveWorkbookAs(); + Interface.Dialog.SaveWorkbookAs(); + ExcelApp.StatusBar = false; } } } diff --git a/src/PriceListTools/ConvertTool.cs b/src/PriceListTools/ConvertTool.cs index f37bfe2..d13c803 100644 --- a/src/PriceListTools/ConvertTool.cs +++ b/src/PriceListTools/ConvertTool.cs @@ -1,16 +1,17 @@ -using System; +using RehauSku.Interface; +using System; namespace RehauSku.PriceListTools { - internal class ConvertTool : PriceListTool + internal class ConvertTool : AbstractTool { - private Source Current; + private SourcePriceList Current; public void GetCurrent() { try { - Current = new Source(ExcelApp.ActiveWorkbook); + Current = new SourcePriceList(ExcelApp.ActiveWorkbook); } catch (Exception exception) @@ -26,12 +27,20 @@ namespace RehauSku.PriceListTools public void FillTarget() { + ProgressBar = new ProgressBar("Заполняю строки...", Current.PositionAmount.Count); + ResultBar = new ResultBar(); + foreach (var kvp in Current.PositionAmount) - FillColumnsWithDictionary(kvp, TargetFile.amountCell.Column); + { + FillPositionAmountToColumns(kvp, TargetFile.amountCell.Column); + ProgressBar.Update(); + } FilterByAmount(); + ResultBar.Update(); - Forms.Dialog.SaveWorkbookAs(); + Dialog.SaveWorkbookAs(); + ExcelApp.StatusBar = false; } } }
\ No newline at end of file diff --git a/src/PriceListTools/ExportTool.cs b/src/PriceListTools/ExportTool.cs index bfb3d8a..f341671 100644 --- a/src/PriceListTools/ExportTool.cs +++ b/src/PriceListTools/ExportTool.cs @@ -1,37 +1,37 @@ using Microsoft.Office.Interop.Excel; -using RehauSku.Assistant; using System; using System.Collections.Generic; +using RehauSku.Interface; namespace RehauSku.PriceListTools { - internal class ExportTool : PriceListTool + internal class ExportTool : AbstractTool { private Dictionary<Position, double> PositionAmount; private Range Selection; - public void TryGetSelection() + public ExportTool() { Selection = ExcelApp.Selection; - - if (Selection == null || Selection.Columns.Count != 2) - { - throw new Exception("Неверный диапазон"); - } } public void FillTarget() { GetSelected(); - + ProgressBar = new ProgressBar("Заполняю строки...", PositionAmount.Count); + ResultBar = new ResultBar(); + foreach (var kvp in PositionAmount) { - FillColumnsWithDictionary(kvp, TargetFile.amountCell.Column); + FillPositionAmountToColumns(kvp, TargetFile.amountCell.Column); + ProgressBar.Update(); } FilterByAmount(); + ResultBar.Update(); - Forms.Dialog.SaveWorkbookAs(); + Interface.Dialog.SaveWorkbookAs(); + ExcelApp.StatusBar = false; } private void GetSelected() diff --git a/src/PriceListTools/MergeTool.cs b/src/PriceListTools/MergeTool.cs index e196b3a..0e3f1dc 100644 --- a/src/PriceListTools/MergeTool.cs +++ b/src/PriceListTools/MergeTool.cs @@ -1,22 +1,32 @@ -using System.Collections.Generic; +using RehauSku.Interface; +using System.Collections.Generic; +using System.Linq; namespace RehauSku.PriceListTools { - internal class MergeTool : PriceListTool + internal class MergeTool : AbstractTool { - public List<Source> SourceFiles; + public List<SourcePriceList> SourceFiles; public void FillTarget() { - foreach (Source source in SourceFiles) + ProgressBar = new ProgressBar("Заполняю строки...", SourceFiles.Sum(x => x.PositionAmount.Count)); + ResultBar = new ResultBar(); + + foreach (SourcePriceList source in SourceFiles) { foreach (var kvp in source.PositionAmount) - FillColumnsWithDictionary(kvp, TargetFile.amountCell.Column); + { + FillPositionAmountToColumns(kvp, TargetFile.amountCell.Column); + ProgressBar.Update(); + } } FilterByAmount(); + ResultBar.Update(); - Forms.Dialog.SaveWorkbookAs(); + Dialog.SaveWorkbookAs(); + ExcelApp.StatusBar = false; } } } diff --git a/src/PriceListTools/Source.cs b/src/PriceListTools/SourcePriceList.cs index 7cf56be..01637f8 100644 --- a/src/PriceListTools/Source.cs +++ b/src/PriceListTools/SourcePriceList.cs @@ -3,14 +3,16 @@ using Microsoft.Office.Interop.Excel; using System; using System.Collections.Generic; using System.Linq; +using RehauSku.Interface; namespace RehauSku.PriceListTools { - internal class Source : PriceList + + internal class SourcePriceList : AbstractPriceList { public Dictionary<Position, double> PositionAmount { get; private set; } - public Source(Workbook workbook) + public SourcePriceList(Workbook workbook) { if (workbook == null) { @@ -20,7 +22,7 @@ namespace RehauSku.PriceListTools Sheet = workbook.ActiveSheet; Name = workbook.Name; - Range[] cells = new [] + Range[] cells = new[] { amountCell = Sheet.Cells.Find(amountHeader), skuCell = Sheet.Cells.Find(skuHeader), @@ -28,7 +30,7 @@ namespace RehauSku.PriceListTools nameCell = Sheet.Cells.Find(nameHeader) }; - if (cells.Any(x => x == null)) + if (cells.Any(x => x == null)) { throw new ArgumentException($"Файл {Name} не распознан"); } @@ -36,11 +38,12 @@ namespace RehauSku.PriceListTools CreatePositionsDict(); } - public static List<Source> GetSourceLists(string[] files) + public static List<SourcePriceList> GetSourceLists(string[] files) { var ExcelApp = (Application)ExcelDnaUtil.Application; + ProgressBar bar = new ProgressBar("Открываю исходные файлы...", files.Length); - List<Source> sourceFiles = new List<Source>(); + List<SourcePriceList> sourceFiles = new List<SourcePriceList>(); foreach (string file in files) { @@ -48,9 +51,10 @@ namespace RehauSku.PriceListTools Workbook wb = ExcelApp.Workbooks.Open(file); try { - Source priceList = new Source(wb); + SourcePriceList priceList = new SourcePriceList(wb); sourceFiles.Add(priceList); wb.Close(); + bar.Update(); } catch (Exception ex) { @@ -60,6 +64,7 @@ namespace RehauSku.PriceListTools System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Information); wb.Close(); + bar.Update(); } ExcelApp.ScreenUpdating = true; } @@ -81,6 +86,12 @@ namespace RehauSku.PriceListTools object name = Sheet.Cells[row, nameCell.Column].Value2; object sku = Sheet.Cells[row, skuCell.Column].Value2; + if (group == null || name == null || sku == null) + continue; + + if (!sku.ToString().IsRehauSku()) + continue; + Position p = new Position(group.ToString(), sku.ToString(), name.ToString()); if (PositionAmount.ContainsKey(p)) diff --git a/src/PriceListTools/Target.cs b/src/PriceListTools/TargetPriceList.cs index 996a092..32b071c 100644 --- a/src/PriceListTools/Target.cs +++ b/src/PriceListTools/TargetPriceList.cs @@ -4,12 +4,12 @@ using System.Linq; namespace RehauSku.PriceListTools { - internal class Target : PriceList + internal class TargetPriceList : AbstractPriceList { private const string oldSkuHeader = "Прежний материал"; public Range oldSkuCell { get; private set; } - public Target(Workbook workbook) + public TargetPriceList(Workbook workbook) { Sheet = workbook.ActiveSheet; Name = workbook.FullName; @@ -19,10 +19,11 @@ namespace RehauSku.PriceListTools amountCell = Sheet.Cells.Find(amountHeader), skuCell = Sheet.Cells.Find(skuHeader), groupCell = Sheet.Cells.Find(groupHeader), - nameCell = Sheet.Cells.Find(nameHeader), - oldSkuCell = Sheet.Cells.Find(oldSkuHeader) + nameCell = Sheet.Cells.Find(nameHeader) }; + oldSkuCell = Sheet.Cells.Find(oldSkuHeader); + if (cells.Any(x => x == null)) { throw new ArgumentException($"Шаблон { Name } не является прайс-листом"); diff --git a/src/RehauSku.Assist.csproj b/src/RehauSku.Assist.csproj index b2e14b7..76bd243 100644 --- a/src/RehauSku.Assist.csproj +++ b/src/RehauSku.Assist.csproj @@ -115,27 +115,32 @@ <Reference Include="WindowsBase" /> </ItemGroup> <ItemGroup> - <Compile Include="Forms\Dialog.cs" /> + <Compile Include="AddIn\EventsUtil.cs" /> + <Compile Include="Interface\AbstractBar.cs" /> + <Compile Include="Interface\Dialog.cs" /> <Compile Include="AddIn\RegistryUtil.cs" /> <Compile Include="AddIn\MemoryCacheUtil.cs" /> <Compile Include="Assistant\ParseUtil.cs" /> <Compile Include="Assistant\RequestModifier.cs" /> - <Compile Include="Assistant\SkuExtensions.cs" /> + <Compile Include="AddIn\SkuExtensions.cs" /> + <Compile Include="Interface\ProgressBar.cs" /> + <Compile Include="Interface\ResultBar.cs" /> <Compile Include="PriceListTools\CombineTool.cs" /> <Compile Include="PriceListTools\ConvertTool.cs" /> <Compile Include="PriceListTools\Position.cs" /> - <Compile Include="PriceListTools\PriceListTool.cs" /> + <Compile Include="PriceListTools\AbstractTool.cs" /> <Compile Include="PriceListTools\MergeTool.cs" /> - <Compile Include="PriceListTools\PriceList.cs" /> - <Compile Include="PriceListTools\Source.cs" /> - <Compile Include="PriceListTools\Target.cs" /> - <Compile Include="Ribbon\RibbonController.cs" /> + <Compile Include="PriceListTools\AbstractPriceList.cs" /> + <Compile Include="PriceListTools\SourcePriceList.cs" /> + <Compile Include="PriceListTools\TargetPriceList.cs" /> + <Compile Include="Interface\RibbonController.cs" /> <Compile Include="Assistant\HttpClientUtil.cs" /> <Compile Include="Assistant\StoreResponse.cs" /> <Compile Include="PriceListTools\ExportTool.cs" /> <Compile Include="AddIn\AddIn.cs" /> <Compile Include="Assistant\IProduct.cs" /> <Compile Include="AddIn\Functions.cs" /> + <Compile Include="AddIn\WorksheetExtensions.cs" /> <Compile Include="Properties\AssemblyInfo.cs" /> <Compile Include="Assistant\SkuAssist.cs" /> </ItemGroup> |