-
Notifications
You must be signed in to change notification settings - Fork 2
/
Copy pathPivotFieldActions.vb
147 lines (121 loc) · 6.15 KB
/
PivotFieldActions.vb
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
Imports DevExpress.Charts.Model
Imports DevExpress.Spreadsheet
Imports System
Namespace SpreadsheetDocServerPivotAPI
Public Module PivotFieldActions
Public AddFieldToAxisAction As Action(Of Workbook) = AddressOf AddFieldToAxis
Public InsertFieldToAxisAction As Action(Of Workbook) = AddressOf InsertFieldToAxis
Public MoveFieldToAxisAction As Action(Of Workbook) = AddressOf MoveFieldToAxis
Public MoveFieldUpAction As Action(Of Workbook) = AddressOf MoveFieldUp
Public MoveFieldDownAction As Action(Of Workbook) = AddressOf MoveFieldDown
Public RemoveFieldFromAxisAction As Action(Of Workbook) = AddressOf RemoveFieldFromAxis
Public SortFieldItemsAction As Action(Of Workbook) = AddressOf SortFieldItems
Public SortFieldItemsByDataFieldAction As Action(Of Workbook) = AddressOf SortFieldItemsByDataField
Public MultipleSubtotalsAction As Action(Of Workbook) = AddressOf MultipleSubtotals
Private Sub AddFieldToAxis(ByVal workbook As IWorkbook)
#Region "#AddToAxis"
Dim sourceWorksheet As Worksheet = workbook.Worksheets("Data1")
Dim worksheet As Worksheet = workbook.Worksheets.Add()
workbook.Worksheets.ActiveWorksheet = worksheet
' Create a pivot table.
Dim pivotTable As PivotTable = worksheet.PivotTables.Add(sourceWorksheet("A1:D41"), worksheet("B2"))
' Add the "Product" field to the row axis area.
pivotTable.RowFields.Add(pivotTable.Fields("Product"))
' Add the "Category" field to the column axis area.
pivotTable.ColumnFields.Add(pivotTable.Fields("Category"))
' Add the "Sales" field to the data area and specify the custom field name.
Dim dataField As PivotDataField = pivotTable.DataFields.Add(pivotTable.Fields("Sales"), "Sales(Sum)")
' Specify the number format for the "Sales" field.
dataField.NumberFormat = "_([$$-409]* #,##0.00_);_([$$-409]* (#,##0.00);_([$$-409]* "" - ""??_);_(@_)"
' Add the "Region" field to the filter area.
pivotTable.PageFields.Add(pivotTable.Fields("Region"))
#End Region ' #AddToAxis
End Sub
Private Sub InsertFieldToAxis(ByVal workbook As IWorkbook)
#Region "#InsertAtTop"
Dim worksheet As Worksheet = workbook.Worksheets("Report1")
workbook.Worksheets.ActiveWorksheet = worksheet
' Access the pivot table by its name in the collection.
Dim pivotTable As PivotTable = worksheet.PivotTables("PivotTable1")
' Insert the "Region" field at the top of the row axis area.
pivotTable.RowFields.Insert(0, pivotTable.Fields("Region"))
#End Region ' #InsertAtTop
End Sub
Private Sub MoveFieldToAxis(ByVal workbook As IWorkbook)
#Region "#MoveToAxis"
Dim worksheet As Worksheet = workbook.Worksheets("Report1")
workbook.Worksheets.ActiveWorksheet = worksheet
' Access the pivot table by its name in the collection.
Dim pivotTable As PivotTable = worksheet.PivotTables("PivotTable1")
' Move the "Region" field to the column axis area.
pivotTable.ColumnFields.Add(pivotTable.Fields("Region"))
#End Region ' #MoveToAxis
End Sub
Private Sub MoveFieldUp(ByVal workbook As IWorkbook)
#Region "#MoveUp"
Dim worksheet As Worksheet = workbook.Worksheets("Report3")
workbook.Worksheets.ActiveWorksheet = worksheet
' Access the pivot table by its name in the collection.
Dim pivotTable As PivotTable = worksheet.PivotTables("PivotTable1")
' Move the "Category" field one position up in the row area.
pivotTable.RowFields("Category").MoveUp()
#End Region ' #MoveUp
End Sub
Private Sub MoveFieldDown(ByVal workbook As IWorkbook)
#Region "#MoveDown"
Dim worksheet As Worksheet = workbook.Worksheets("Report3")
workbook.Worksheets.ActiveWorksheet = worksheet
' Access the pivot table by its name in the collection.
Dim pivotTable As PivotTable = worksheet.PivotTables("PivotTable1")
' Move the "Region" field one position down in the row area.
pivotTable.RowFields("Region").MoveDown()
#End Region ' #MoveDown
End Sub
Private Sub RemoveFieldFromAxis(ByVal workbook As IWorkbook)
#Region "#RemoveFromAxis"
Dim worksheet As Worksheet = workbook.Worksheets("Report1")
workbook.Worksheets.ActiveWorksheet = worksheet
' Access the pivot table by its name in the collection.
Dim pivotTable As PivotTable = worksheet.PivotTables("PivotTable1")
' Remove the "Product" field from the row axis area.
pivotTable.RowFields.Remove(pivotTable.RowFields("Product"))
#End Region ' #RemoveFromAxis
End Sub
Private Sub SortFieldItems(ByVal workbook As IWorkbook)
#Region "#SortFieldItems"
Dim worksheet As Worksheet = workbook.Worksheets("Report1")
workbook.Worksheets.ActiveWorksheet = worksheet
' Access the pivot table by its name in the collection.
Dim pivotTable As PivotTable = worksheet.PivotTables("PivotTable1")
' Access the pivot field by its name in the collection.
Dim field As PivotField = pivotTable.Fields("Product")
' Sort items in the "Product" field.
field.SortType = PivotFieldSortType.Ascending
#End Region ' #SortFieldItems
End Sub
Private Sub SortFieldItemsByDataField(ByVal workbook As IWorkbook)
#Region "#SortFieldItemsByDataField"
Dim worksheet As Worksheet = workbook.Worksheets("Report1")
workbook.Worksheets.ActiveWorksheet = worksheet
' Access the pivot table by its name in the collection.
Dim pivotTable As PivotTable = worksheet.PivotTables("PivotTable1")
' Access the pivot field by its name in the collection.
Dim field As PivotField = pivotTable.Fields("Product")
' Sort items in the "Product" field by values of the "Sum of Sales" data field.
field.SortItems(PivotFieldSortType.Ascending, 0)
#End Region ' #SortFieldItemsByDataField
End Sub
Private Sub MultipleSubtotals(ByVal workbook As IWorkbook)
#Region "#MultipleSubtotals"
Dim worksheet As Worksheet = workbook.Worksheets("Report1")
workbook.Worksheets.ActiveWorksheet = worksheet
' Access the pivot table by its name in the collection.
Dim pivotTable As PivotTable = worksheet.PivotTables("PivotTable1")
' Access the pivot field by its name in the collection.
Dim field As PivotField = pivotTable.Fields("Category")
' Display multiple subtotals for the field.
field.SetSubtotal(PivotSubtotalFunctions.Sum Or PivotSubtotalFunctions.Average)
#End Region ' #MultipleSubtotals
End Sub
End Module
End Namespace