/
PivotFieldGroupingActions.cs
113 lines (99 loc) · 5.01 KB
/
PivotFieldGroupingActions.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
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
using DevExpress.Spreadsheet;
using System.Collections.Generic;
namespace SpreadsheetDocServerPivotAPI
{
class PivotFieldGroupingActions
{
static void GroupFieldItems(IWorkbook workbook)
{
#region #GroupFieldItems
Worksheet worksheet = workbook.Worksheets["Report11"];
workbook.Worksheets.ActiveWorksheet = worksheet;
// Access the pivot table by its name in the collection.
PivotTable pivotTable = worksheet.PivotTables["PivotTable1"];
// Access the "State" field by its name in the collection.
PivotField field = pivotTable.Fields["State"];
// Add the "State" field to the column axis area.
pivotTable.ColumnFields.Add(field);
// Group the first three items in the field.
IEnumerable<int> items = new List<int>() { 0, 1, 2 };
field.GroupItems(items);
// Access the created grouped field by its index in the field collection.
int groupedFieldIndex = pivotTable.Fields.Count - 1;
PivotField groupedField = pivotTable.Fields[groupedFieldIndex];
// Set the grouped item caption to "West".
groupedField.Items[0].Caption = "West";
#endregion #GroupFieldItems
}
static void GroupFieldByDates(IWorkbook workbook)
{
#region #GroupFieldByDates
Worksheet worksheet = workbook.Worksheets["Report8"];
workbook.Worksheets.ActiveWorksheet = worksheet;
// Access the pivot table by its name in the collection.
PivotTable pivotTable = worksheet.PivotTables["PivotTable1"];
// Access the "DATE" field by its name in the collection.
PivotField field = pivotTable.Fields["DATE"];
// Group field items by quarters and months.
field.GroupItems(PivotFieldGroupByType.Quarters | PivotFieldGroupByType.Months);
#endregion #GroupFieldByDates
}
static void GroupFieldByNumericRanges(IWorkbook workbook)
{
#region #GroupFieldByNumericRanges
Worksheet worksheet = workbook.Worksheets["Report12"];
workbook.Worksheets.ActiveWorksheet = worksheet;
// Access the pivot table by its name in the collection.
PivotTable pivotTable = worksheet.PivotTables["PivotTable1"];
// Access the "Usual Hours Worked" field by its name in the collection.
PivotField field = pivotTable.Fields["Sales"];
// Group field items from 1000 to 4000 by 1000.
field.GroupItems(1000, 4000, 1000, PivotFieldGroupByType.NumericRanges);
#endregion #GroupFieldByNumericRanges
}
static void UngroupSpecificItem(IWorkbook workbook)
{
#region #UngroupSpecificItem
Worksheet worksheet = workbook.Worksheets["Report11"];
workbook.Worksheets.ActiveWorksheet = worksheet;
// Access the pivot table by its name in the collection
PivotTable pivotTable = worksheet.PivotTables["PivotTable1"];
// Access the "State" field by its name in the collection.
PivotField field = pivotTable.Fields["State"];
// Add the "State" field to the column axis area.
pivotTable.ColumnFields.Add(field);
// Group the first three items in the field.
IEnumerable<int> items = new List<int>() { 0, 1, 2 };
field.GroupItems(items);
// Access the created grouped field by its index in the field collection.
int groupedFieldIndex = pivotTable.Fields.Count - 1;
PivotField groupedField = pivotTable.Fields[groupedFieldIndex];
// Set the grouped item caption to "West".
groupedField.Items[0].Caption = "West";
// Group the remaining field items.
items = new List<int>() { 3, 4, 5 };
field.GroupItems(items);
// Set the grouped item caption to "Midwest"
groupedField.Items[1].Caption = "Midwest";
// Ungroup the "West" item.
items = new List<int> { 0 };
groupedField.UngroupItems(items);
#endregion #UngroupSpecificItem
}
static void UngroupFieldItems(IWorkbook workbook)
{
#region #UngroupFieldItems
Worksheet worksheet = workbook.Worksheets["Report8"];
workbook.Worksheets.ActiveWorksheet = worksheet;
// Access the pivot table by its name in the collection.
PivotTable pivotTable = worksheet.PivotTables["PivotTable1"];
// Access the "DATE" field by its name in the collection.
PivotField field = pivotTable.Fields["DATE"];
// Group field items by days.
field.GroupItems(field.GroupingInfo.DefaultStartValue, field.GroupingInfo.DefaultEndValue, 50, PivotFieldGroupByType.Days);
// Ungroup field items.
field.UngroupItems();
#endregion #UngroupFieldItems
}
}
}