Pivot table refresh problems (filter, col width, localization)

Aspose.Cells Product Family
1

See attached sample: I call “Worksheet.RefreshPivotTables()”, which brings three problems:
AsposeCellsPivot.zip (26.3 KB)

Problem 1
In the source file, the filter for cell B6 is set to single select. After applying “RefreshPivotTables()”, it changes to multi select.

filter_multiselect.png916×507 37.4 KB

Problem 2
See previous screenshot: the cell B6 does not display the german text “(Alle)”, but the english version “(All)”.

Problem 3
Cell D7 contains a formula to display the current date. The col width in the resulting file is to small, so that the date is shown as “#####”. The original file has a proper col width.

Could you take a look at it and check whether these issues are bugs or have to be fixed on my side?

Workaround
I think I found a workaround on my side, but need a confirmation from you whether this really works as expected.

I can set the excel pivot table to refresh when the document is loaded (and not to store the data).

Instead of calling “RefreshPivotTables()” for each sheet, can I do this (refreshes only tables that don’t have the auto-refresh setting enabled)? We have to refresh some tables, as the call is deep in code that writes sql query results to excel templates. Some templates might contain pivot tables, others don’t. So this code has to work for all kinds of excel templates.

foreach (Worksheet sheet = workbook.Worksheets)
{
  foreach (PivotTable piv in sheet.PivotTables)
  {
    if (piv.RefreshDataOnOpeningFile == false)
    {
      piv.RefreshData();
      piv.CalculateData();
    }
  }
}

Question 1: is “PivotTable.RefreshDataOnOpeningFile” the setting “Aktualisieren beim Öffnen der Datei” of my screenshot?
Question 2: in my tests, I found that I need to first call “RefreshData”, then “CalculateDate”. Is this true? It should be the same sequence of actions that “Worksheet.RefreshPivotTables” does?

Best regards

Wolfgang

2

@wknauf

Hello Wolfgang,

Thank you for your detailed inquiry regarding the issues you’re facing with pivot table refresh in Aspose.Cells. Let’s address your concerns one by one.

Problem 1: Filter Changes to Multi-Select

The issue where the filter for cell B6 changes from single select to multi-select after calling RefreshPivotTables() could be related to how the pivot table is being refreshed. It’s important to ensure that the pivot table settings are correctly configured before the refresh operation. If this behavior persists, it may be worth checking if there are any updates or patches available for Aspose.Cells that address this specific issue.

Problem 2: Localization of Filter Text

Regarding the localization issue where the filter text does not display in German, this could be due to the default language settings of the workbook or the environment in which the workbook is being processed. Ensure that the locale settings are correctly set to German before performing the refresh operation. If the problem continues, it might be beneficial to report this as a potential bug.

Problem 3: Column Width Issue

The column width issue where the date is displayed as “#####” indicates that the column is not wide enough to display the content. You can manually adjust the column width after refreshing the pivot tables. Here’s a code snippet to set the column width:

sheet.AutoFitColumn(columnIndex); // Replace columnIndex with the actual index of the column

Workaround Confirmation

Your proposed workaround of checking the RefreshDataOnOpeningFile property is a valid approach. This property corresponds to the setting “Aktualisieren beim Öffnen der Datei” in your screenshot.

Sequence of Refreshing Data

Regarding your question about the sequence of calling RefreshData() followed by CalculateData(), you are correct. This sequence is necessary to ensure that the data is refreshed before any calculations are performed. This is consistent with how Worksheet.RefreshPivotTables() operates.

Here’s a refined version of your code snippet for clarity:

foreach (Worksheet sheet in workbook.Worksheets)
{
    foreach (PivotTable piv in sheet.PivotTables)
    {
        if (!piv.RefreshDataOnOpeningFile)
        {
            piv.RefreshData();
            piv.CalculateData();
        }
    }
}

This approach will ensure that only the pivot tables that do not have the auto-refresh setting enabled are processed.

If you continue to experience these issues, please consider reaching out to Aspose support for further assistance.

Best regards!

3

@wknauf

The ‘PivotTable.RefreshDataOnOpeningFile’ attribute is used to control whether Excel refreshes the pivot table when opening the sample file.

4

@wknauf

If you refresh a single pivot table, you need to call the RefreshData method first, and then call the CalculateData method. Of course, you can also directly call the Worksheet.RefreshPivotTables method, which will refresh and calculate all pivot tables in the worksheet.

5

@wknauf
By testing on the latest version v25.5 using sample file and code, we were able to reproduce the three issues mentioned. Both problem 1 and problem 2 belong to the filters. Filters are changed after refreshing pivot tables. Problem 3 is the column width decrease 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-58443: Filters are changed after refreshing pivot tables
CELLSNET-58444: The decrease in column width causes the date to be shown as "#####" after refreshing pivot table

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.

6

@wknauf
Regarding question 2, you can refer to the following sample code to customize globalization settings for PivotTable. result.zip (22.7 KB)

Workbook workbook = new Workbook("..\\..\\..\\sample.xlsx");
workbook.Settings.GlobalizationSettings.PivotSettings = new CustomPivotTableGlobalizationSettings();
//Modify all rows on first sheet to show a different result:
Worksheet sheetData = workbook.Worksheets[0];

int maxDataRow = sheetData.Cells.MaxDataRow;
for (int row = 1; row <= maxDataRow; row++)
{
//Column 4 contains last names:
sheetData.Cells[row, 4].Value = sheetData.Cells[row, 4].Value + " modified";
}

//Refresh Pivot tables - this causes the issue.

int sheetCount = workbook.Worksheets.Count;
for (int intIndexSheet = 0; intIndexSheet < sheetCount; intIndexSheet++)
{
Worksheet sheet = workbook.Worksheets[intIndexSheet];

sheet.RefreshPivotTables();
}

workbook.Save("result.xlsx");
class CustomPivotTableGlobalizationSettings : PivotGlobalizationSettings
{
    //Gets the name of "(All)" label in the PivotTable.
    public override string GetTextOfAll()
    {
        Console.WriteLine("---------GetAllName-------------");
        return "(Alle)";
    }
}

You can also refer to the following document for other settings.

7

Thanks for your feedback.

I applied the workaround to our code to not refresh PivotTables with RefreshDataOnOpeningFile == true, and additionally added the localization code to fallback that calls piv.RefreshData(); piv.CalculateData();

So, if we run into this issue with other queries, we can simply switch the pivot table in the excel template to auto refresh and the problem will be gone. But a fix for the remaining two problems would be nice anyway.

Best regards

Wolfgang

8

@wknauf,

It seems the suggested workaround works for your needs to cope with the problem. And, sure, we will be working over the following two tickets:

  • CELLSNET-58443 - Filters are changed after refreshing pivot tables
  • CELLSNET-58444 - The decrease in column width causes the date to be shown as “#####” after refreshing pivot table

Once we have an update on any of the issues, we will let you know here.

9

Just for the records: activating the excel setting that corresponds to the flag PivotTable.RefreshDataOnOpeningFile causes excel to ask every time when closing the file whether changes should be saved. This is not so nice for end users I think.

10

@wknauf,

The PivotTable.RefreshDataOnOpeningFile property indicates whether you need to refresh data by MS Excel when opening the resultant file into it. If you set it to “true” in code, then MS Excel will refresh it when opening the file into it. When you set it to “false”, MS Excel won’t refresh pivot table, so it is always better to call pivotTable.RefreshData() and pivotTable.CalculateData() methods in code just before saving the Excel file.

11

@wknauf
25.5.4.zip (22.7 KB)

Please check attached file which is generated by inner hotfix 25.5.4 with following codes:

 Workbook workbook = new Workbook(dir + "sample.xlsx"); 
 Worksheet sheetData = workbook.Worksheets[0];
 int maxDataRow = sheetData.Cells.MaxDataRow;
 for (int row = 1; row <= maxDataRow; row++)
 {
     //Column 4 contains last names:
     sheetData.Cells[row, 4].Value = sheetData.Cells[row, 4].Value + " modified";
 }
 workbook.Settings.GlobalizationSettings.PivotSettings = new CustomPivotTableGlobalizationSettings();
 workbook.Worksheets.RefreshAll();
 workbook.Save(dir + "25.5.4.xlsx");
12

Does workbook.Worksheets.RefreshAll() also refresh the pivot tables?

If yes: Problem 1 (pivot filter reverts to multi select) is fixed. Problem 2 seems to be fixed by CustomPivotTableGlobalizationSettings.
But problem 3 is still open (column D7 with current date is too small).

13

@wknauf
Your understanding is correct. WorksheetCollection.RefreshAll method will refresh all pivot tables and charts with pivot source in the workbook. Please refer to the following document.

Regarding problem 3, please refer to the result file provided by @simon.zhao. We have already resolved the issue.

result_d7.png911×852 51.1 KB

If you can still reproduce the issue, please provide a screenshot of the opened result file, and we will check it soon.

14

Here is a screenshot of the excel file created by @simon.zhao (“25.5.4.zip”):

With my german excel, the cell content is displayed as “25.05.2025”. It becomes visible if I make the col just a bit wider or double click it for autosize (10,57 instead of 9,71), so the problem might be cause by the fact that the english text is is bit shorter?

Best regards

Wolfgang

15

@wknauf
Thank you for your feedback. We can reproduce your issue in the German environment. We will continue to fix the issue and notify you promptly once there are updates.

16

@wknauf
The file 25.4.4.zip is generated in English environment. The display string value of the date time is different from German. Please set the region when loading the file or run in German environment.
25.5.5.zip (22.6 KB) is generated by the following codes:

 LoadOptions loadOptions = new LoadOptions();
 loadOptions.Region = CountryCode.Germany; //set region as Germany
 Workbook workbook = new Workbook(testPath + "pivottable/CellsNet58443.xlsx", loadOptions);
 Worksheet sheetData = workbook.Worksheets[0];
 int maxDataRow = sheetData.Cells.MaxDataRow;
 for (int row = 1; row <= maxDataRow; row++)
 {
     //Column 4 contains last names:
     sheetData.Cells[row, 4].Value = sheetData.Cells[row, 4].Value + " modified";
 }
 Console.WriteLine(workbook.Worksheets[1].Cells["D7"].StringValue);
 workbook.Settings.GlobalizationSettings.PivotSettings = new CustomPivotTableGlobalizationSettings();
 workbook.Worksheets.RefreshAll();
 workbook.Save(dir + "dest.xlsx");
17

Thanks!

I assume that the “LoadOptions.Region” is just a hint for Aspose.Cells to render e.g. date values, but is not stored in the excel file?

Could you confirm that setting the region on load does not change the handling of e.g. formulas? Aspose.Cells still uses the english names internally?

Best regards

Wolfgang

18

@wknauf,

Yes, your understanding is correct. The LoadOptions.Region is used only as a little reference for cultural formatting during the loading of the Excel file. It affects only parsing of locale-specific number/date formats. It does not change the internal logic or structure of the Excel file. It does not affect on how formulas are stored, parsed, or evaluated (it uses English names). It does not persist or write into the saved Excel file.

19

What happens if a file is not loaded from template but created from scratch? Also the comments on LoadOptions.Region irritated me (“… If you do not want to use the region saved in the file, please reset it after reading the file.”).

After digging a bit through your code, i found the property Workbook.Settings.Region. Could you confirm that assigning CountryCode.Germany to this property has the same effect as setting LoadOptions.Region when a template is loaded, but also works for blank files?

Also, WorkbookSettings.Region has a much more detailed comment than LoadOptions.Region - the latter sounds like this value would be persisted in the excel file, but the first property states that this happens only for xls format, but not for xlsx.

20

@wknauf

For LoadOptions.Region, it takes effect not only in the process of instantiating a Workbook from template files. After the loading process, the instantiated Workbook will keep the specified regional setting. That is, its WorkbookSettings.Region is the value specified by LoadOptions.Region.

When loading workbook from template files, some locale-depended features may need the proper regional settings to make the workbook behavior exactly as expected, such as the default font to measure row height/column width…, the text values that need to be parsed as numeric/datetime for cells, …etc.

If the workbook is created from scratch, you may set region for it by WorkbookSettings.Region to make the workbook give the expected behavior(same effect with specifying LoadOptions.Region when loading from template file).

For the difference between xls and xlsx: for xls file format, regional settings information may be saved in the template file, so when you loading workbook from xls file without specifying your expected region, the regional settings saved in the template file(if this data exists in the file) will be used. However, for xlsx there is no such kind of data, so when loading workbook from xlsx template file without specified region, environment’s default region will be used always.