Merge pull request #12 from schebotar/dev

Dev

14 files changed, 504 insertions, 364 deletions

diff --git a/README.md b/README.md
index 92c5f1c..4e1bb53 100644
--- a/README.md
+++ b/README.md
@@ -11,6 +11,7 @@
     - Отображение артикула с помощью `=RAUSKU()`
     - Отображение цены с помощью формулы `=RAUPRICE()`
 - Экспорт массива ячеек вида "Артикул - Количество" в прайс-лист
+- Актуализация прайс-листа
 - Объединение нескольких прайс-листов в один файл
     - Сложением всех позиций по артикулам
     - С разнесением данных по колонкам в конечном файле
diff --git a/src/PriceListTools/AbstractPriceListTool.cs b/src/PriceListTools/AbstractPriceListTool.cs
deleted file mode 100644
index 1aef0be..0000000
--- a/src/PriceListTools/AbstractPriceListTool.cs
+++ /dev/null
@@ -1,65 +0,0 @@
-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
index cf02059..dc0f2af 100644
--- a/src/PriceListTools/CombineTool.cs
+++ b/src/PriceListTools/CombineTool.cs
@@ -1,76 +1,33 @@
 using Microsoft.Office.Interop.Excel;
-using System;
+using System.Collections.Generic;
 
 namespace RehauSku.PriceListTools
 {
-    internal class CombineTool : AbstractPriceListTool, IDisposable
+    internal class CombineTool : PriceListTool
     {
-        public override void FillPriceList()
-        {
-            PriceListSheet offer = NewPriceList.OfferSheet;
-            offer.Sheet.Activate();
+        public List<Source> SourceFiles;
 
-            int exportedValues = 0;
-            int exportedLists = 0;
+        public void FillTarget()
+        {
+            ExcelApp.ScreenUpdating = false;
 
-            foreach (var priceList in sourcePriceLists)
+            foreach (Source source in SourceFiles)
             {
-                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];
+                TargetFile.Sheet.Columns[TargetFile.amountCell.Column]
+                    .EntireColumn
+                    .Insert(XlInsertShiftDirection.xlShiftToRight, XlInsertFormatOrigin.xlFormatFromRightOrBelow);
 
-                            if (sumCell.Value2 == null)
-                                sumCell.Value2 = kvp.Value;
-                            else
-                                sumCell.Value2 += kvp.Value;
+                Range newColumnHeader = TargetFile.Sheet.Cells[TargetFile.amountCell.Row, TargetFile.amountCell.Column - 1];
+                newColumnHeader.Value2 = $"{source.Name}";
+                newColumnHeader.WrapText = true;
 
-                            exportedValues++;
-                        }
-
-                        offer.Sheet.Cells[offer.headerRowNumber, offer.amountColumnNumber].Value2 = $"{priceList.Name}\n{sheet.Name}";
-                    }
-                }
+                FillColumnsWithDictionary(source.PositionAmount, TargetFile.amountCell.Column - 1, TargetFile.amountCell.Column);
             }
 
-            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} файлов";
+            FilterByAmount();
+            ExcelApp.ScreenUpdating = true;
 
             Forms.Dialog.SaveWorkbookAs();
         }
-
-        public void Dispose()
-        {
-            GC.SuppressFinalize(this);
-        }
     }
 }
diff --git a/src/PriceListTools/ConvertTool.cs b/src/PriceListTools/ConvertTool.cs
new file mode 100644
index 0000000..48e93d2
--- /dev/null
+++ b/src/PriceListTools/ConvertTool.cs
@@ -0,0 +1,37 @@
+using System;
+
+namespace RehauSku.PriceListTools
+{
+    internal class ConvertTool : PriceListTool
+    {
+        private Source Current;
+
+        public void GetCurrent()
+        {
+            try
+            {
+                Current = new Source(ExcelApp.ActiveWorkbook);
+            }
+
+            catch (Exception exception)
+            {
+                System.Windows.Forms.MessageBox.Show
+                    (exception.Message,
+                    "Ошибка распознавания",
+                    System.Windows.Forms.MessageBoxButtons.OK,
+                    System.Windows.Forms.MessageBoxIcon.Information);
+                throw exception;
+            }
+        }
+
+        public void FillTarget()
+        {
+            ExcelApp.ScreenUpdating = false;            
+            FillColumnsWithDictionary(Current.PositionAmount, TargetFile.amountCell.Column);
+            FilterByAmount();
+            ExcelApp.ScreenUpdating = true;
+
+            Forms.Dialog.SaveWorkbookAs();
+        }
+    }
+}
\ No newline at end of file
diff --git a/src/PriceListTools/ExportTool.cs b/src/PriceListTools/ExportTool.cs
index a93097d..8bf274a 100644
--- a/src/PriceListTools/ExportTool.cs
+++ b/src/PriceListTools/ExportTool.cs
@@ -1,34 +1,77 @@
-using ExcelDna.Integration;
-using Microsoft.Office.Interop.Excel;
+using Microsoft.Office.Interop.Excel;
 using RehauSku.Assistant;
 using System;
 using System.Collections.Generic;
 
 namespace RehauSku.PriceListTools
 {
-    internal class ExportTool : AbstractPriceListTool, IDisposable
+    internal class ExportTool : PriceListTool
     {
         private Dictionary<string, double> SkuAmount { get; set; }
         private Range Selection;
 
-        public ExportTool()
+        public void TryGetSelection()
         {
-            ExcelApp = (Application)ExcelDnaUtil.Application;
             Selection = ExcelApp.Selection;
+
+            if (Selection == null || Selection.Columns.Count != 2)
+            {
+                throw new Exception("Неверный диапазон");
+            }
+        }
+
+        public void FillTarget()
+        {
+            ExcelApp.ScreenUpdating = false;
+            GetSelected();
+            FillColumn(SkuAmount, TargetFile.amountCell.Column);
+            FilterByAmount();
+            ExcelApp.ScreenUpdating = true;
+
+            Forms.Dialog.SaveWorkbookAs();
         }
 
-        public override void GetSource()
+        private void FillColumn(IEnumerable<KeyValuePair<string, double>> dictionary, int column)
         {
-            if (Selection != null && Selection.Columns.Count == 2)
-                FillSkuAmountDict();
+            List<KeyValuePair<string, double>> missing = new List<KeyValuePair<string, double>>();
 
-            else throw new Exception("Неверный диапазон");
+            foreach (var kvp in dictionary)
+            {
+                Range cell = TargetFile.skuCell.EntireColumn.Find(kvp.Key);
+
+                if (cell == null)
+                {
+                    missing.Add(kvp);
+                }
+
+                else
+                {
+                    Range sumCell = TargetFile.Sheet.Cells[cell.Row, column];
+
+                    if (sumCell.Value2 == null)
+                    {
+                        sumCell.Value2 = kvp.Value;
+                    }
+
+                    else
+                    {
+                        sumCell.Value2 += kvp.Value;
+                    }
+                }
+            }
+
+            if (missing.Count > 0)
+            {
+                System.Windows.Forms.MessageBox.Show
+                    ($"{missing.Count} артикулов отсутствует в таблице заказов {RegistryUtil.PriceListPath} Попробовать найти новый вариант?",
+                    "Отсутствует позиция в конечной таблице заказов",
+                    System.Windows.Forms.MessageBoxButtons.YesNo,
+                    System.Windows.Forms.MessageBoxIcon.Information);
+            }
         }
 
-        public override void GetSource(string[] files)
-            => GetSource();
 
-        private void FillSkuAmountDict()
+        private void GetSelected()
         {
             object[,] cells = Selection.Value2;
             SkuAmount = new Dictionary<string, double>();
@@ -64,63 +107,19 @@ namespace RehauSku.PriceListTools
                 }
 
                 if (sku == null || amount == null)
+                {
                     continue;
+                }
 
                 if (SkuAmount.ContainsKey(sku))
-                    SkuAmount[sku] += amount.Value;
-                else
-                    SkuAmount.Add(sku, amount.Value);
-            }
-        }
-
-        public override void FillPriceList()
-        {
-            if (SkuAmount.Count < 1) return;
-
-            PriceListSheet offer = NewPriceList.OfferSheet;
-            offer.Sheet.Activate();
-
-            int exportedValues = 0;
-
-            foreach (var kvp in 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);
+                    SkuAmount[sku] += amount.Value;
                 }
-
                 else
                 {
-                    Range sumCell = offer.Sheet.Cells[cell.Row, offer.amountColumnNumber];
-
-                    if (sumCell.Value2 == null)
-                        sumCell.Value2 = kvp.Value;
-                    else
-                        sumCell.Value2 += kvp.Value;
-
-                    exportedValues++;
+                    SkuAmount.Add(sku, amount.Value);
                 }
             }
-
-            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()
-        {
-            GC.SuppressFinalize(this);
         }
     }
 }
diff --git a/src/PriceListTools/MergeTool.cs b/src/PriceListTools/MergeTool.cs
index 493f8a8..0d3e8eb 100644
--- a/src/PriceListTools/MergeTool.cs
+++ b/src/PriceListTools/MergeTool.cs
@@ -1,65 +1,25 @@
-using Microsoft.Office.Interop.Excel;
-using System;
+using System.Collections.Generic;
+using System.Linq;
 
 namespace RehauSku.PriceListTools
 {
-    internal class MergeTool : AbstractPriceListTool, IDisposable
+    internal class MergeTool : PriceListTool
     {
-        public override void FillPriceList()
-        {
-            PriceListSheet offer = NewPriceList.OfferSheet;
-            offer.Sheet.Activate();
+        public List<Source> SourceFiles;
 
-            int exportedValues = 0;
+        public void FillTarget()
+        {
+            ExcelApp.ScreenUpdating = false;
 
-            foreach (var priceList in sourcePriceLists)
+            foreach (Source source in SourceFiles)
             {
-                foreach (var sheet in priceList.Sheets)
-                {
-                    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++;
-                        }
-                    }
-                }
+                FillColumnsWithDictionary(source.PositionAmount, TargetFile.amountCell.Column);
             }
 
-            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} строк из {sourcePriceLists.Count} файлов";
+            FilterByAmount();
+            ExcelApp.ScreenUpdating = true;
 
             Forms.Dialog.SaveWorkbookAs();
         }
-
-        public void Dispose()
-        {
-            GC.SuppressFinalize(this);
-        }
     }
 }
diff --git a/src/PriceListTools/Position.cs b/src/PriceListTools/Position.cs
new file mode 100644
index 0000000..471aa59
--- /dev/null
+++ b/src/PriceListTools/Position.cs
@@ -0,0 +1,17 @@
+namespace RehauSku.PriceListTools
+{
+    public class Position
+    {
+        public string Group { get; private set; }
+        public string Sku { get; private set; }
+        public string Name { get; private set; }
+
+        public Position(string group, string sku, string name)
+        {
+            Group = group;
+            Sku = sku;
+            Name = name;
+        }
+    }
+}
+
diff --git a/src/PriceListTools/PriceList.cs b/src/PriceListTools/PriceList.cs
index bc11a17..65ff3df 100644
--- a/src/PriceListTools/PriceList.cs
+++ b/src/PriceListTools/PriceList.cs
@@ -1,42 +1,20 @@
 using Microsoft.Office.Interop.Excel;
-using System.Collections.Generic;
-using System.IO;
-using System.Linq;
 
 namespace RehauSku.PriceListTools
 {
     internal class PriceList
     {
-        public readonly string Name;
-        public readonly PriceListSheet OfferSheet;
-        public List<PriceListSheet> Sheets { get; private set; }
-
-        private const string offerSheetHeader = "КП";
-
-        public PriceList(Workbook workbook)
-        {
-            Name = workbook.Name;
-            Sheets = new List<PriceListSheet>();
-
-            foreach (Worksheet worksheet in workbook.Sheets)
-            {
-                PriceListSheet priceListSheet = new PriceListSheet(worksheet);
-
-                if (priceListSheet.FillSkuAmount())
-                    Sheets.Add(priceListSheet);
-            }
-
-            OfferSheet = Sheets.Where(s => s.Name == offerSheetHeader).FirstOrDefault();
-        }
-
-        public static string CreateNewFile()
-        {
-            string fileExtension = Path.GetExtension(RegistryUtil.PriceListPath);
-            string path = Path.GetTempFileName() + fileExtension;
-
-            File.Copy(RegistryUtil.PriceListPath, path);
-            return path;
-        }
+        protected const string amountHeader = "Кол-во";
+        protected const string skuHeader = "Актуальный материал";
+        protected const string groupHeader = "Программа";
+        protected const string nameHeader = "Наименование";
+
+        public Range amountCell { get; protected set; }
+        public Range skuCell { get; protected set; }
+        public Range groupCell { get; protected set; }
+        public Range nameCell { get; protected set; }
+
+        public Worksheet Sheet { get; protected set; }
+        public string Name { get; protected set; }
     }
-}
-
+}
\ No newline at end of file
diff --git a/src/PriceListTools/PriceListSheet.cs b/src/PriceListTools/PriceListSheet.cs
deleted file mode 100644
index 8a34c2f..0000000
--- a/src/PriceListTools/PriceListSheet.cs
+++ /dev/null
@@ -1,64 +0,0 @@
-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/PriceListTool.cs b/src/PriceListTools/PriceListTool.cs
new file mode 100644
index 0000000..9d898e7
--- /dev/null
+++ b/src/PriceListTools/PriceListTool.cs
@@ -0,0 +1,195 @@
+using ExcelDna.Integration;
+using Microsoft.Office.Interop.Excel;
+using System;
+using System.Collections.Generic;
+using System.Windows.Forms;
+using Application = Microsoft.Office.Interop.Excel.Application;
+
+namespace RehauSku.PriceListTools
+{
+    internal abstract class PriceListTool
+    {
+        protected private Application ExcelApp = (Application)ExcelDnaUtil.Application;
+        protected private Target TargetFile;
+        protected private List<KeyValuePair<Position, double>> Missing;
+
+        public void OpenNewPrice()
+        {
+            Workbook wb = ExcelApp.Workbooks.Open(RegistryUtil.PriceListPath);
+
+            try
+            {
+                TargetFile = new Target(wb);
+            }
+
+            catch (Exception ex)
+            {
+                MessageBox.Show
+                    (ex.Message,
+                    "Ошибка открытия шаблонного прайс-листа",
+                    MessageBoxButtons.OK,
+                    MessageBoxIcon.Information);
+                wb.Close();
+                throw ex;
+            }
+        }
+
+        protected private void FillColumnsWithDictionary(IEnumerable<KeyValuePair<Position, double>> dictionary, params int[] columns)
+        {
+            Missing = new List<KeyValuePair<Position, double>>();
+
+            foreach (var positionAmount in dictionary)
+            {
+                FillPositionAmountToColumns(positionAmount, columns);
+            }
+
+            if (Missing.Count > 0)
+            { 
+                DialogResult result = 
+                MessageBox.Show
+                    ($"{Missing.Count} артикулов отсутствует в таблице заказов {RegistryUtil.PriceListPath} Попробовать найти новый вариант?",
+                    "Отсутствует позиция в конечной таблице заказов",
+                    MessageBoxButtons.YesNo,
+                    MessageBoxIcon.Information);
+
+                if (result == DialogResult.Yes)
+                {
+                    var lookUp = new List<KeyValuePair<Position, double>>(Missing);
+
+                    foreach (var missingPosition in lookUp)
+                    {
+                        TryFillVariantlessSkuToColumns(missingPosition, columns);
+                    }
+                }
+            }
+
+            if (Missing.Count > 0)
+            {
+                FillMissing();
+                MessageBox.Show
+                    ($"{Missing.Count} артикулов отсутствует в таблице заказов {RegistryUtil.PriceListPath}\n" +
+                    $"Под основной таблицей составлен список не найденных артикулов",
+                    "Отсутствует позиция в конечной таблице заказов",
+                    MessageBoxButtons.OK,
+                    MessageBoxIcon.Information);
+            }
+        }
+
+        protected private void FillPositionAmountToColumns(KeyValuePair<Position, double> positionAmount, int[] columns)
+        {
+            Range foundCell = TargetFile.skuCell.EntireColumn.Find(positionAmount.Key.Sku);
+
+            if (foundCell == null)
+            {
+                Missing.Add(positionAmount);
+                return;
+            }
+
+            string foundCellGroup = TargetFile.Sheet.Cells[foundCell.Row, TargetFile.groupCell.Column].Value2.ToString();
+
+            while (foundCell != null && foundCellGroup != positionAmount.Key.Group)
+            {
+                foundCell = TargetFile.skuCell.EntireColumn.FindNext(foundCell);
+                foundCellGroup = TargetFile.Sheet.Cells[foundCell.Row, TargetFile.groupCell.Column].Value2.ToString();
+            }
+
+            if (foundCell == null)
+            {
+                Missing.Add(positionAmount);
+            }
+
+            else
+            {
+                foreach (var column in columns)
+                {
+                    Range sumCell = TargetFile.Sheet.Cells[foundCell.Row, column];
+
+                    if (sumCell.Value2 == null)
+                    {
+                        sumCell.Value2 = positionAmount.Value;
+                    }
+
+                    else
+                    {
+                        sumCell.Value2 += positionAmount.Value;
+                    }
+                }
+            }
+        }
+
+        protected private void TryFillVariantlessSkuToColumns(KeyValuePair<Position, double> positionAmount, int[] columns)
+        {
+            string sku = positionAmount.Key.Sku.Substring(1, 6);
+
+            Range foundCell = TargetFile.skuCell.EntireColumn.Find(sku);
+
+            if (foundCell == null)
+            {
+                return;
+            }
+
+            string foundCellGroup = TargetFile.Sheet.Cells[foundCell.Row, TargetFile.groupCell.Column].Value2.ToString();
+
+            while (foundCell != null && foundCellGroup != positionAmount.Key.Group)
+            {
+                foundCell = TargetFile.skuCell.EntireColumn.FindNext(foundCell);
+                foundCellGroup = TargetFile.Sheet.Cells[foundCell.Row, TargetFile.groupCell.Column].Value2.ToString();
+            }
+
+            if (foundCell == null)
+            {
+                return;
+            }
+
+            foreach (var column in columns)
+            {
+                Range sumCell = TargetFile.Sheet.Cells[foundCell.Row, column];
+
+                if (sumCell.Value2 == null)
+                {
+                    sumCell.Value2 = positionAmount.Value;
+                }
+
+                else
+                {
+                    sumCell.Value2 += positionAmount.Value;
+                }
+            }
+
+            Missing.Remove(positionAmount);
+        }
+
+        protected private void FillMissing()
+        {
+            int startRow =
+                TargetFile.Sheet.AutoFilter.Range.Row + 
+                TargetFile.Sheet.AutoFilter.Range.Rows.Count + 5;
+
+            for (int i = 0; i < Missing.Count; i++)
+            {
+                Range group = TargetFile.Sheet.Cells[startRow + i, TargetFile.groupCell.Column];
+                Range sku = TargetFile.Sheet.Cells[startRow + i, TargetFile.skuCell.Column];
+                Range name = TargetFile.Sheet.Cells[startRow + i, TargetFile.nameCell.Column];
+                Range amount = TargetFile.Sheet.Cells[startRow + i, TargetFile.amountCell.Column];
+
+                group.Value2 = Missing[i].Key.Group;
+                sku.Value2 = Missing[i].Key.Sku;
+                name.Value2 = Missing[i].Key.Name;
+                amount.Value2 = Missing[i].Value;
+
+                group.ClearFormats();
+                sku.ClearFormats();
+                name.ClearFormats();
+                amount.ClearFormats();
+            }
+        }
+
+        protected private void FilterByAmount()
+        {
+            AutoFilter filter = TargetFile.Sheet.AutoFilter;
+
+            filter.Range.AutoFilter(TargetFile.amountCell.Column, "<>");
+            TargetFile.Sheet.Range["A1"].Activate();
+        }
+    }
+}
\ No newline at end of file
diff --git a/src/PriceListTools/Source.cs b/src/PriceListTools/Source.cs
new file mode 100644
index 0000000..5013157
--- /dev/null
+++ b/src/PriceListTools/Source.cs
@@ -0,0 +1,95 @@
+using ExcelDna.Integration;
+using Microsoft.Office.Interop.Excel;
+using System;
+using System.Collections.Generic;
+
+namespace RehauSku.PriceListTools
+{
+    internal class Source : PriceList
+    {
+        public Dictionary<Position, double> PositionAmount { get; private set; }
+
+        public Source(Workbook workbook)
+        {
+            if (workbook == null)
+            {
+                throw new ArgumentException($"Нет рабочего файла");
+            }
+
+            Sheet = workbook.ActiveSheet;
+            Name = workbook.Name;
+
+            amountCell = Sheet.Cells.Find(amountHeader);
+            skuCell = Sheet.Cells.Find(skuHeader);
+            groupCell = Sheet.Cells.Find(groupHeader);
+            nameCell = Sheet.Cells.Find(nameHeader);
+
+            if (amountCell == null || skuCell == null || groupCell == null || nameCell == null)
+            {
+                throw new ArgumentException($"Файл {Name} не распознан");
+            }
+
+            CreatePositionsDict();
+        }
+
+        public static List<Source> GetSourceLists(string[] files)
+        {
+            var ExcelApp = (Application)ExcelDnaUtil.Application;
+
+            List<Source> sourceFiles = new List<Source>();
+
+            ExcelApp.ScreenUpdating = false;
+            foreach (string file in files)
+            {
+                Workbook wb = ExcelApp.Workbooks.Open(file);
+                try
+                {
+                    Source priceList = new Source(wb);
+                    sourceFiles.Add(priceList);
+                    wb.Close();
+                }
+                catch (Exception ex)
+                {
+                    System.Windows.Forms.MessageBox.Show
+                        (ex.Message,
+                        "Ошибка открытия исходного прайс-листа",
+                        System.Windows.Forms.MessageBoxButtons.OK,
+                        System.Windows.Forms.MessageBoxIcon.Information);
+                    wb.Close();
+                }
+            }
+            ExcelApp.ScreenUpdating = true;
+
+            return sourceFiles;
+        }
+
+        private void CreatePositionsDict()
+        {
+            PositionAmount = new Dictionary<Position, double>();
+
+            for (int row = amountCell.Row + 1; row < Sheet.AutoFilter.Range.Rows.Count; row++)
+            {
+                object amount = Sheet.Cells[row, amountCell.Column].Value2;
+
+                if (amount != null && (double)amount != 0)
+                {
+                    object group = Sheet.Cells[row, groupCell.Column].Value2;
+                    object name = Sheet.Cells[row, nameCell.Column].Value2;
+                    object sku = Sheet.Cells[row, skuCell.Column].Value2;
+
+                    Position p = new Position(group.ToString(), sku.ToString(), name.ToString());
+
+                    if (PositionAmount.ContainsKey(p))
+                    {
+                        PositionAmount[p] += (double)amount;
+                    }
+                    else
+                    {
+                        PositionAmount.Add(p, (double)amount);
+                    }
+                }
+            }
+        }
+    }
+}
+
diff --git a/src/PriceListTools/Target.cs b/src/PriceListTools/Target.cs
new file mode 100644
index 0000000..a7e87ec
--- /dev/null
+++ b/src/PriceListTools/Target.cs
@@ -0,0 +1,25 @@
+using Microsoft.Office.Interop.Excel;
+using System;
+
+namespace RehauSku.PriceListTools
+{
+    internal class Target : PriceList
+    {
+        public Target(Workbook workbook)
+        {
+            Sheet = workbook.ActiveSheet;
+            Name = workbook.FullName;
+
+            amountCell = Sheet.Cells.Find(amountHeader);
+            skuCell = Sheet.Cells.Find(skuHeader);
+            groupCell = Sheet.Cells.Find(groupHeader);
+            nameCell = Sheet.Cells.Find(nameHeader);
+
+            if (amountCell == null || skuCell == null || groupCell == null || nameCell == null)
+            {
+                throw new ArgumentException($"Шаблон { Name } не является прайс-листом");
+            }
+        }
+    }
+}
+
diff --git a/src/RehauSku.Assist.csproj b/src/RehauSku.Assist.csproj
index 2c6792c..b2e14b7 100644
--- a/src/RehauSku.Assist.csproj
+++ b/src/RehauSku.Assist.csproj
@@ -122,10 +122,13 @@
     <Compile Include="Assistant\RequestModifier.cs" />
     <Compile Include="Assistant\SkuExtensions.cs" />
     <Compile Include="PriceListTools\CombineTool.cs" />
-    <Compile Include="PriceListTools\AbstractPriceListTool.cs" />
+    <Compile Include="PriceListTools\ConvertTool.cs" />
+    <Compile Include="PriceListTools\Position.cs" />
+    <Compile Include="PriceListTools\PriceListTool.cs" />
     <Compile Include="PriceListTools\MergeTool.cs" />
     <Compile Include="PriceListTools\PriceList.cs" />
-    <Compile Include="PriceListTools\PriceListSheet.cs" />
+    <Compile Include="PriceListTools\Source.cs" />
+    <Compile Include="PriceListTools\Target.cs" />
     <Compile Include="Ribbon\RibbonController.cs" />
     <Compile Include="Assistant\HttpClientUtil.cs" />
     <Compile Include="Assistant\StoreResponse.cs" />
diff --git a/src/Ribbon/RibbonController.cs b/src/Ribbon/RibbonController.cs
index ed59541..9011a43 100644
--- a/src/Ribbon/RibbonController.cs
+++ b/src/Ribbon/RibbonController.cs
@@ -4,6 +4,7 @@ using ExcelDna.Integration.CustomUI;
 using RehauSku.PriceListTools;
 using RehauSku.Forms;
 using System;
+using System.Collections.Generic;
 
 namespace RehauSku.Ribbon
 {
@@ -19,13 +20,14 @@ namespace RehauSku.Ribbon
           <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'/> 
                 <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='large' imageMso='CurrentViewSettings' onAction='OnSetPricePressed'/>
+                <button id='setPriceList' label='Указать путь к шаблону' size='large' imageMso='CurrentViewSettings' onAction='OnSetPricePressed'/>
             </group>
           </tab>
         </tabs>
@@ -33,35 +35,29 @@ 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())
+            MergeTool mergeTool = new MergeTool();
+            string[] files = Dialog.GetMultiplyFiles();
+
+            if (files.Length != 0)
             {
-                string[] files = Dialog.GetMultiplyFiles();
-                if (files.Length != 0)
-                {
-                    mergeTool.GetSource(files);
-                    string exportFile = RegistryUtil.PriceListPath;
-                    mergeTool.OpenNewPrice(exportFile);
-                    mergeTool.FillPriceList();
-                }
+                mergeTool.SourceFiles = Source.GetSourceLists(files);
+                mergeTool.OpenNewPrice();
+                mergeTool.FillTarget();
             }
         }
 
         public void OnCombinePressed(IRibbonControl control)
         {
-            using (CombineTool combineTool = new CombineTool())
+            CombineTool combineTool = new CombineTool();
+            string[] files = Dialog.GetMultiplyFiles();
+
+            if (files.Length != 0)
             {
-                string[] files = Dialog.GetMultiplyFiles();
-                if (files.Length != 0)
-                {
-                    combineTool.GetSource(files);
-                    string exportFile = RegistryUtil.PriceListPath;
-                    combineTool.OpenNewPrice(exportFile);
-                    combineTool.FillPriceList();
-                }
+                combineTool.SourceFiles = Source.GetSourceLists(files);
+                combineTool.OpenNewPrice();
+                combineTool.FillTarget();
             }
         }
 
@@ -69,14 +65,12 @@ namespace RehauSku.Ribbon
         {
             try
             {
-                using (ExportTool exportTool = new ExportTool())
-                {
-                    exportTool.GetSource();
-                    string exportFile = RegistryUtil.PriceListPath;
-                    exportTool.OpenNewPrice(exportFile);
-                    exportTool.FillPriceList();
-                }
+                ExportTool exportTool = new ExportTool();
+                exportTool.TryGetSelection();
+                exportTool.OpenNewPrice();
+                exportTool.FillTarget();
             }
+
             catch (Exception ex)
             {
                 MessageBox.Show(ex.Message,
@@ -85,7 +79,15 @@ namespace RehauSku.Ribbon
                     MessageBoxIcon.Information);
                 return;
             }
+        }
+
+        public void OnConvertPressed(IRibbonControl control)
+        {
+            ConvertTool convertTool = new ConvertTool();
 
+            convertTool.GetCurrent();
+            convertTool.OpenNewPrice();
+            convertTool.FillTarget();
         }
 
         public void OnSetPricePressed(IRibbonControl control)