Export pivot table data that was just added to a worksheet

Aspose.Cells Product Family
1

I followed the example on this page:

How to create a pivottable

Then after the pivot table was created I want to export the pivot table to a datatable in vb .net, however it does not seem to exist. If the workbook is saved to the hard drive the pivot table exists.

How can I export a pivot table to a datatable after just creating the pivot table?

Basically I just want to use the library to do pivot table functions to a datatable(so I am importing the datatable into a worksheet then doing the pivot table on that data, and trying to export the resulting pivot table as a datatable)

2

Hi,


Please call PivotTable.CalculateData() before exporting to DataTable.

See the sample code below:

Sample code:

'Instantiating a Workbook object
Dim workbook As New Workbook()

'Obtaining the reference of the newly added worksheet
Dim sheet As Worksheet = workbook.Worksheets(0)

Dim cells As Cells = sheet.Cells

'Setting the value to the cells
Dim cell As Aspose.Cells.Cell = cells(“A1”)
cell.PutValue(“Sport”)
cell = cells(“B1”)
cell.PutValue(“Quarter”)
cell = cells(“C1”)
cell.PutValue(“Sales”)

cell = cells(“A2”)
cell.PutValue(“Golf”)
cell = cells(“A3”)
cell.PutValue(“Golf”)
cell = cells(“A4”)
cell.PutValue(“Tennis”)
cell = cells(“A5”)
cell.PutValue(“Tennis”)
cell = cells(“A6”)
cell.PutValue(“Tennis”)
cell = cells(“A7”)
cell.PutValue(“Tennis”)
cell = cells(“A8”)
cell.PutValue(“Golf”)

cell = cells(“B2”)
cell.PutValue(“Qtr3”)
cell = cells(“B3”)
cell.PutValue(“Qtr4”)
cell = cells(“B4”)
cell.PutValue(“Qtr3”)
cell = cells(“B5”)
cell.PutValue(“Qtr4”)
cell = cells(“B6”)
cell.PutValue(“Qtr3”)
cell = cells(“B7”)
cell.PutValue(“Qtr4”)
cell = cells(“B8”)
cell.PutValue(“Qtr3”)

cell = cells(“C2”)
cell.PutValue(1500)
cell = cells(“C3”)
cell.PutValue(2000)
cell = cells(“C4”)
cell.PutValue(600)
cell = cells(“C5”)
cell.PutValue(1500)
cell = cells(“C6”)
cell.PutValue(4070)
cell = cells(“C7”)
cell.PutValue(5000)
cell = cells(“C8”)
cell.PutValue(6430)

Dim pivotTables As Aspose.Cells.Pivot.PivotTableCollection = sheet.PivotTables

'Adding a PivotTable to the worksheet
Dim index As Integer = pivotTables.Add("=A1:C8", “E3”, “PivotTable2”)

'Accessing the instance of the newly added PivotTable
Dim pivotTable As Aspose.Cells.Pivot.PivotTable = pivotTables(index)

'Unshowing grand totals for rows.
pivotTable.RowGrand = False

'Draging the first field to the row area.
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, 0)

'Draging the second field to the column area.
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Column, 1)

'Draging the third field to the data area.
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Data, 2)

pivotTable.RefreshData()
pivotTable.CalculateData()

Dim dt As DataTable = sheet.Cells.ExportDataTable(pivotTable.TableRange1.StartRow, pivotTable.TableRange1.StartColumn, pivotTable.TableRange1.EndRow - pivotTable.TableRange1.StartRow + 1, pivotTable.TableRange1.EndColumn - pivotTable.TableRange1.StartColumn + 1)

Dim sheet1 As Worksheet = workbook.Worksheets(workbook.Worksheets.Add())

sheet1.Cells.ImportDataTable(dt,True,“A1”)



'Saving the Excel file
workbook.Save(“e:\test2\book1.xls”)
3

Awesome!

The RefreshData() and CalculateData() took care of my problem.

Thanks again.