Should RefreshDataFlag be set when pivotTable.RefreshData

Aspose.Cells Product Family
1

Should the RefreshDataFlag be set before calling RefreshData on a pivotTable as shown in the code below?
If yes, why?

The documentation does help much (PivotTable.RefreshDataFlag | Aspose.Cells for .NET API Reference) and I can’t find other info

foreach (PivotTable pt in sheet.PivotTables)
{
pt.RefreshDataFlag = true;
pt.RefreshData();
pt.RefreshDataFlag = false;
pt.CalculateData();
}

2

@mortenma,

Thanks for your query.

It is not necessary to set RefreshDataFlag option before refreshing pivot table. You can simply try:
e.g
Sample code:

 foreach (PivotTable pt in sheet.PivotTables)
{
.....
pt.RefreshData();
pt.CalculateData();
......
}

Let us know if you find any issue while refreshing or calculating pivot tables in the spreadsheet, kindly do provide complete details, sample code (runnable) and template files, we will check it soon.

3

thanks Amjad,

I’m having a few trouble points with a pivot table and I think I’m not updating the pivot table datasource correctly.

I’ve created a test project and if I ChangeDataSource as below the slicers disappears from the first pivot table/Pivot1 (see attached). Also the pivot table name/reference is not longer visible from the slicers on Pivot 2.

var workbook = new Workbook(pathToFile);
var cellsMaxDisplayRange = workbook.Worksheets["Data_Reports"].Cells.MaxDisplayRange;
var dataWorksheetRefersTo = cellsMaxDisplayRange.RefersTo;

var pivotSheets = new List<string> { "Pivot1", "Pivot2" };
foreach (var name in pivotSheets)
{
    var pivotSheet = workbook.Worksheets[name];
    foreach (var pt in pivotSheet.PivotTables)
    {
        pt.ChangeDataSource(new[] { dataWorksheetRefersTo });
        pt.RefreshData();
        pt.CalculateData();
    }
}

var newFile = Path.Combine(pathToNewFile);
workbook.Save(newFile);

The documentation I can find (Pivot Table and Source Data|Documentation) refer to a named range as a datasource.

Could you by any change correct the code block above to the correct way of updating the pivot table datasource?

Thanks in advance

Best regards Morten

4

I’ve changed the datasource to a named range and now my slicer problem seem to have been fixed:

I now:

  • select data on the datasheet and define a named data range in Excel (“MyDataRange”)
  • point to the data range (MyDataRange) as data source in the pivot table in Excel
  • delete the existing data on the datasheet (code)
  • import data to the datasheet (code)
  • re-define the data range (dataSheet.Cells.MaxDisplayRange) and name it MyDataRange (code)
  • call RefreshData and CalculateData on the pivot table

Is this the recommended way?

5

@mortenma,

Good to know that your issue is sorted out now. And, yes, your devised approach is ok using a named range when you dynamically change a pivot table’s data source.