Pivot table cell references do not work when producing pdf reports from Excel templates

Aspose.Cells Product Family
1

Hello
Pivot table cell references (ie: using =Pivot!A2) does not work when producing .PDF from Excel templates.

Steps to reproduce;

  1. Open solution, run it and check the “ConsoleApp3\bin\Debug” folder for results

Expected Behavior:
All three reference to the data (on the three excel template sheets, ie: using =Pivot!A2) should display the same data in the generated .pdf;

Actual results:
Referenced pivot table cells (ie: using =Pivot!A2) displays the tag name instead of the contents of the cell when report is run to .pdf;

Thanks

2

@andreaghirardello,

Thanks for your query.
It seems that you have forgotten to attach the solution. Please provide it here for our reference. If due to some reason you are not able to attach it here, you may upload the solution to some public file sharing server and provide the link to download it. We will reproduce the issue here and provide our feedback.

Also regarding the expected behavior, please share the expected output file created by Excel for our reference.

3

Hi,
Thanks for the quick response. Basically i’m trying to do something like this:
new Workbook("[your_path]\input.xlsx").Save("[your_path]\result.pdf"), new PdfSaveOptions());

Attaching input.xlsx and result.pdf for comparison.
input.zip (49.3 KB)

4

@andreaghirardello,

We were able to observe the issue but we need to look into it more. We have logged the issue in our database for investigation and for a fix. Once, we will have some news for you, we will update you in this topic.

This issue has been logged as

CELLSNET-46614 - Pivot table cell references do not work
5

@andreaghirardello,

Please calculate formulas before saving PDF file.

Workbook workbook = new Workbook(dir + "input.xlsx");
workbook.CalculateFormula();
workbook.Save(dir + "dest.pdf");

Let us know your feedback.

6

Thanks! this solved the problem!

7

@andreaghirardello,

Good to know that the suggested code segment figures out your issue now. Feel free to contact us any time if you need further help or have some other issue or queries, we will be happy to assist you soon.

1 Like
8

Hi, is there a way to apply the calculateformula() in the narrowest way possible? For example apply it to the single pivot i need to evaluate instead of the entire workbook?
Because i could have xlsx templates with a lot of formulas.
Thanks

9

@andreaghirardello,

Well, Workbook.CalculateFormula() is already optimized (even it can process huge files efficiently) so you do not worry about using it. You may even try to use Worksheet.CalculateFormula() for selected sheets before rendering to PDF file format.

If you find any issue regarding Workbook.CalculateFormula, do let us know with template files and sample code, we will check it soon.

10

Thanks.
We’ll try by using Workbook.CalculateFormula() and let you know if we find something strange.

Br,
Andrea

11

@andreaghirardello,

Sure, take your time. In the event of any issue or queries, feel free to write us back, we will check it soon.