linkedinfacebooktwittermenuarrow-up

GemBox Support Forum

Formulas based on pivot table shows invalid reference after PivotCache.Refresh() and worksheet.calculate

I’ve uploaded a test project to show this error. I’m loading an XLSX file with data where I haven’t called the PivotCache.Refresh() and worksheet.Calculate() methods.

After calling PivotCache.Refresh(), the pivot table has the correct numbers but I’ve lost my cell formatting (numbers with commas). The cell background color of the first row is not correct either; it has the same background color as the pivot table column headers.

After calling the worksheet.Calculate(), my formulas based on the pivot table have invalid references because it looks like the pivot table doesn’t recognize row filters.
E.g. my formula GETPIVOTDATA("Sum of CuLand",Pivot!$A$1,"CueType","Agricultural") has an invalid reference because the pivot table doesn’t recognize the row filter CueType=Agricultural

Everything is corrected when I open the spreadsheet in excel and refresh all my data. Please fix this because I’m trying to print the last worksheet in the spreadsheet without asking my users to manually open the spreadsheet, refresh the data, and then print from Excel.

Here’s the link to my test project:

Hi Hoang,

Just wanted to let you know that we were able to reproduce your issue and are currently working on it.
I’ll contact you again as soon as possible.

Regards,
Mario

Hi Hoang,

Please try again with this bugfix:
https://www.gemboxsoftware.com/spreadsheet/nightlybuilds/GBS49v1118.zip

Or this NuGet package:
Install-Package GemBox.Spreadsheet -Version 49.0.1118-hotfix

However, note that the number format will be different.
This is because the number format was set directly on the cell of the pivot table, instead of that, you should set the number format on the data field itself.

Anyway, I hope this helps, let me know if you need anything else.

Regards,
Mario

looks good except one minor thing. can you include the cell formatting? I had formatted the pivot table to to show commas. If I refresh the data within excel, it’ll put the commas back but I’d like to print with the commas.

Hi Hoang,

That is what I was referring to with “number format”, please check my previous reply.

Don’t specify the formatting on the cell, specify it on the pivot field.
For example, see the following:

image1

I hope this helps.

Regards,
Mario