Can not refresh PivotTable on a separate WorkSheet

Aspose.Cells Product Family
1

I am trying to update a pivot table on its own worksheet (Pvt) with new data that is on a separate worksheet (DataFinal). I attempt to set several options on the pivot table. When I open the excel document, none of the changes (sorted “Grouping” page list - see attachment) take affect until I go to Data -> RefreshAll. 

	WorksheetCollection worksheets = workbook.Worksheets;

Worksheet dataFinalSheet = GetDataFinalSheet(worksheets, meaningfulDifferenceDataTable);

Worksheet pivotTableSheet = workbook.Worksheets.GetSheetByCodeName(“Pvt”);

PivotTable pivotTable = pivotTableSheet.PivotTables[0];

	pivotTable.IsAutoFormat = true;

PivotFieldCollection pivotFieldCollection = pivotTable.PageFields;

for (int i = 0; i < pivotFieldCollection.Count; i++)

{

pivotFieldCollection[i].IsAutoSort = true;

pivotFieldCollection[i].IsAscendSort = true;

pivotFieldCollection[i].AutoSortField = -1;

pivotFieldCollection[i].BaseField = 1;

pivotFieldCollection[i].BaseItemPostion = PivotItemPosition.Next;

}

pivotTable.RefreshData();

workbook.Save(filePath);

What am I doing wrong and what can I do so that all changes are visible as soon as I open the excel document?

2

Hi,


Thank you for sharing your source code and snapshots of the issue. But I am afraid I am unable to replicate it on my end with some of my spreadsheets having pivot tables. It would be of great help that you also provide your template file which you are manipulating. Along with a simple console application that can re-create your said scenario.
We will provide you all assistance to rectify this issue.
3

Here you go.
You’ll need to create an export directory (C:\Export) for the excel file or set it in the app.config.

4

Hi,

Thanks for sharing us the project.

After an initial test, I am able to reproduce the issue as you have mentioned by your screen shots.
I have logged your issue with an id: CELLSNET-27296. We will figure it out soon.

Thank you.

5

Hi,


Thanks for sharing your sample project.
Please set pivotTable.RefreshDataOnOpeningFile = true before saving the workbook to disk. This will solve your issue of Pivot table refreshing. Also, please share your feedback on this.

C# Code:
PivotFieldCollection pivotFieldCollection = pivotTable.PageFields;
for (int i = 0; i < pivotFieldCollection.Count; i++)
{
pivotFieldCollection[i].IsAutoSort = true;
pivotFieldCollection[i].IsAscendSort = true;
pivotFieldCollection[i].AutoSortField = -1;
pivotFieldCollection[i].BaseField = 1;
pivotFieldCollection[i].BaseItemPostion = PivotItemPosition.Next;
}
pivotTable.RefreshData();
pivotTable.RefreshDataOnOpeningFile = true;
workbook.Save(filePath);
6

This resolved my issue.
More to come!
Thank you.