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.
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.
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.
