Recalculate formulas of IChartDataWorkbook

Aspose.Slides Product Family
1

I have a IChartDataWorkbook, that I got from a IChartData, that I got from a Chart. Within this IChartDataWorkbook, I’m changing some values and need afterwards all formulas recalculated. Is this possible using Aspose.Slides? If it helps, we have a Aspose.Total licence, so I would be able to use Aspose.Cells if needed.
I’m accessing the IChartDataCell instances via ChartSeries and IChartCategory.

2

@fbu,

I have observed your requirements and regret to share that at present the support for formula calculation for chart series data elements is not available in API. An issue with ID SLIDESJAVA-34897 has already been created to provide the requested support. This thread has been linked with the issue so that you may be notified once the support will be available.

3

Ok, thanks. But what if I open the workbook as Aspose.Cells workbook and use that API and save the workbook back into the PowerPoint. After that I can update the chart series values with the recalculated values out of the workbook.
Can you please provide a workaround? If not, what is the estimated release date for SLIDESJAVA-34897?

4

@fbu,

If you will insert values in corresponding chart series data points values cells then it shall work. Otherwise, if you add formula value in chart series data points value cells, it will not work at the moment. The issue SLIDESJAVA-34897 has been added in our issue tracking system to provide this support.

5

@fbu,

This is a new feature request and it requires considerable amount of time and resources for implementation owing to its complexity. At the moment, I am unable to share any ETA and will be able to share with you as soon as it will be available.

6

Of course I understand that. But is there really no workaround using Aspose.Cells?

7

@fbu,

I suggest you to please visit this documentation link to set the chart data from external workbook. You can update the workbook data using Aspose.Cells and sets that for Aspose.Slides chart.

8

Ok, at the first view, this seems to be a valid work around:

  1. change values via chart series

  2. open workbook and calculate formulas

  3. save workbook back into the ChartData

private void replaceChart(Chart chart) {
IChartData chartData = chart.getChartData();

chartData.getSeries().forEach(series -> {
  if (series instanceof ChartSeries) {
    ChartSeries chartSeries = (ChartSeries) series;
    chartSeries.getDataPoints().forEach(dataPoint -> {
      IChartDataCell cell = dataPoint.getValue().getAsCell();
      replaceChartDataCell(cell);
    });
  }
});
chartData.getCategories().forEach(categorie -> {
  IChartDataCell cell = categorie.getAsCell();
  replaceChartDataCell(cell);
});

byte[] workbookBytes = chartData.readWorkbookStream();
ByteArrayOutputStream os = new ByteArrayOutputStream();
try (InputStream is = new ByteArrayInputStream(workbookBytes)) {
  Workbook workbook = new Workbook(is);
  CalculationOptions options = new CalculationOptions();
  options.setRecursive(true);
  options.setIgnoreError(false);
  workbook.calculateFormula(options);
  workbook.save(os, SaveFormat.XLSX);
  chartData.writeWorkbookStream(os.toByteArray());
} catch (Exception e) {
  String msg = "cannot recalculate formulas: cannot open workbook";
  sysLogger.error(msg, e);
  throw new RuntimeException(msg);
} finally {
  IOUtils.closeQuietly(os);
}

}

9

@fbu,

It’s good to know that suggested workaround worked on your end. Please feel free to share if there is any further help required from our end.

10

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