Pivot table and chart(VB.Net)

 
top
Excel table

create an Excel table.

Dim tableDataInCSV As String
Dim workBook As New WorkBook
Dim model As BookPivotRangeModel
Dim table As Table

workBook.read("PivotTable.xls")
model = workBook.getPivotModel()

tableDataInCSV = "Sales Person\tRegion\tSales Amount\t% Commission\n" +
"Joe\tNorth\t260\t10%\n" +
"Robert\tSouth\t660\t15%\n" +
"Michelle\tEast\t940\t15%\n" +
"Erich\tWest\t410\t12%\n" +
"Dafna\tNorth\t800\t15%\n" +
"Rob\tSouth\t900\t15%"

'import table data
workBook.setCSVString(tableDataInCSV)

'add table to range A1:D7
table = workBook.addTable("DeptSales", 0, 0, 6, 3, TableBuiltInStyles.TableStyleMedium2)
'banded row
table.RowStripes = True
'enable total row
table.setTotalRow(True, "Total")

'add new column(with structured references formula) to the table
table.addCalculatedColumn("Commission Amount", "DeptSales[[#This Row],[Sales Amount]]*DeptSales[[#This Row],[% Commission]]")
'set the column's total func(1-average 2-count 3-countNums 4-max 5-min 6-sum 7-stdDev 8-var)
table.setColumnTotalsFunc("Commission Amount", ExcelTotalsCalculation.Sum)

workBook.setText(11, 1, "Sales Total")
'using structured references formula in sheet
' #link Use structured references in Excel table formulas
' http://office.microsoft.com/en-gb/excel-help/use-structured-references-in-excel-table-formulas-HA102749547.aspx
workBook.setFormula(11, 2, "SUM(DeptSales[Sales Amount])")

Dim i As Integer
For i = 0 To 5
workBook.setColWidthAutoSize(i, True)
Next

workBook.writeXLSX("Table.xlsx")

 
top
Pivot table

create and set PivotTable Objects.

Dim workBook As New WorkBook
Dim csv As String

csv = "Who\tWeek\tWhat\tAmount\n" + "Joe\t3\tBeer\t18\n" + "Beth\t4\tFood\t17\n" + "Janet\t5\tBeer\t14\n" + "Joe\t3\tFood\t12\n" + "Joe\t4\tBeer\t19\n" + "Janet\t5\tCar\t12\n" + "Joe\t3\tFood\t19\n" + "Beth\t4\tBeer\t15\n" + "Janet\t5\tBeer\t19\n"
csv = csv + "Joe\t3\tCar\t20\n" + "Joe\t4\tBeer\t16\n" + "Beth\t5\tFood\t12\n" + "Beth\t3\tBeer\t16\n" + "Joe\t4\tFood\t17\n" + "Joe\t5\tBeer\t14\n" + "Janet\t3\tCar\t19\n" + "Joe\t4\tFood\t17\n" + "Beth\t5\tBeer\t20\n" + "Janet\t3\tFood\t18\n"
csv = csv + "Joe\t4\tBeer\t14\n" + "Joe\t5\tFood\t12\n" + "Janet\t3\tBeer\t18\n" + "Janet\t4\tCar\t17\n" + "Janet\t5\tFood\t12\n" + "Test\t4\tTester\t4\n" + "Stephen\t8\tPerson\t7"

'set to cell B1 active
workBook.setActiveCell(1, 1)
workBook.setCSVString(csv)

'this will create new sheet for pivot table
workBook.insertSheets(1, 1)

Dim model As BookPivotRangeModel
model = workBook.getPivotModel()

'set pivot table linked to range
model.setList("Sheet1!$B$2:$E$28")

'set pivot table in the top-left location Sheet2!C3
model.setLocation(1, 2, 2)

'select Sheet2
workBook.Sheet = 1

'make the pivot table active
workBook.setSelection("C3")

Dim pivotRange As BookPivotRange
pivotRange = model.getActivePivotRange()

Dim rowArea, columnArea, dataArea As BookPivotArea
rowArea = pivotRange.getArea(BookPivotRange.row)
columnArea = pivotRange.getArea(BookPivotRange.column)
dataArea = pivotRange.getArea(BookPivotRange.data)

Dim rowField, columnField, dataField As BookPivotField
rowField = pivotRange.getField("Week")
rowArea.addField(rowField)
rowField = pivotRange.getField("Who")
rowArea.addField(rowField)
columnField = pivotRange.getField("What")
columnArea.addField(columnField)
dataField = pivotRange.getField("Amount")
dataArea.addField(dataField)

'workBook.write("Pivot2.xls");
workBook.writeXLSX("Pivot2.xlsx")

 
top
Pivot table area formatting

Applied the formatting(font,fill,align etc) to specified area of the PivotTable.

Dim pivotRange As BookPivotRange
pivotRange = model.getActivePivotRange()

'...
Dim rs As RangeStyle
RangeStyle rs = workBook.getRangeStyle()
rangeStyle.FontBold = true
rangeStyle.FontItalic = true

'fill background color with green
rangeStyle.Pattern = 1
rangeStyle.PatternFG = Color.Green.ToArgb()

'0-BlankColumnHeader 2-Data 3-all 4-TopLeft
Dim selection As Integer = 0
pivotRange.setTableFormat(rs, selection)

 
top
Pivot table conditional formatting

Applied the conditional formatting(color scales,data bar,iconset etc) to specified area of the PivotTable.

Dim pivotRange As BookPivotRange
pivotRange = model.getActivePivotRange()

'...
'set the conditional formatting for the pivot table
Dim conditionFormat As ConditionFormat
conditionFormat = workBook.CreateConditionFormat()

' 3 color scales(green-yellow-red)
conditionFormat.setColorScale(3, New Color() {Color.Green, Color.Yellow, Color.Red})

'data field index in the data area
Dim datafiled As Integer = 0

'0 - Selected cells 1- All cells showing "#DATAFIELD" values 3- All cells showing "#DATAFIELD" values for "#ROW_AREA_FIELDS" and "#COL_AREA_FIELDS"
Dim conditionType As Integer = 0

pivotRange.setConditionalFormat(conditionFormat, datafiled, conditionType)

 
top
Pivot chart

Create a pivot table and then use that pivot table as the datasource to create a pivot chart..

Dim workBook As New WorkBook
Dim rowIndex As Integer = 0
workBook.read("PivotTable.xls")
Dim model As BookPivotRangeModel
model = workBook.getPivotModel()

'select the sheet and range
workBook.Sheet = 0
workBook.setSelection(0, 0, 0, 0)
Dim pivotRange As BookPivotRange
pivotRange = model.getActivePivotRange()
'fresh the pivot table
model.refreshRange(pivotRange)

Dim chart As ChartShape
chart = workBook.addChart(5, 10, 12.6, 29.5)
chart.ChartType = ChartShape.Line

'link the chart to the pivot table
chart.PivotSource = pivotRange

workBook.writeXLSX("PivotTable.xlsx")

 
top
Pivot table with external data source

Create a pivot table and then use that pivot table as the datasource to create a pivot chart..

PivotExDSSample.vb
Module PivotExDSSample

Function Query() As SXQuery
Dim strAccessConn As String
strAccessConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Northwind.mdb"
Dim myAccessConn, conn As System.Data.OleDb.OleDbConnection
myAccessConn = New OleDbConnection(strAccessConn)
myAccessConn.Open()
conn = myAccessConn
Dim st As System.Data.OleDb.OleDbCommand

st = conn.CreateCommand()
st.CommandText = "SELECT * FROM Orders"
Dim rs As System.Data.OleDb.OleDbDataReader
rs = st.ExecuteReader()
Dim rsMetaData As System.Data.DataTable
rsMetaData = rs.GetSchemaTable()
Return New SXQuery(st, rs)
End Function

Sub Main()

Dim workBook As New WorkBook
workBook.Sheet = 0
Dim model As BookPivotRangeModel
model = workBook.getPivotModel()
model.setDataQuery(Query)

workBook.setSelection(7, 5, 7, 5)
Dim pivotRange As BookPivotRange
pivotRange = model.getActivePivotRange()

model.refreshRange(pivotRange)
Dim rangeArea As RangeArea
rangeArea = pivotRange.getRangeArea()
Dim rowArea, columnArea, dataArea, pageArea As BookPivotArea
rowArea = pivotRange.getArea(BookPivotRange.row)
columnArea = pivotRange.getArea(BookPivotRange.column)
dataArea = pivotRange.getArea(BookPivotRange.data)
pageArea = pivotRange.getArea(BookPivotRange.page)

Dim rowField, columnField, dataField As BookPivotField
rowField = pivotRange.getField("OrderID")
rowArea.addField(rowField)
rowField = pivotRange.getField("CustomerID")
rowArea.addField(rowField)

columnField = pivotRange.getField("OrderDate")
columnArea.addField(columnField)

dataField = pivotRange.getField("Freight")
dataArea.addField(dataField)

'BookPivotField pageField = pivotRange.getField("ShipRegion")
'pageArea.addField(pageField);

'workBook.write("PivotTableDS.xls")
workBook.writeXLSX("PivotTableDS.xlsx")
End Sub
End Module