-
Notifications
You must be signed in to change notification settings - Fork 2
/
PivotTableActions.cs
147 lines (120 loc) · 6.05 KB
/
PivotTableActions.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
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
using DevExpress.Spreadsheet;
namespace SpreadsheetDocServerPivotAPI
{
public static class PivotTableActions
{
static void CreatePivotTableFromRange(IWorkbook workbook)
{
#region #CreateFromRange
Worksheet sourceWorksheet = workbook.Worksheets["Data1"];
Worksheet worksheet = workbook.Worksheets.Add();
workbook.Worksheets.ActiveWorksheet = worksheet;
// Create a pivot table using the cell range "A1:D41" as the data source.
PivotTable pivotTable = worksheet.PivotTables.Add(sourceWorksheet["A1:D41"], worksheet["B2"]);
// Add the "Category" field to the row axis area.
pivotTable.RowFields.Add(pivotTable.Fields["Category"]);
// Add the "Product" field to the row axis area.
pivotTable.RowFields.Add(pivotTable.Fields["Product"]);
// Add the "Sales" field to the data area.
pivotTable.DataFields.Add(pivotTable.Fields["Sales"]);
#endregion #CreateFromRange
}
static void CreatePivotTableFromCache(IWorkbook workbook)
{
#region #CreateFromPivotCache
Worksheet worksheet = workbook.Worksheets.Add();
workbook.Worksheets.ActiveWorksheet = worksheet;
// Create a pivot table based on the specified PivotTable cache.
PivotCache cache = workbook.Worksheets["Report1"].PivotTables["PivotTable1"].Cache;
PivotTable pivotTable = worksheet.PivotTables.Add(cache, worksheet["B2"]);
// Add the "Category" field to the row axis area.
pivotTable.RowFields.Add(pivotTable.Fields["Category"]);
// Add the "Product" field to the row axis area.
pivotTable.RowFields.Add(pivotTable.Fields["Product"]);
// Add the "Sales" field to the data area.
pivotTable.DataFields.Add(pivotTable.Fields["Sales"]);
// Set the default style for the pivot table.
pivotTable.Style = workbook.TableStyles.DefaultPivotStyle;
#endregion #CreateFromPivotCache
}
static void RemovePivotTable(IWorkbook workbook)
{
#region #RemoveTable
Worksheet worksheet = workbook.Worksheets["Report1"];
workbook.Worksheets.ActiveWorksheet = worksheet;
// Access the pivot table by its name in the collection.
PivotTable pivotTable = worksheet.PivotTables["PivotTable1"];
// Remove the pivot table from the collection.
worksheet.PivotTables.Remove(pivotTable);
#endregion #RemoveTable
}
static void ChangePivotTableLocation(IWorkbook workbook)
{
#region #ChangeLocation
Worksheet worksheet = workbook.Worksheets["Report1"];
workbook.Worksheets.ActiveWorksheet = worksheet;
// Change the pivot table location.
worksheet.PivotTables["PivotTable1"].MoveTo(worksheet["A7"]);
// Refresh the pivot table.
worksheet.PivotTables["PivotTable1"].Cache.Refresh();
#endregion #ChangeLocation
}
static void MovePivotTableToWorksheet(IWorkbook workbook)
{
#region #MoveToWorksheet
Worksheet worksheet = workbook.Worksheets["Report1"];
// Create a new worksheet.
Worksheet targetWorksheet = workbook.Worksheets.Add();
// Access the pivot table by its name in the collection
// and move it to the new worksheet.
worksheet.PivotTables["PivotTable1"].MoveTo(targetWorksheet["B2"]);
// Refresh the pivot table.
targetWorksheet.PivotTables["PivotTable1"].Cache.Refresh();
workbook.Worksheets.ActiveWorksheet = targetWorksheet;
#endregion #MoveToWorksheet
}
static void ChangePivotTableDataSource(IWorkbook workbook)
{
#region #ChangeDataSource
Worksheet worksheet = workbook.Worksheets["Report1"];
workbook.Worksheets.ActiveWorksheet = worksheet;
// Access the pivot table by its name in the collection.
PivotTable pivotTable = worksheet.PivotTables["PivotTable1"];
Worksheet sourceWorksheet = workbook.Worksheets["Data2"];
// Change the data source of the pivot table.
pivotTable.ChangeDataSource(sourceWorksheet["A1:H6367"]);
// Add the "State" field to the row axis area.
pivotTable.RowFields.Add(pivotTable.Fields["State"]);
// Add the "Yearly Earnings" field to the data area.
PivotDataField dataField = pivotTable.DataFields.Add(pivotTable.Fields["Yearly Earnings"]);
// Calculate the average of the "Yearly Earnings" values for each state.
dataField.SummarizeValuesBy = PivotDataConsolidationFunction.Average;
#endregion #ChangeDataSource
}
static void ClearPivotTable(IWorkbook workbook)
{
#region #ClearTable
Worksheet worksheet = workbook.Worksheets["Report1"];
workbook.Worksheets.ActiveWorksheet = worksheet;
// Clear the pivot table.
worksheet.PivotTables["PivotTable1"].Clear();
#endregion #ClearTable
}
static void ChangeBehaviorOptions(IWorkbook workbook)
{
#region #ChangeBehaviorOptions
Worksheet worksheet = workbook.Worksheets["Report1"];
workbook.Worksheets.ActiveWorksheet = worksheet;
worksheet.Columns["B"].WidthInCharacters = 40;
// Access the pivot table by its name in the collection.
PivotTable pivotTable = worksheet.PivotTables["PivotTable1"];
// Restrict specific operations for the pivot table.
PivotBehaviorOptions behaviorOptions = pivotTable.Behavior;
behaviorOptions.AutoFitColumns = false;
behaviorOptions.EnableFieldList = false;
// Refresh the pivot table.
pivotTable.Cache.Refresh();
#endregion #ChangeBehaviorOptions
}
}
}