blob: 35b3f7ddc1ac627d60fca7c89eacf5b917d8af1e (
plain)
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
88
89
90
91
92
93
94
95
96
97
98
99
100
101
|
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;
int exportedValues = 0;
foreach (KeyValuePair<string, double> kvp in values)
{
Range cell = ws.Columns[skuColumn].Find(kvp.Key);
if (cell == null)
{
System.Windows.Forms.MessageBox.Show
($"Артикул {kvp.Key} отсутствует в таблице заказов {RegistryUtil.PriceListPath}",
"Отсутствует позиция в конечной таблице заказов",
System.Windows.Forms.MessageBoxButtons.OK,
System.Windows.Forms.MessageBoxIcon.Information);
}
else
{
ws.Cells[cell.Row, amountColumn].Value = kvp.Value;
exportedValues++;
}
}
AutoFilter filter = ws.AutoFilter;
int firstFilterColumn = filter.Range.Column;
filter.Range.AutoFilter(amountColumn - firstFilterColumn + 1, "<>");
ws.Range["A1"].Activate();
ws.Application.StatusBar = $"Экспортировано {exportedValues} строк из {values.Count}";
}
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;
}
}
}
}
|
