Aspose Cells - Pivot Table - Sum not updated

Aspose.Cells Product Family
, , ,
1

Good Morning,
I have a template Excel file with a pivot table. When I refresh it in Excel, the data are correct. However, when I refresh it in Aspose Cells, the percent column does not refresh. It is a Value Field with Summarise value field by Sum.
In the result file, the field is empty but should have 50%, by example. I tried

  • PivotTable.RefreshData
  • PivotTable.CalculateData
  • WorksheetCollection.RefreshPivotTables
  • WorksheetCollection.RefreshAll
  • Workbook.RefreshDynamicArrayFormulas
  • PivotTable.RefreshDataOnOpeningFile
  • Workbook.CalculateFormula
  • WorksheetCollection.RefreshAll
    without success

Result:
image.png (5.4 KB)
The right column should have e percentage

2

@damienrcds

Good morning!

It seems you’re experiencing an issue with the pivot table not updating correctly in Aspose.Cells, even after trying various refresh methods. Here are a few suggestions to troubleshoot and potentially resolve the issue:

  1. Ensure Data Source is Correct: Verify that the data source for the pivot table is correctly set and that it includes all necessary data.

  2. Check Pivot Table Settings: Make sure that the pivot table settings are configured correctly, especially the value field settings. Sometimes, recalculating the pivot table can help.

  3. Use PivotTable.RefreshData: Ensure that this method is called after any changes to the data source to refresh the pivot table data.

  4. Recalculate Workbook: After refreshing the pivot table, call Workbook.CalculateFormula() to ensure all formulas are recalculated.

  5. Check for Errors: Look for any potential errors in the data that might prevent the pivot table from calculating correctly.

If the issue persists, consider sharing a minimal reproducible example or the specific version of Aspose.Cells you are using, as there may be version-specific bugs or limitations.

Let me know if you need further assistance!

3

@damienrcds
We recommend you to kindly try using our latest version: Aspose.Cells for .NET 25.10.

If you still find the issue, kindly do share your complete sample (runnable) code and template Excel file to reproduce the issue on our end, we will check it soon.

4

Excel.7z (699.9 KB)

Here is the Excel file.

And the sample code I tried, with Aspose.Cells 25.10.0

            var path = @"C:\tmp\poc-source.xlsx";
            var sheet = "Graphiques";
            var pivotTableName = "Tableau croisé dynamique1";
            var result = @"C:\tmp\poc-destination.xlsx";
            
            var workbook = new Workbook(path);
            var worksheet = workbook.Worksheets.FirstOrDefault(e => e.Name == sheet);
            var pivotTable = worksheet.PivotTables.FirstOrDefault(e => e.Name == pivotTableName);
            var state = pivotTable.RefreshData();
            pivotTable.CalculateData();
            Console.WriteLine($"Pivot table {pivotTable.Name} refresh state: {state}. Date: {pivotTable.RefreshDate}");

            workbook.Worksheets.RefreshPivotTables();
            workbook.Worksheets.RefreshAll();
            
            workbook.CalculateFormula();

            workbook.Save(result);

I did not keep all the tests I did (the methods mentioned in my ticket).

5

@damienrcds
By testing on the latest version v25.10 using sample files and the following sample code, we can reproduce the issue. Pourcentage column data error after refreshing and calculating PivotTable.

var path = filePath + @"poc-source - Bare.xlsx";
var sheet = "Graphiques";
var pivotTableName = "Tableau croisé dynamique1";
var result = filePath + @"poc-destination.xlsx";

var workbook = new Workbook(path);
var worksheet = workbook.Worksheets.FirstOrDefault(e => e.Name == sheet);
var pivotTable = worksheet.PivotTables.FirstOrDefault(e => e.Name == pivotTableName);
workbook.CalculateFormula();

var state = pivotTable.RefreshData();
pivotTable.CalculateData();
Console.WriteLine($"Pivot table {pivotTable.Name} refresh state: {state}. Date: {pivotTable.RefreshDate}");

//workbook.Worksheets.RefreshPivotTables();
//workbook.Worksheets.RefreshAll();           

workbook.Save(result);

Additionally, for performance reasons, please do not perform pivot table calculations repeatedly.

We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.

Issue ID(s): CELLSNET-59229

You can obtain Paid Support Services if you need support on a priority basis, along with the direct access to our Paid Support management team.