Issue on Pivot Table Rendering After Refresh

Aspose.Cells Product Family
1

Hi,

I’m using Aspose.Cells for .NET version 7.1.2.
I open an xlsx template file then I fill it with new data then refresh pivot table and formulas before saving it all in PDF format.

I do a full refresh using that method:
void FinalRefresh(Workbook wb)
{
foreach (Worksheet ws in wb.Worksheets)
{
foreach (PivotTable t in ws.PivotTables)
{
t.RefreshData();
t.CalculateData();
}
}
wb.CalculateFormula();
}

I currently have an issue with the rendering of pivot table: the header row, that normally should contain field values, is empty.
Thus the whole pivot table is shifted one row upward.

You’ll find attached the file straight out the automated script (OutOfScript.xlsx) and the same file with pivot table manually updated in Excel (AfterExcelUpdate.xlsx).

The point is I’m using that script to generate PDF, not Excel.
So the option to use RefreshDataOnOpeningFile or RefreshDataFlag is useless.

Regards
Grégoire Cousin

2

Hi,

Please see the following code. I have attached both the output pdfs generated by your provided files using the code below.

It appears to me fine.

Let me know, if there are any errors. Help us with your screenshots with red circles.

Please download and use the latest version:
Aspose.Cells
for .NET v7.1.2.4

C#


string filePath = @“F:\Shak-Data-RW\Downloads\OutOfScript.xlsx”;


Workbook wb = new Workbook(filePath);


wb.CalculateFormula();


foreach (Worksheet ws in wb.Worksheets)

{

foreach (PivotTable t in ws.PivotTables)

{

t.RefreshData();

t.CalculateData();

}

}


wb.Save(filePath + “.out.pdf”, SaveFormat.Pdf);


3

Hi,

the point is not on the PDF rendering.
It is on the pivot table rendering that is visible in Excel format, starting at cell AD79.

By opening both files, you’ll see the difference in rendering.

Normally, coloured transition table that is visible in PDF output uses formulas pointing at AD79 pivot table.
I’ve temporarily removed those formulas.
That’s why PDF files look the same.

Regards
Grégoire Cousin

4

Hi,


I could not understand you completely. If you are talking about that in MS Excel, the XLSX file has pivot table that is not rendered at all in the output PDF, it is due to your printable area set in Excel i.e. in Page Setup dialog, click “Sheet” tab and you may see: “A1:X31” in the Print area field. Please remove this entry and re-save the file. Now open the file by Aspose.Cells API and save to PDF as per the code shared by Shakeel Faiz, it works fine accordingly. For your information, Aspose.Cells for .NET PDF rendering engine will render the PDF file based on the print preview of the worksheets in MS Excel, you may confirm this in MS Excel.

Let us know if you have different issue what I have discussed above, we will check it accordingly then.

Thank you.


5

Hi,

Thanks for your feedback.

I was able to see the problem as you mentioned after testing it with the following code.

Please see the screenshot for a reference.

We have added this issue in our database. Once we will resolve this issue or we have some other update, we will let you know asap.

This issue has been logged as CELLSNET-40518.

C#


string filePath = @“F:\Shak-Data-RW\Downloads\AfterExcelUpdate.xlsx”;


Workbook wb = new Workbook(filePath);


wb.CalculateFormula();


foreach (Worksheet ws in wb.Worksheets)

{

foreach (PivotTable t in ws.PivotTables)

{

t.RefreshData();

t.CalculateData();

}

}



wb.Save(filePath + “.out.xlsx”);

Screenshot:
6

Hi,

We have fixed this issue. Please download: Aspose.Cells for .NET v7.1.2.5

7

Hello,

sorry about that but I’ve just deployed version 7.1.2.5 and I still have the issue: the pivot table is shifted one row upward when updated using methods t.RefreshData(); t.CalculateData();

You’ll find attached the files I’ve got with version 7.1.2.5.

Regards
Grégoire Cousin

8

Hi Grégoire Cousin,


I could not find the issue, the resaved (output) file is fine to me, it does not shift the pivot table one row upwards.

string filePath = @“e:\test2\OutOfScript_2012-04-02.xlsx”;


Workbook wb = new Workbook(filePath);


wb.CalculateFormula();


foreach (Worksheet ws in wb.Worksheets)
{

foreach (PivotTable t in ws.PivotTables)
{

t.RefreshData();

t.CalculateData();

}

}

wb.Save(filePath + “.out.xlsx”, SaveFormat.Xlsx);



Could you give us screen shots to highlight (encircling with red color) and comparing the problematic areas with right areas. We will check it soon.

Thank you.
9

Hi,

a HUGE sorry and a big big Thank You !

Deployment of version 7.1.2.5 was not correct on production side.
Now it’s been done correctly everything looks fine.

Thank you again !

Grégoire

10

Hi,


It is OK :),

Have a good day!
11

The issues you have found earlier (filed as CELLSNET-40518) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by aspose.notifier.