-
Notifications
You must be signed in to change notification settings - Fork 2
/
PivotCalculatedItemActions.vb
60 lines (46 loc) · 2.66 KB
/
PivotCalculatedItemActions.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
Imports DevExpress.Spreadsheet
Namespace SpreadsheetDocServerPivotAPI
Friend Class PivotCalculatedItemActions
Private Shared Sub AddCalculatedItem(ByVal workbook As IWorkbook)
' #Region "#AddCalculatedItem"
Dim worksheet As Worksheet = workbook.Worksheets("Report10")
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("State")
' Add calculated items to the "State" field.
field.CalculatedItems.Add("=Arizona+California+Colorado", "West Total")
field.CalculatedItems.Add("=Illinois+Kansas+Wisconsin", "Midwest Total")
' #End Region ' #AddCalculatedItem
End Sub
Private Shared Sub RemoveCalculatedItem(ByVal workbook As IWorkbook)
' #Region "#RemoveCalculatedItem"
Dim worksheet As Worksheet = workbook.Worksheets("Report7")
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("Customer")
' Add a calculated item to the "Customer" field.
field.CalculatedItems.Add("='Big Foods'*110%", "Big Foods Sales Plan")
'Remove the calculated item by its index from the collection.
field.CalculatedItems.RemoveAt(0)
' #End Region ' #RemoveCalculatedItem
End Sub
Private Shared Sub ModifyCalculatedItem(ByVal workbook As IWorkbook)
' #Region "#ModifyCalculatedItem"
Dim worksheet As Worksheet = workbook.Worksheets("Report7")
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("Customer")
' Add a calculated item to the "Customer" field.
Dim item As PivotItem = field.CalculatedItems.Add("='Big Foods'*110%", "Big Foods Sales Plan")
'Change the formula for the calculated item.
item.Formula = "='Big Foods'*115%"
' #End Region ' #ModifyCalculatedItem
End Sub
End Class
End Namespace