src/PriceListTools/PriceList.cs


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
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;
            }
        }
    }
}