spreadsheet-document-api-pivot-table-examples/VB/SpreadsheetDocServerPivotAPI/CodeExamples/PivotCalculatedFieldActions.vb at 24.2.1+ · DevExpress-Examples/spreadsheet-document-api-pivot-table-examples · GitHub

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
Imports DevExpress.Charts.Model
Imports DevExpress.Spreadsheet
Imports System

Namespace SpreadsheetDocServerPivotAPI
	Friend Class PivotCalculatedFieldActions

		Public Shared AddCalculatedFieldAction As Action(Of Workbook) = AddressOf AddCalculatedField
		Public Shared RemoveCalculatedFieldAction As Action(Of Workbook) = AddressOf RemoveCalculatedField
		Public Shared ModifyCalculatedFieldAction As Action(Of Workbook) = AddressOf ModifyCalculatedField
		Private Shared Sub AddCalculatedField(ByVal workbook As IWorkbook)
#Region "#AddCalculatedField"
			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")
			' Create a calculated field based on data in the "Sales" field.
			Dim field As PivotField = pivotTable.CalculatedFields.Add("=Sales*10%", "Sales Tax")
			' Add the calculated field to the data area and specify the custom field name.
			Dim dataField As PivotDataField = pivotTable.DataFields.Add(field, "Total Tax")
			' Specify the number format for the data field.
			dataField.NumberFormat = "_([$$-409]* #,##0.00_);_([$$-409]* (#,##0.00);_([$$-409]* "" - ""??_);_(@_)"
#End Region ' #AddCalculatedField
		End Sub

		Private Shared Sub RemoveCalculatedField(ByVal workbook As IWorkbook)
#Region "#RemoveCalculatedField"
			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")
			' Create a calculated field based on data in the "Sales" field.
			pivotTable.CalculatedFields.Add("=Sales*10%", "Sales Tax")
			' Access the calculated field by its name in the collection.
			Dim field As PivotField = pivotTable.CalculatedFields("Sales Tax")
			' Add the calculated field to the data area.
			Dim dataField As PivotDataField = pivotTable.DataFields.Add(field)
			'Remove the calculated field.
			pivotTable.CalculatedFields.RemoveAt(0)
#End Region ' #RemoveCalculatedField
		End Sub

		Private Shared Sub ModifyCalculatedField(ByVal workbook As IWorkbook)
#Region "#ModifyCalculatedField"
			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")
			' Create a calculated field based on data in the "Sales" field.
			pivotTable.CalculatedFields.Add("=Sales*10%", "Sales Tax Rate 10")
			' Access the calculated field by its name in the collection.
			Dim field As PivotField = pivotTable.CalculatedFields("Sales Tax Rate 10")
			'Change the formula for the calculated field.
			field.Formula = "=Sales*15%"
			'Change the calculated field name.
			field.Name = "Sales Tax Rate 15"
			'Add the calculated field to the data area and specify the custom field name.
			Dim dataField As PivotDataField = pivotTable.DataFields.Add(field, "Total Tax")
			' Specify the number format for the data field.
			dataField.NumberFormat = "_([$$-409]* #,##0.00_);_([$$-409]* (#,##0.00);_([$$-409]* "" - ""??_);_(@_)"
#End Region ' #ModifyCalculatedField
		End Sub
	End Class
End Namespace