Create a workbook where chart data labels reference cells using formulas.
Save as XLSX: ok - the data labels are rendered with calculated formulas results
Save as PDF: not ok - data labels are rendered with original series value
Here is the code snippet:
String inputFilePath = "/input.xlsx";
String outputExcelFilePath = "/output.xlsx";
String outputPdfFilePath = "/output.pdf";
Workbook workBook = null;
PdfSaveOptions saveOptions = new PdfSaveOptions(SaveFormat.PDF);
saveOptions.setCreatedTime(DateTime.getNow());
saveOptions.setAllColumnsInOnePagePerSheet(true);
saveOptions.setOnePagePerSheet(true);
saveOptions.setCalculateFormula(true);
// Set formulas
workBook = new Workbook(inputFilePath);
Worksheet sheet = workBook.getWorksheets().get(0);
sheet.getCells().get("A2").setValue("123");
sheet.getCells().get("B2").setFormula("=B1");
sheet.getCells().get("C2").setFormula("=C1*10");
// Set data labels based on formulas
Chart chart = sheet.getCharts().get(0);
for (int i = 0; i < chart.getNSeries().get(0).getPoints().getCount(); i++)
{
int row = sheet.getCells().get("A2").getRow();
int col = sheet.getCells().get("A2").getColumn()+i;
String cellName = sheet.getCells().get(row, col).getName();
ChartPoint point = chart.getNSeries().get(0).getPoints().get(i);
String formula = "="+sheet.getName()+"!"+cellName;
log.debug("formula="+formula);
point.getDataLabels().setLinkedSource(formula);
}
// Save as Excel (will have expected data labels)
workBook.save(outputExcelFilePath);
// Save as PDF - will not have the expected data labels
workBook.save(outputPdfFilePath, saveOptions);
// Workaround: reopen Workbook and the save as PDF - have correct data labels
// workBook = new Workbook(outputExcelFilePath);
// workBook.save(outputPdfFilePath, saveOptions);
I noticed the issue as you mentioned by using your sample code with your template file. The method setLinkedSource is not working properly. Well, you may try the following workaround to cope with it and it will work as expected. You may change the code segment as following:
e.g Sample code:
.......
workBook = new Workbook(inputFilePath);
Worksheet sheet = workBook.getWorksheets().get(0);
sheet.getCells().get("A2").setValue("123");
sheet.getCells().get("B2").setFormula("=B1");
sheet.getCells().get("C2").setFormula("=C1*10");
workBook.calculateFormula();
// Set data labels based on formulas
Chart chart = sheet.getCharts().get(0);
for (int i = 0; i < chart.getNSeries().get(0).getPoints().getCount(); i++)
{
int row = sheet.getCells().get("A2").getRow();
int col = sheet.getCells().get("A2").getColumn()+i;
String cellName = sheet.getCells().get(row, col).getName();
ChartPoint point = chart.getNSeries().get(0).getPoints().get(i);
point.getDataLabels().setText(sheet.getCells().get(cellName).getStringValue());
}
// Save as Excel (will have expected data labels)
workBook.save(outputExcelFilePath);
// Save as PDF - will not have the expected data labels
workBook.save(outputPdfFilePath, saveOptions);
.....
Hi Amjad,
Unfortunately we cannot “hardcode” data labels as we want those to be refreshed when the data area is updated by the end user - we need both Excel and PDF files from the same workbook.
Regards,
Jean.
We are sorry that you cannot use the workaround to cope with it. Since we already reproduced the issue (I found data labels based on formulas are not correctly rendered in PDF) using your sample code and template file, so we have logged a ticket with an id “CELLSJAVA-42996” for your issue.
We will look into it soon.
Once we have an update on it, we will let you know.
This is to inform you that your issue is fixed now. We are scheduled to publish our next official release, i.e., Aspose.Cells for Java v19.9 in the next few days which will include your fix.
The issues you have found earlier (filed as CELLSJAVA-42996) have been fixed in Aspose.Cells for Java v19.9. This message was posted using BugNotificationTool from Downloads module by ahsaniqbalsidiqui