We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Cells Java - Data labels based on formulas not correctly rendered in PDF

Aspose.Cells Product Family
#1

Hi Aspose Cells team,

  1. Create a workbook where chart data labels reference cells using formulas.
  2. Save as XLSX: ok - the data labels are rendered with calculated formulas results
  3. 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);

Regards,
Jean.

input.zip (11.3 KB)
expected_ok.pdf (28.3 KB)
expected_nok.pdf (28.3 KB)

#2

Aspose Cells Java Version used: 19.8

#3

@jeankst,

Thanks for the template file and sample code.

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

Hope, this helps a bit.

#4

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.

#5

@jeankst,

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.

#6

@jeankst,

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.

#7

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