Using version 8.5.1 of Aspose.Cells for NET
Please see attached sample code.
If I import data to a ListObject using the ImportDataTable method and afterwards calls the RefreshData on the pivot table (which has it’s DataSource pointing to the ListObject), the pivot table isn’t refreshed with the imported data.
But if I afterwards open the saved workbook in Excel and refresh the pivot table manually, the pivot table is refreshed with the data.
If I break the code before the RefreshData call, it would seem that PivotTable.Datasource still “believes” that the ListObject only contains one row.
So I’m unsure if the problem is the way I import data to the ListObject (as far as I can see, this is the way your own examples work and if I output TableList.DataRange.ToString just after deleting the spare row it returns Sheet1!A2:B5 so I believe that the ListObject has been updated correctly) or if it’s bug.
Can you reproduce my problem and advise on how to overcome it?
Hi,
Thanks for your posting and using Aspose.Cells.
Please call PivotTable.CalculateData() after PivotTable.RefreshData(). It should resolve your issue.
Please also download and try the latest version: Aspose.Cells
for .NET v8.5.1.5 and see if it makes any difference in resolving this issue.
Please see the following code for your reference. Changes are highlighted in red.
VB.NET
’ Create workbook from template
Dim wrkbook As Workbook = New Workbook(“C:\Temp\PivotTable\PivotTemplate.xltx”)
’ Create test data
Dim MyData As DataTable = New DataTable(“Table1”)
MyData.Columns.Add(“Initials”, System.Type.GetType(“System.String”))
MyData.Columns.Add(“Value”, System.Type.GetType(“System.Int32”))
Dim MyRow As DataRow
MyRow = MyData.NewRow
MyRow.Item(0) = “INI1”
MyRow.Item(1) = “1”
MyData.Rows.Add(MyRow)
MyRow = MyData.NewRow
MyRow.Item(0) = “INI1”
MyRow.Item(1) = “2”
MyData.Rows.Add(MyRow)
MyRow = MyData.NewRow
MyRow.Item(0) = “INI1”
MyRow.Item(1) = “3”
MyData.Rows.Add(MyRow)
MyRow = MyData.NewRow
MyRow.Item(0) = “INI2”
MyRow.Item(1) = “20”
MyData.Rows.Add(MyRow)
’ Import testdata to ListObject
Dim TableList As Tables.ListObject
TableList = wrkbook.Worksheets(0).ListObjects(“Table1”)
Dim MyCells As Cells
MyCells = wrkbook.Worksheets(0).Cells
MyCells.ImportDataTable(MyData, False, TableList.StartRow + 1, TableList.StartColumn, True)
’ Delete spare row
MyCells.DeleteRow(TableList.EndRow)
’ Update pivottable
Dim PivotTable As Pivot.PivotTable
PivotTable = wrkbook.Worksheets(0).PivotTables.Item(0)
PivotTable.RefreshData()
PivotTable.CalculateData()
’ Save workbook
wrkbook.Save(“C:\Temp\PivotTable\Pivot.xlsx”, SaveFormat.Xlsx)
Hi Shakeel.
Thanks a lot for your quick reply.
Calling the CalculateData() method did the trick so it was just me who didn’t understand how to use the PivotTable 
Regards,
Martin
Hi,
Thanks for your feedback and using Aspose.Cells.
It is good to know that your issue is resolved with the addition of PivotTable.CalculateData(). Let us know if you encounter any other issue, we will be glad to look into it and help you further.