Hi, I’m testing Aspose.Cells Java to convert xlsx to pdf.
But it doesn’t refresh the pivot tables and the pdf exit with fields not correct.
How I can do? Thanks
Andrea
Hi,
Thanks for the details.
Please make sure that the PivotTable’s underlying data source is not referring to external data source which is not supported. Aspose.Cells only supports to refresh data based on data source (internal) in the same workbook. Also, you may try to add the following lines to refresh and calculate data before rendering to PDF file format if it works fine, you may try it in the loop to iterate each Pivot Table in the worksheet(s):
e.g
Sample code:
worksheet.getPivotTables().get(index).refreshData();
worksheet.getPivotTables().get(index).calculateData();
…
If you still find the issue and the PivotTables’ data source is not external, kindly do provide your template file (containing the PivotTables) and sample code (or preferably a simple console application (runnable), you may zip it prior attaching here), we will check it soon.
PS. Please try using our latest version/fix: Aspose.Cells for Java v8.9.2.6.
Thank you.
Hi Andrea,
Thank you for contacting Aspose support.
I have evaluated the presented scenario while using the following piece of code against the latest version of Aspose.Cells for Java 8.9.2.6. I am afraid, I am not able to see any problem in the resultant PDF as the Pivot Table has been refreshed according to the new data source. Please note, the Aspose.Cells APIs currently do not support Pivot Table recalculation in case the data source is external. However, if the data source resides in the same spreadsheet, you can refresh the Pivot Table on similar lines.
In case the problem persists, please provide us an executable sample application (or Java code) along with the input spreadsheet for further investigation.
Java
Workbook book = new Workbook(dir + “sample.xlsx”);
PivotTable pivot = book.getWorksheets().get(0).getPivotTables().get(0);
System.out.println(pivot.getDataSource()[0]);
for (int i = 2; i < 12; i++)
{
System.out.println(book.getWorksheets().get(0).getCells().get(“G” + i).getValue());
}
pivot.changeDataSource(new String[] { book.getWorksheets().getRangeByName(“testRange”).getRefersTo() });
System.out.println(pivot.getDataSource()[0]);
pivot.setRefreshDataFlag(true);
pivot.refreshData();
pivot.setRefreshDataFlag(false);
pivot.calculateData();
for (int i = 2; i < 12; i++)
{
System.out.println(book.getWorksheets().get(0).getCells().get(“G” + i).getValue());
}
book.save(dir + “output.xlsx”);
book.save(dir + “output.pdf”);
PivotTable pivot = book.getWorksheets().get(0).getPivotTables().get(0);
System.out.println(pivot.getDataSource()[0]);
for (int i = 2; i < 12; i++)
{
System.out.println(book.getWorksheets().get(0).getCells().get(“G” + i).getValue());
}
pivot.changeDataSource(new String[] { book.getWorksheets().getRangeByName(“testRange”).getRefersTo() });
System.out.println(pivot.getDataSource()[0]);
pivot.setRefreshDataFlag(true);
pivot.refreshData();
pivot.setRefreshDataFlag(false);
pivot.calculateData();
for (int i = 2; i < 12; i++)
{
System.out.println(book.getWorksheets().get(0).getCells().get(“G” + i).getValue());
}
book.save(dir + “output.xlsx”);
book.save(dir + “output.pdf”);
Hi, I attach my xlsx,
In third sheet change the values in column report.referenceCcy.
Then I save without refresh pivot.
I run this code:
for (int i = 0; i < asposeWorkbook.getWorksheets().getCount(); i++) {
Worksheet asposeWorksheet = asposeWorkbook.getWorksheets().get(i);
for (int j = 0; j < asposeWorksheet.getPivotTables().getCount(); j++) {
PivotTable pt = asposeWorksheet.getPivotTables().get(j);
try {
pt.setRefreshDataOnOpeningFile(false);
System.out.println(pt.getName());
for (int a = 0; a < pt.getDataFields().getCount(); a++) {
pt.setRefreshDataFlag(true);
pt.refreshData();
pt.setRefreshDataFlag(false);
pt.calculateData();
}
}
catch (Throwable t) {}
}
asposeWorkbook.save(“c:\Risk Report.pdf”, SaveFormat.PDF);
But the pdf have old value.
In the pdf attached the page three have currency: CHF (correct)
in the first page it is: aCHF (old value)
Thank you for help
Hi,
Well, I tried to manually refresh the report table report in MS Excel, it gives me error as per the screenshot:
so there is some issue with your template file too. Anyways, I have simply used the following sample code (I have updated your code segment to evaluate the issue only) with your attached file, it works fine and the output PDF file (attached) is also fine.
I can also see the PivotTable is not rendered at all on the second page (in the output PDF file). But this is due to the fact that in the second sheet, the Printable area is set as “A1:U36”, so it won’t render the PivotTable which is outside of this area. You got to clear this area (printable area for second worksheet PageSetup) either dynamically by Aspose.Cells APIs or manually in MS Excel if you want to render the PivotTable also. By the way, could you give more details, what’s wrong with the output PDF file. We appreciate if you could create a simple console demo application with v8.9.2.6 (Download link is already provided in the previous post) to reproduce the issue on our end? Are you inputting the value into the data source (for PivotTable) sheet dynamically via code? Please also provide us your original template file (if any). You may zip your project and attach it here with all the files. This will help us to evaluate your issue properly to consequently figure your issue out soon.
e.g
Sample code:
Workbook asposeWorkbook = new Workbook(“Risk+Report.xlsx”);
for (int i = 0; i < asposeWorkbook.getWorksheets().getCount(); i++) {
Worksheet asposeWorksheet = asposeWorkbook.getWorksheets().get(i);
for (int j = 0; j < asposeWorksheet.getPivotTables().getCount(); j++) {
PivotTable pt = asposeWorksheet.getPivotTables().get(j);
//Refresh first two sheets onlly for PivotTables data.
if(i<2)
{
pt.setRefreshDataOnOpeningFile(false);
System.out.println(pt.getName());
System.out.println(asposeWorksheet.getName());
pt.setRefreshDataFlag(true);
pt.refreshData();
pt.setRefreshDataFlag(false);
pt.calculateData();
}
}
}
asposeWorkbook.save(“out1Risk Report2.pdf”, SaveFormat.PDF);
Thank you.
I send you test.zip, with a simple console app, when i run it, the pivot remains with field “Bar” while data table name is "Bar test"
I would that Aspose Cells refresh the pivot fields with “Bar test” and create pdf. but seems not work…
I used the last aspose 8.9.2 version.
Thanks
Hi,
After an initial test, I observed the issue as you mentioned by using the following sample code with your template file. I found that refreshing PivotTable is not working in the rendered PDF file, the data is not refreshed/calculated in the PivotTable report in the output PDF file.
e.g
Sample code:
Workbook asposeWorkbook = new Workbook(“sample1.xlsx”);
for (int i = 0; i < asposeWorkbook.getWorksheets().getCount(); i++) {
Worksheet asposeWorksheet = asposeWorkbook.getWorksheets().get(i);
for (int j = 0; j < asposeWorksheet.getPivotTables().getCount(); j++) {
PivotTable pt = asposeWorksheet.getPivotTables().get(j);
try {
System.out.println(pt.getName());
System.out.println(asposeWorksheet.getName());
pt.refreshData();
pt.calculateData();
}
catch (Throwable t) {}
}
asposeWorkbook.save(“out1.pdf”, SaveFormat.PDF);
e.g
Sample code:
Workbook asposeWorkbook = new Workbook(“sample1.xlsx”);
for (int i = 0; i < asposeWorkbook.getWorksheets().getCount(); i++) {
Worksheet asposeWorksheet = asposeWorkbook.getWorksheets().get(i);
for (int j = 0; j < asposeWorksheet.getPivotTables().getCount(); j++) {
PivotTable pt = asposeWorksheet.getPivotTables().get(j);
try {
System.out.println(pt.getName());
System.out.println(asposeWorksheet.getName());
pt.refreshData();
pt.calculateData();
}
catch (Throwable t) {}
}
asposeWorkbook.save(“out1.pdf”, SaveFormat.PDF);
I have logged a ticket with an id “CELLSJAVA-41974” for your issue. We will look into your issue soon.
Once we have an update on it, we will let you know here.
Thank you.
Hi,
when it will be fixed? otherwise we move to another framework…
Thank you
Andrea
Hi Andera,
Thank you for writing back.
Please note, the ticket logged earlier as CELLSJAVA-41974 is currently in analysis phase therefore we are not able to share any updates unless we have completed the preliminary analysis. I have requested the concerned member of the product team to share some insight of the problem along with ETA, if possible. As soon as we get any news in this regard, we will post here for your kind reference.
Hi,
This is to inform you that we are working over your issue and we hope that your issue would be fixed by September 21, 2016.
Once the fix is available, we will share the Download link with your here.
Keep in touch.
Thank you.
Hi,
This is to update you that the ticket logged earlier as “CELLSJAVA-41974” has been resolved now. The fix would be delivered in the next few days (2-5 days) after performing QA and incorporating other enhancements and fixes.
Thank you.
Hi,
Thanks for using Aspose.Cells.
Please download and try the latest fix: Aspose.Cells for Java v9.0.4 and let us know your feedback.
The issues you have found earlier (filed as CELLSJAVA-41974) have been fixed in this update.
This message was posted using Notification2Forum from Downloads module by Aspose Notifier.