Pivot Tables without columns or rows lose formatting

Aspose.Cells Product Family
1

We are running into an issue refreshing a Pivot table that only has a Filter (no rows, columns or values). Aspose gets rid of some of the formatting and adds extra formatting to cells we do not expect to have pivot table data in. Here is a before and after of the file along with the sample files.
image.png (1.5 MB)

Aspose Bug - Filter Only Pivot.zip (22.8 KB)

2

@sabnuq

It sounds like you’re experiencing issues with the formatting of a Pivot Table that only has a filter applied, without any rows, columns, or values. This can lead to unexpected formatting changes when refreshing the Pivot Table.

Cause: The issue may arise from the way the Pivot Table is set up, particularly when it lacks rows, columns, or values, which can affect its formatting upon refresh.

Solution: Here are a few steps you can take to troubleshoot and resolve the issue:

  1. Check for Updates: Ensure you are using the latest version of Aspose.Cells, as updates often include bug fixes and improvements related to Pivot Tables.

  2. Review Formatting Settings: After refreshing the Pivot Table, check if the formatting settings are being reset. You may need to reapply specific formatting after the refresh.

  3. Use PivotTable.Refresh() Method: Make sure you are using the correct method to refresh the Pivot Table. The Refresh() method should be called on the PivotTable object to ensure it updates correctly.

  4. Inspect the Source Data: Verify that the source data for the Pivot Table is intact and correctly formatted. Any changes in the source data can affect how the Pivot Table displays.

  5. Test with a Simple Example: Create a new, simple Pivot Table with similar settings to see if the issue persists. This can help determine if the problem is specific to your current document.

Next Steps: If the problem continues after following these steps, please share more details about your implementation, including any relevant code snippets. This will help in diagnosing the issue further.

Let me know if you need further assistance!

3
  1. Yes I am using the latest version of Aspose.Cells for this test.
  2. I was not able to find any formatting settings that are being reset.
  3. I have tested many methods of refreshing including PivotTable.Refresh() and Worksheets.RefreshAll().
  4. Source data is okay.
  5. The example I attached in the initial question is the simplified example.
4

@sabnuq,

Thanks for the sample Excel file and screenshot.

I reproduced the issue as you mentioned by using your template Excel file and following sample code. I found pivot table with filters (only) and without rows and columns loses formatting upon being refreshed.

var workbook = new Workbook("e:\\test2\\Original.xlsx"); 

foreach (var workSheet in workbook.Worksheets)
{
    workSheet.RefreshPivotTables();
    //or 
    //foreach (var pivotTable in workSheet.PivotTables)
    //{
        //pivotTable.RefreshData();
        //pivotTable.CalculateData();
    //}
}
workbook.Save("e:\\test2\\out1.xlsx");

We require thorough evaluation of the issue. 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-58848

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.

5

@sabnuq,

We are pleased to inform you that your issue (Ticket ID: “CELLSNET-58848”) has been resolved. The fix/enhancement will be included in an upcoming release (Aspose.Cells v25.8) that we plan to release either this weekend or during the next week of August 2025. You will be notified when the next version is released.

6

The issues you have found earlier (filed as CELLSNET-58848) have been fixed in this update. This message was posted using Bugs notification tool by leoluo