/
PivotTableFormattingActions.vb
73 lines (59 loc) · 3.04 KB
/
PivotTableFormattingActions.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
Imports DevExpress.Spreadsheet
Namespace SpreadsheetDocServerPivotAPI
Public NotInheritable Class PivotTableFormattingActions
Private Sub New()
End Sub
Private Shared Sub ChangeStylePivotTable(ByVal workbook As IWorkbook)
' #Region "#SetStyle"
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")
' Set the pivot table style.
pivotTable.Style = workbook.TableStyles(BuiltInPivotStyleId.PivotStyleDark7)
' #End Region ' #SetStyle
End Sub
Private Shared Sub BandedColumns(ByVal workbook As IWorkbook)
' #Region "#BandedColumns"
Dim worksheet As Worksheet = workbook.Worksheets("Report4")
workbook.Worksheets.ActiveWorksheet = worksheet
' Access the pivot table by its name in the collection.
Dim pivotTable As PivotTable = worksheet.PivotTables("PivotTable1")
' Apply the banded column formatting to the pivot table.
pivotTable.BandedColumns = True
' #End Region ' #BandedColumns
End Sub
Private Shared Sub BandedRows(ByVal workbook As IWorkbook)
' #Region "#BandedRows"
Dim worksheet As Worksheet = workbook.Worksheets("Report4")
workbook.Worksheets.ActiveWorksheet = worksheet
' Access the pivot table by its name in the collection.
Dim pivotTable As PivotTable = worksheet.PivotTables("PivotTable1")
' Apply the banded row formatting to the pivot table.
pivotTable.BandedRows = True
' #End Region ' #BandedRows
End Sub
Private Shared Sub ShowColumnHeaders(ByVal workbook As IWorkbook)
' #Region "#ColumnHeaders"
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 formatting from column headers.
pivotTable.ShowColumnHeaders = False
' #End Region ' #ColumnHeaders
End Sub
Private Shared Sub ShowRowHeaders(ByVal workbook As IWorkbook)
' #Region "#RowHeaders"
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")
' Add the "Region" field to the column axis area.
pivotTable.ColumnFields.Add(pivotTable.Fields("Region"))
' Remove formatting from row headers.
pivotTable.ShowRowHeaders = False
' #End Region ' #RowHeaders
End Sub
End Class
End Namespace