Merge pull request #8 from schebotar/dev

Dev

21 files changed, 963 insertions, 144 deletions

diff --git a/Source/ExcelDNA/AddIn.cs b/Source/AddIn/AddIn.cs
index 0505e5b..67cdcc8 100644
--- a/Source/ExcelDNA/AddIn.cs
+++ b/Source/AddIn/AddIn.cs
@@ -1,21 +1,40 @@
 using ExcelDna.Integration;
+using ExcelDna.IntelliSense;
 using ExcelDna.Registration;
 using System.Net.Http;
+using System.Runtime.Caching;
 
-namespace Rehau.Sku.Assist
+
+namespace RehauSku
 {
+    public enum ResponseOrder
+    {
+        Default,
+        Relevance,
+        Name,
+        Price,
+        Series
+    }
+
     public class AddIn : IExcelAddIn
     {
         public static HttpClient httpClient;
+        public static MemoryCache memoryCache;
 
         public void AutoOpen()
         {
-            RegisterFunctions();
             httpClient = new HttpClient();
+            memoryCache = new MemoryCache("RehauSku");
+            RegisterFunctions();
+            IntelliSenseServer.Install();
+            RegistryUtil.Initialize();
         }
 
         public void AutoClose()
         {
+            IntelliSenseServer.Uninstall();
+            RegistryUtil.Uninitialize();
+            memoryCache.Dispose();
         }
 
         void RegisterFunctions()
diff --git a/Source/AddIn/Functions.cs b/Source/AddIn/Functions.cs
new file mode 100644
index 0000000..618d17d
--- /dev/null
+++ b/Source/AddIn/Functions.cs
@@ -0,0 +1,56 @@
+using ExcelDna.Integration;
+using RehauSku.Assistant;
+
+namespace RehauSku
+{
+    public class Functions
+    {
+        [ExcelFunction(description: "Получение названия первого продукта в поиске")]
+        public static object RAUNAME([ExcelArgument(Name = "\"Запрос\"", Description = "в свободной форме или ячейка с запросом")] string request)
+            => MakeRequest(request, ProductField.Name);
+
+        [ExcelFunction(Description = "Получение артикула первого продукта в поиске")]
+        public static object RAUSKU([ExcelArgument(Name = "\"Запрос\"", Description = "в свободной форме или ячейка с запросом")] string request)
+            => MakeRequest(request, ProductField.Id);
+
+        [ExcelFunction(Description = "Получение цены первого продукта в поиске")]
+        public static object RAUPRICE([ExcelArgument(Name = "\"Запрос\"", Description = "в свободной форме или ячейка с запросом")] string request)
+            => MakeRequest(request, ProductField.Price);
+
+        private static object MakeRequest(string request, ProductField field)
+        {
+            object result;
+
+            if (request.IsCached())
+                result = request.GetFromCache();
+
+            else
+            {
+                result = ExcelAsyncUtil.Run("Request", request, delegate
+                {
+                    return request.RequestAndCache().GetAwaiter().GetResult();
+                });
+            }
+
+            if (result == null)
+                return "Не найдено :(";
+
+            if (result.Equals(ExcelError.ExcelErrorNA))
+                return "Загрузка...";
+
+            IProduct product = result as IProduct;
+
+            switch (field)
+            {
+                case ProductField.Name:
+                    return product.Name;
+                case ProductField.Id:
+                    return product.Id;
+                case ProductField.Price:
+                    return double.Parse(product.Price, System.Globalization.CultureInfo.InvariantCulture);
+                default:
+                    return null;
+            }
+        }
+    }
+}
\ No newline at end of file
diff --git a/Source/AddIn/MemoryCacheUtil.cs b/Source/AddIn/MemoryCacheUtil.cs
new file mode 100644
index 0000000..1d42e14
--- /dev/null
+++ b/Source/AddIn/MemoryCacheUtil.cs
@@ -0,0 +1,37 @@
+using System;
+using System.Runtime.Caching;
+using System.Threading.Tasks;
+using RehauSku.Assistant;
+
+namespace RehauSku
+{
+    static class MemoryCacheUtil
+    {
+        public static bool IsCached(this string request)
+        {
+            return AddIn.memoryCache.Contains(request);
+        }
+
+        public static IProduct GetFromCache(this string request)
+        {
+            return AddIn.memoryCache[request] as IProduct;
+        }
+
+        public static async Task<IProduct> RequestAndCache(this string request)
+        {
+            IProduct product = await SkuAssist.GetProductAsync(request);
+
+            if (product == null)
+                return null;
+
+            AddIn.memoryCache.Add(request, product, DateTime.Now.AddMinutes(10));
+            return product;
+        }
+
+        public static void ClearCache()
+        {
+            AddIn.memoryCache.Dispose();
+            AddIn.memoryCache = new MemoryCache("RehauSku");
+        }
+    }
+}
\ No newline at end of file
diff --git a/Source/AddIn/RegistryUtil.cs b/Source/AddIn/RegistryUtil.cs
new file mode 100644
index 0000000..40d0ec2
--- /dev/null
+++ b/Source/AddIn/RegistryUtil.cs
@@ -0,0 +1,76 @@
+using Microsoft.Win32;
+using System.IO;
+using RehauSku.Forms;
+using System.Windows.Forms;
+
+namespace RehauSku
+{
+    static class RegistryUtil
+    {
+        private static string _priceListPath;
+        private static int? _storeResponseOrder;
+        private static RegistryKey _RootKey { get; set; }
+
+        public static void Initialize()
+        {
+            _RootKey = Registry.CurrentUser.CreateSubKey(@"SOFTWARE\REHAU\SkuAssist"); 
+            _priceListPath = _RootKey.GetValue("PriceListPath") as string;
+            _storeResponseOrder = _RootKey.GetValue("StoreResponseOrder") as int?;
+        }
+
+        public static void Uninitialize()
+        {
+            _RootKey.Close();
+            
+        }
+
+        public static bool IsPriceListPathEmpty()
+        {
+            return string.IsNullOrEmpty(_priceListPath);
+        }
+
+        public static string PriceListPath
+        {
+            get
+            {
+                if (IsPriceListPathEmpty() || !File.Exists(_priceListPath))
+                {
+                    MessageBox.Show("Прайс-лист отсутствует или неверный файл прайс-листа", "Укажите файл прайс-листа", MessageBoxButtons.OK, MessageBoxIcon.Warning);
+                    string fileName = Dialog.GetFilePath();
+                    _priceListPath = fileName;
+                    _RootKey.SetValue("PriceListPath", fileName);
+                    return _priceListPath;
+                }
+
+                else
+                {
+                    return _priceListPath;
+                }
+            }
+
+            set
+            {
+                _priceListPath = value;
+                _RootKey.SetValue("PriceListPath", value);
+            }
+        }
+
+        public static ResponseOrder StoreResponseOrder
+        {
+            get
+            {
+                if (_storeResponseOrder == null)
+                {
+                    _RootKey.SetValue("StoreResponseOrder", (int)ResponseOrder.Default);
+                    _storeResponseOrder = (int)ResponseOrder.Default;
+                    return (ResponseOrder)_storeResponseOrder.Value;
+                }
+
+                else
+                {
+                    return (ResponseOrder)_storeResponseOrder.Value;
+                }
+            }
+        }
+    }
+}
diff --git a/Source/Assistant/HttpClientUtil.cs b/Source/Assistant/HttpClientUtil.cs
index 3ad5d85..316ea07 100644
--- a/Source/Assistant/HttpClientUtil.cs
+++ b/Source/Assistant/HttpClientUtil.cs
@@ -1,19 +1,18 @@
-using AngleSharp;
-using AngleSharp.Dom;
-using System;
+using System;
 using System.Net;
 using System.Net.Http;
 using System.Threading.Tasks;
-using System.Text;
 
-namespace Rehau.Sku.Assist
+namespace RehauSku.Assistant
 {
     static class HttpClientUtil
     {
         private static HttpClient _httpClient = AddIn.httpClient;
 
-        public async static Task<string> GetContentByUriAsync(Uri uri)
+        public async static Task<string> GetContentByRequest(string request)
         {
+            Uri uri = request.ConvertToUri();
+
             ServicePointManager.SecurityProtocol =
                 SecurityProtocolType.Tls12 |
                 SecurityProtocolType.Tls11 |
@@ -22,22 +21,14 @@ namespace Rehau.Sku.Assist
             return await _httpClient.GetStringAsync(uri);
         }
 
-        public async static Task<IDocument> ContentToDocAsync(Task<string> content)
-        {
-            IConfiguration config = Configuration.Default;
-            IBrowsingContext context = BrowsingContext.New(config);
-
-            return await context.OpenAsync(req => req.Content(content.Result));
-        }
-
-        public static Uri ConvertToUri(this string request, ResponseOrder order)
+        private static Uri ConvertToUri(this string request)
         {
             UriBuilder baseUri = new UriBuilder("https", "shop-rehau.ru");
 
             baseUri.Path = "/catalogsearch/result/index/";
-            string cleanedRequest = request._CleanRequest();
+            string cleanedRequest = request.CleanRequest();
 
-            switch (order)
+            switch (RegistryUtil.StoreResponseOrder)
             {
                 case ResponseOrder.Relevance:
                     baseUri.Query = "dir=asc&order=relevance&q=" + cleanedRequest;
@@ -51,25 +42,12 @@ namespace Rehau.Sku.Assist
                 case ResponseOrder.Series:
                     baseUri.Query = "dir=asc&order=sch_product_series&q=" + cleanedRequest;
                     break;
-                case ResponseOrder.NoSettings:
+                default:
                     baseUri.Query = "q=" + cleanedRequest;
                     break;
-                default:
-                    throw new ArgumentException();
             }
 
             return baseUri.Uri;
         }
-
-        private static string _CleanRequest(this string input)
-        {
-            return new StringBuilder(input)
-                .Replace("+", " plus ")
-                .Replace("РХ", "")
-                .Replace("º", " ")
-                .Replace(".", " ")
-                .Replace("Ø", " ")
-                .ToString();
-        }
     }
 }
\ No newline at end of file
diff --git a/Source/Assistant/IProduct.cs b/Source/Assistant/IProduct.cs
index d5db286..9494eeb 100644
--- a/Source/Assistant/IProduct.cs
+++ b/Source/Assistant/IProduct.cs
@@ -1,4 +1,4 @@
-namespace Rehau.Sku.Assist
+namespace RehauSku.Assistant
 {
     interface IProduct
     {
diff --git a/Source/Assistant/ParseUtil.cs b/Source/Assistant/ParseUtil.cs
new file mode 100644
index 0000000..a93c658
--- /dev/null
+++ b/Source/Assistant/ParseUtil.cs
@@ -0,0 +1,44 @@
+using AngleSharp;
+using AngleSharp.Dom;
+using Newtonsoft.Json;
+using System.Linq;
+using System.Threading.Tasks;
+
+namespace RehauSku.Assistant
+{
+    static class ParseUtil
+    {
+        public async static Task<IDocument> ContentToDocAsync(string content)
+        {
+            IConfiguration config = Configuration.Default;
+            IBrowsingContext context = BrowsingContext.New(config);
+
+            return await context.OpenAsync(req => req.Content(content));
+        }
+
+        public static IProduct GetProduct(IDocument document)
+        {
+            string script = document
+                .Scripts
+                .Where(s => s.InnerHtml.Contains("dataLayer"))
+                .FirstOrDefault()
+                .InnerHtml;
+
+            string json = script
+                .Substring(script.IndexOf("push(") + 5)
+                .TrimEnd(new[] { ')', ';', '\n', ' ' });
+
+            if (!json.Contains("impressions"))
+                return null;
+
+            StoreResponce storeResponse = JsonConvert.DeserializeObject<StoreResponce>(json);
+            IProduct product = storeResponse
+                .Ecommerce
+                .Impressions
+                .Where(p => p.Id.IsRehauSku())
+                .FirstOrDefault();
+
+            return product;
+        }
+    }
+}
\ No newline at end of file
diff --git a/Source/Assistant/RequestModifier.cs b/Source/Assistant/RequestModifier.cs
new file mode 100644
index 0000000..9f42e71
--- /dev/null
+++ b/Source/Assistant/RequestModifier.cs
@@ -0,0 +1,67 @@
+using System.Collections.Generic;
+using System.Linq;
+using System.Text;
+using System.Text.RegularExpressions;
+
+namespace RehauSku.Assistant
+{
+    public static class RequestModifier
+    {
+        public static string CleanRequest(this string input)
+        {
+            string replace = new StringBuilder(input)
+                .Replace("+", " plus ")
+                .Replace("РХ", "")
+                .Replace("º", " ")
+                .Replace(".", " ")
+                .Replace("Ø", " ")
+                .ToString();
+
+            return replace._tPieceNormalize();
+        }
+
+        private static string _tPieceNormalize(this string line)
+        {
+            Regex regex = new Regex(@"\d{2}.\d{2}.\d{2}");
+
+            if (!regex.IsMatch(line))
+                return line;
+
+            string match = regex.Match(line).Value;
+
+            int side = int.Parse($"{match[3]}{match[4]}");
+            int[] endFaces = new int[]
+            {
+                int.Parse($"{match[0]}{match[1]}"),
+                int.Parse($"{match[6]}{match[7]}")
+            };
+
+            if (new[] { endFaces[0], endFaces[1], side }.Any(x => x == 45 || x == 90 || x == 87))
+                return line;
+
+            List<string> additions = new List<string>();
+
+            if (endFaces.All(x => x < side))
+                additions.Add("увеличенный боковой");
+
+            else
+            {
+                if (new[] { endFaces[0], endFaces[1], side }.Distinct().Count() == 1)
+                    additions.Add("равнопроходной");
+                else
+                    additions.Add("уменьшенный");
+
+                if (endFaces.Any(x => x > side))
+                    additions.Add("боковой");
+
+                if (endFaces[0] != endFaces[1])
+                    additions.Add("торцевой");
+            }
+
+            string piece = $" {endFaces.Max()}-{side}-{endFaces.Min()} ";
+            string modifiedMatch = string.Join(" ", additions) + piece;
+
+            return line.Replace(match, modifiedMatch);
+        }
+    }
+}
\ No newline at end of file
diff --git a/Source/Assistant/SkuAssist.cs b/Source/Assistant/SkuAssist.cs
index e8ce789..6c68288 100644
--- a/Source/Assistant/SkuAssist.cs
+++ b/Source/Assistant/SkuAssist.cs
@@ -1,24 +1,7 @@
-using AngleSharp.Dom;
-using ExcelDna.Integration;
-using Newtonsoft.Json;
-using System;
-using System.Globalization;
-using System.Linq;
-using System.Runtime.Caching;
-using System.Text.RegularExpressions;
-using System.Threading.Tasks;
+using System.Threading.Tasks;
 
-namespace Rehau.Sku.Assist
+namespace RehauSku.Assistant
 {
-    public enum ResponseOrder
-    {
-        NoSettings,
-        Relevance,
-        Name,
-        Price,
-        Series
-    }
-
     public enum ProductField
     {
         Name,
@@ -28,79 +11,12 @@ namespace Rehau.Sku.Assist
 
     static class SkuAssist
     {
-        public static async Task<IProduct> GetProduct(string request)
+        public static async Task<IProduct> GetProductAsync(string request)
         {
-            Uri uri = request.ConvertToUri(ResponseOrder.NoSettings);
-
-            Task<string> contentTask = Task.Run(() => HttpClientUtil.GetContentByUriAsync(uri));
-            Task<IDocument> documentTask = await contentTask.ContinueWith(content => HttpClientUtil.ContentToDocAsync(content));
-
-            return GetProduct(documentTask.Result);
-        }
-
-        public static IProduct GetProduct(IDocument d)
-        {
-            string script = d.Scripts
-                   .Where(s => s.InnerHtml.Contains("dataLayer"))
-                   .First()
-                   .InnerHtml;
-
-            string json = script
-                .Substring(script.IndexOf("push(") + 5)
-                .TrimEnd(new[] { ')', ';', '\n', ' ' });
-
-            if (!json.Contains("impressions"))
-                return null;
-
-            StoreResponce storeResponse = JsonConvert.DeserializeObject<StoreResponce>(json);
-            IProduct product = storeResponse
-                .Ecommerce
-                .Impressions
-                .Where(p => Regex.IsMatch(p.Id, @"\d{11}", RegexOptions.None))
-                .FirstOrDefault();
-
-            return product;
-        }
-
-        public static object GetProduct(string request, ProductField field)
-        {
-            IProduct product;
-
-            if (MemoryCache.Default.Contains(request))
-            {
-                product = MemoryCache.Default[request] as IProduct;
-            }
-
-            else
-            {
-                object result = ExcelAsyncUtil.Run("RauName", new[] { request },
-                    delegate
-                    {
-                        Task<IProduct> p = Task.Run(() => GetProduct(request));
-                        return p.Result;
-                    });
-
-                if (result == null)
-                    return "Не найдено :(";
-
-                if (result.Equals(ExcelError.ExcelErrorNA))
-                    return "Загрузка...";
-
-                product = result as IProduct;
-                MemoryCache.Default.Add(request, product, DateTime.Now.AddMinutes(10));
-            }
+            var content = await HttpClientUtil.GetContentByRequest(request);
+            var document = await ParseUtil.ContentToDocAsync(content);
 
-            switch (field)
-            {
-                case ProductField.Name:
-                    return product.Name;
-                case ProductField.Id:
-                    return product.Id;
-                case ProductField.Price:
-                    return double.Parse((string)product.Price, CultureInfo.InvariantCulture);
-                default:
-                    return ExcelError.ExcelErrorValue;
-            }
+            return ParseUtil.GetProduct(document);
         }
     }
 }
\ No newline at end of file
diff --git a/Source/Assistant/SkuExtensions.cs b/Source/Assistant/SkuExtensions.cs
new file mode 100644
index 0000000..e39807b
--- /dev/null
+++ b/Source/Assistant/SkuExtensions.cs
@@ -0,0 +1,12 @@
+using System.Text.RegularExpressions;
+
+namespace RehauSku.Assistant
+{
+    static class SkuExtensions
+    {
+        public static bool IsRehauSku(this string line)
+        {
+            return Regex.IsMatch(line, @"^[1]\d{6}[1]\d{3}$");
+        }
+    }
+}
\ No newline at end of file
diff --git a/Source/Assistant/StoreResponse.cs b/Source/Assistant/StoreResponse.cs
index 78fe846..8e1759d 100644
--- a/Source/Assistant/StoreResponse.cs
+++ b/Source/Assistant/StoreResponse.cs
@@ -1,6 +1,6 @@
 using System.Collections.Generic;
 
-namespace Rehau.Sku.Assist
+namespace RehauSku.Assistant
 {
     public class StoreResponce
     {
diff --git a/Source/ExcelDNA/Functions.cs b/Source/ExcelDNA/Functions.cs
deleted file mode 100644
index cd09535..0000000
--- a/Source/ExcelDNA/Functions.cs
+++ /dev/null
@@ -1,19 +0,0 @@
-using ExcelDna.Integration;
-
-namespace Rehau.Sku.Assist
-{
-    public class Functions
-    {
-        [ExcelFunction]
-        public static object RAUNAME(string request)
-            => SkuAssist.GetProduct(request, ProductField.Name);
-
-        [ExcelFunction]
-        public static object RAUSKU(string request)
-            => SkuAssist.GetProduct(request, ProductField.Id);
-
-        [ExcelFunction]
-        public static object RAUPRICE(string request)
-            => SkuAssist.GetProduct(request, ProductField.Price);
-    }
-}
\ No newline at end of file
diff --git a/Source/Forms/Dialog.cs b/Source/Forms/Dialog.cs
new file mode 100644
index 0000000..170cc81
--- /dev/null
+++ b/Source/Forms/Dialog.cs
@@ -0,0 +1,46 @@
+using System.Collections.Generic;
+using System.Windows.Forms;
+
+namespace RehauSku.Forms
+{
+    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 filePath;
+        }
+
+        public static string[] GetMultiplyFiles()
+        {
+            List<string> fileNames = new List<string>();
+
+            using (OpenFileDialog dialog = new OpenFileDialog())
+            {
+                dialog.Filter = "Файлы Excel (*.xls;*.xlsx;*.xlsm)|*.xls;*.xlsx;*.xlsm";
+                dialog.Multiselect = true;
+
+                if (dialog.ShowDialog() == DialogResult.OK)
+                {
+                    foreach (string file in dialog.FileNames)
+                    {
+                        fileNames.Add(file);
+                    }
+                }
+            }
+
+            return fileNames.ToArray();
+        }
+    }
+}
diff --git a/Source/Forms/SettingsForm.Designer.cs b/Source/Forms/SettingsForm.Designer.cs
new file mode 100644
index 0000000..669406e
--- /dev/null
+++ b/Source/Forms/SettingsForm.Designer.cs
@@ -0,0 +1,46 @@
+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/Source/Forms/SettingsForm.cs b/Source/Forms/SettingsForm.cs
new file mode 100644
index 0000000..4dffadb
--- /dev/null
+++ b/Source/Forms/SettingsForm.cs
@@ -0,0 +1,21 @@
+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/Source/Forms/SettingsForm.resx b/Source/Forms/SettingsForm.resx
new file mode 100644
index 0000000..1af7de1
--- /dev/null
+++ b/Source/Forms/SettingsForm.resx
@@ -0,0 +1,120 @@
+<?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/Source/PriceListTools/ExportTool.cs b/Source/PriceListTools/ExportTool.cs
new file mode 100644
index 0000000..0a28bf3
--- /dev/null
+++ b/Source/PriceListTools/ExportTool.cs
@@ -0,0 +1,115 @@
+using ExcelDna.Integration;
+using Microsoft.Office.Interop.Excel;
+using RehauSku.Assistant;
+using System;
+using System.Collections.Generic;
+
+namespace RehauSku.PriceListTools
+{
+    class ExportTool : IDisposable
+    {
+        private Application ExcelApp;
+        private Dictionary<string, double> SkuAmount { get; set; }
+        private Range Selection { get; set; }
+
+        public ExportTool()
+        {
+            this.ExcelApp = (Application)ExcelDnaUtil.Application;
+            Selection = ExcelApp.Selection;
+
+            if (IsRangeValid())
+                _FillSkuAmountDict();
+        }
+
+        public bool IsRangeValid()
+        {
+            return Selection != null &&
+                Selection.Columns.Count == 2;
+        }
+
+        private void _FillSkuAmountDict()
+        {
+            object[,] cells = Selection.Value2;
+            SkuAmount = new Dictionary<string, double>();
+            int rowsCount = Selection.Rows.Count;
+
+            for (int row = 1; row <= rowsCount; row++)
+            {
+                if (cells[row, 1] == null || cells[row, 2] == null)
+                    continue;
+
+                string sku = null;
+                double? amount = null;
+
+                for (int column = 1; column <= 2; column++)
+                {
+                    object current = cells[row, column];
+
+                    if (current.ToString().IsRehauSku())
+                    {
+                        sku = current.ToString();
+                    }
+
+                    else if (current.GetType() == typeof(string)
+                        && double.TryParse(current.ToString(), out _))
+                    {
+                        amount = double.Parse((string)current);
+                    }
+
+                    else if (current.GetType() == typeof(double))
+                    {
+                        amount = (double)current;
+                    }
+                }
+
+                if (sku == null || amount == null)
+                    continue;
+
+                if (SkuAmount.ContainsKey(sku))
+                    SkuAmount[sku] += amount.Value;
+                else
+                    SkuAmount.Add(sku, amount.Value);
+            }
+        }
+
+        public void ExportToNewFile()
+        {
+            if (SkuAmount.Count < 1)
+            {
+                return;
+            }
+
+            string exportFile = PriceListUtil.CreateNewExportFile();
+            Workbook wb = ExcelApp.Workbooks.Open(exportFile);
+
+            try
+            {
+                PriceList priceList = new PriceList(wb);
+                priceList.Fill(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();
+            }
+        }
+
+        public void Dispose()
+        {
+            Dispose(true);
+            GC.SuppressFinalize(this);
+        }
+
+        protected virtual void Dispose(bool disposing)
+        {
+
+        }
+    }
+}
+
diff --git a/Source/PriceListTools/MergeTool.cs b/Source/PriceListTools/MergeTool.cs
new file mode 100644
index 0000000..6b0644d
--- /dev/null
+++ b/Source/PriceListTools/MergeTool.cs
@@ -0,0 +1,88 @@
+using ExcelDna.Integration;
+using Microsoft.Office.Interop.Excel;
+using System;
+using System.Collections.Generic;
+
+namespace RehauSku.PriceListTools
+{
+    class MergeTool : IDisposable
+    {
+        private Application ExcelApp;
+        private Dictionary<string, double> SkuAmount { get; set; }
+
+        public MergeTool()
+        {
+            this.ExcelApp = (Application)ExcelDnaUtil.Application;
+            this.SkuAmount = new Dictionary<string, double>();
+        }
+
+        public void AddSkuAmountToDict(string[] files)
+        {
+            ExcelApp.ScreenUpdating = false;
+            foreach (string file in files)
+            {
+                Workbook wb = ExcelApp.Workbooks.Open(file);
+
+                try
+                {
+                    PriceList priceList = new PriceList(wb);
+                    SkuAmount.AddValues(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
+                {
+                    wb.Close();
+                }
+            }
+            ExcelApp.ScreenUpdating = true;
+        }
+
+        public void ExportToNewFile(string exportFile)
+        {
+            if (SkuAmount.Count < 1)
+            {
+                return;
+            }
+
+            Workbook wb = ExcelApp.Workbooks.Open(exportFile);
+            PriceList priceList;
+
+            try
+            {
+                priceList = new PriceList(wb);
+                priceList.Fill(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();
+            }
+        }
+
+        public void Dispose()
+        {
+            Dispose(true);
+            GC.SuppressFinalize(this);
+        }
+
+        protected virtual void Dispose(bool disposing)
+        {
+
+        }
+    }
+}
diff --git a/Source/PriceListTools/PriceList.cs b/Source/PriceListTools/PriceList.cs
new file mode 100644
index 0000000..1460c07
--- /dev/null
+++ b/Source/PriceListTools/PriceList.cs
@@ -0,0 +1,87 @@
+using Microsoft.Office.Interop.Excel;
+using System.Collections.Generic;
+
+namespace RehauSku.PriceListTools
+{
+    class PriceList
+    {
+        public readonly Workbook Workbook;
+        public readonly PriceListSheet OfferSheet;
+        public readonly PriceListSheet ActiveSheet;
+
+        private const string _amountHeader = "Кол-во";
+        private const string _skuHeader = "Актуальный материал";
+        private const string _offerSheetHeader = "КП";
+
+        public PriceList(Workbook workbook)
+        {
+            Workbook = workbook;
+            OfferSheet = new PriceListSheet(workbook.Sheets[_offerSheetHeader]);
+
+            Worksheet active = workbook.ActiveSheet;
+
+            if (active.Name == _offerSheetHeader)
+                ActiveSheet = OfferSheet;
+
+            else
+                ActiveSheet = new PriceListSheet(active);                
+        }
+
+        public bool IsValid()
+        {
+            return OfferSheet.IsValid() &&
+                ActiveSheet.IsValid();
+        }
+
+        public void Fill(Dictionary<string, double> values)
+        {
+            Worksheet ws = OfferSheet.sheet;
+            ws.Activate();
+
+            int amountColumn = OfferSheet.amountColumn.Value;
+            int skuColumn = OfferSheet.skuColumn.Value;
+
+            foreach (KeyValuePair<string, double> kvp in values)
+            {
+                Range cell = ws.Columns[skuColumn].Find(kvp.Key);
+                ws.Cells[cell.Row, amountColumn].Value = kvp.Value;
+            }
+
+            AutoFilter filter = ws.AutoFilter;
+            int firstFilterColumn = filter.Range.Column;
+
+            filter.Range.AutoFilter(amountColumn - firstFilterColumn + 1, "<>");
+            ws.Range["A1"].Activate();
+        }
+
+        public class PriceListSheet
+        {
+            public readonly Worksheet sheet;
+            public readonly int? headerRow;
+            public readonly int? amountColumn;
+            public readonly int? skuColumn;
+            public object[,] amountCells;
+            public object[,] skuCells;
+
+            public PriceListSheet(Worksheet sheet)
+            {
+                this.sheet = sheet;
+                headerRow = sheet.Cells.Find(_amountHeader).Row;
+                amountColumn = sheet.Cells.Find(_amountHeader).Column;
+                skuColumn = sheet.Cells.Find(_skuHeader).Column;
+
+                amountCells = sheet.Columns[amountColumn].Value2;
+                skuCells = sheet.Columns[skuColumn].Value2;
+            }
+
+            public bool IsValid()
+            {
+                return sheet != null &&
+                    headerRow != null &&
+                    amountColumn != null &&
+                    skuColumn != null;
+            }
+        }
+    }
+}
+
diff --git a/Source/PriceListTools/PriceListUtil.cs b/Source/PriceListTools/PriceListUtil.cs
new file mode 100644
index 0000000..14797d9
--- /dev/null
+++ b/Source/PriceListTools/PriceListUtil.cs
@@ -0,0 +1,41 @@
+using System.Collections.Generic;
+using System.IO;
+
+namespace RehauSku.PriceListTools
+{
+    static class PriceListUtil
+    {
+        public static string CreateNewExportFile()
+        {
+            string fileExtension = Path.GetExtension(RegistryUtil.PriceListPath);
+            string path = Path.GetTempFileName() + fileExtension;
+
+            File.Copy(RegistryUtil.PriceListPath, path);
+            return path;
+        }
+
+        public static void AddValues(this Dictionary<string, double> SkuAmount, PriceList priceList)
+        {
+            object[,] amountCells = priceList.ActiveSheet.amountCells;
+            object[,] skuCells = priceList.ActiveSheet.skuCells;
+
+            for (int row = priceList.ActiveSheet.headerRow.Value + 1; row < amountCells.GetLength(0); row++)
+            {
+                object amount = amountCells[row, 1];
+                object sku = skuCells[row, 1];
+
+                if (amount != null && (double)amount != 0)
+                {
+                    if (SkuAmount.ContainsKey(sku.ToString()))
+                    {
+                        SkuAmount[sku.ToString()] += (double)amount;
+                    }
+
+                    else
+                        SkuAmount.Add(sku.ToString(), (double)amount);
+                }
+            }
+        }
+    }
+}
+
diff --git a/Source/Ribbon/RibbonController.cs b/Source/Ribbon/RibbonController.cs
new file mode 100644
index 0000000..df6f327
--- /dev/null
+++ b/Source/Ribbon/RibbonController.cs
@@ -0,0 +1,69 @@
+using System.Runtime.InteropServices;
+using System.Windows.Forms;
+using ExcelDna.Integration.CustomUI;
+using RehauSku.PriceListTools;
+using RehauSku.Forms;
+
+namespace RehauSku.Ribbon
+{
+    [ComVisible(true)]
+    public class RibbonController : ExcelRibbon
+    {
+        public override string GetCustomUI(string RibbonID)
+        {
+            return @"
+      <customUI 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='mergeFiles' label='Объединить' size='normal' imageMso='Copy' onAction='OnMergePressed'/>                
+            </group>
+            <group id='rausettings' label='Настройки'>
+                <button id='setPriceList' label='Файл прайс-листа' size='normal' imageMso='CurrentViewSettings' onAction='OnSetPricePressed'/>
+            </group>
+          </tab>
+        </tabs>
+      </ribbon>
+    </customUI>";
+        }
+
+        public void OnMergePressed(IRibbonControl control)
+        {
+            using (MergeTool mergeTool = new MergeTool())
+            {
+                string[] files = Dialog.GetMultiplyFiles();
+                mergeTool.AddSkuAmountToDict(files);
+                string exportFile = PriceListUtil.CreateNewExportFile();
+                mergeTool.ExportToNewFile(exportFile);
+            }
+        }
+
+        public void OnExportPressed(IRibbonControl control)
+        {
+            using (ExportTool exportTool = new ExportTool())
+            {
+                if (!exportTool.IsRangeValid())
+                {
+                    MessageBox.Show("Выделен неверный диапазон!",
+                        "Неверный диапазон",
+                        MessageBoxButtons.OK,
+                        MessageBoxIcon.Information);
+                    return;
+                }
+
+                else
+                {
+                    exportTool.ExportToNewFile();
+                }
+            }
+        }
+
+        public void OnSetPricePressed(IRibbonControl control)
+        {
+            string path = Dialog.GetFilePath();
+            RegistryUtil.PriceListPath = path;
+        }
+    }
+}