Failed to Save Presentation with Charts Containing Unsupported Formulas in C#

Aspose.Slides Product Family
, , ,
1

In our application we are using aspose.slides to fill powerpoint documents, defined by the user, with data from our backend, when using charts with certain formulars in the datasheet apsose throws an exception on saving the document. (which did not happen in earlier versions)

I am using Aspose.Slides 21.4
When saving a Presentation, where a slide contains chart which is using IF or OR functions in its data areas

Sample Formular =IF(OR(in!$H5="False";in!$H5="");in!B5;"Z")

Exception: Aspose.Slides.Spreadsheet.CellUnsupportedDataException: The cell 'temp'!F4 contains an unsupported formula.

This error did not exist in previous version of Aspose(19.8), there I had to use Aspose.Cell to recalculate the workbook and reapplied it to the chart. When the calculation was not successfully the chart went in the document without a view update, this happened without an error from the aspose side, this was also not ideal, but still better than how it is now.

There are a few issues I have with this error:

  1. Why does this error happen at saving and not earlier. This error should happen at the moment when I modify the chart, not on saving the document.
    At the moment our generator is at the mercy that the user did not use any unsupported formular. Currently Report that was generated totally fine, can crash because of one lousy chart. And I am not even able to choose to catch this errors so I could add an error message to the chart and have an otherwise fine working document

  2. Why does it happen at all? We are talking about IF and OR functions which are two of the most basic spreadsheet functions.

  3. Can I disable the processing of the charts onSave so it would use the cached values in the spreadsheet provided by Aspose.Cells? And atleast the generation would not crash

Currently our only workaround for this error is to not use the charts or the unsupported formular. Which defeats the point of using it.

Greetings Julian

2

@jnvoigt,
Thank you for the issue description. At the moment, Aspose.Slides doesn’t support OR function for calculations. Please, look at the next article: Predefined Functions.

Usually, if you don’t call a recalculation of formulas containing unsupported expressions, the document is saved without errors.

Could you share the presentation file and code example reproducing the issue, please?

I’ve logged the issue in our tracking system with ID SLIDESNET-42552 to add supporting the OR function.

Unfortunately, when the formula in the cell changes, the last calculated cached value is reset. I’ve logged the issue in our tracking system with ID SLIDESNET-42553 to consider adding such an ability.

3

Thank you for the quick response

I build a sample in the next days

4

@jnvoigt,
Thank you, it will help us better understand the issue.

5

ChartCrashSample.zip (84.9 KB)
Ok in the zip is a boiled down version that can reproduce the errors

in the AsposeLicense.cs you need to change the path
there are two samples,

  1. nested IFs
    this will cause the presentation save call to crash

  2. OR in IF the presentation will save with updated data, but the chart wont update until open the data sheets

behavior of 2 is more ok than 1.

When using the commented out Refresh-Call in Program.cs it’s working fine because the workbook update is made by cells.
This is were I have good news, I may be able to fix our bug on my own.

6

@jnvoigt,
Thank you for the project example. I reproduced the issues and received the same results. I’ve logged the issues in our tracking system with ID SLIDESNET-42559 and SLIDESNET-42560. Our development team will investigate these cases. I will inform you about any progress.

7

Hello again,

Our Fallback idea was to check every cell in the chart workbook for formulas and replace them with their calculated values via Cells.

The replacement is working fine, but we are still getting the error were we have unsupported formulas in cells, but now we should not have any.

Out of despair we tried to replace the workbook with an empty one. Still no success

For everything we used

for reading and

at this point I am confused, annoyed and questioning a few of my life choices

somehow the the slides api, has access to its old data even after it was replaced with ChartData.WriteWorkbookStream

greetings Julian

8

@jnvoigt,
Thank you for the additional information. I will answer you later.

9

@jnvoigt,
We apologize for any inconvenience caused.

You are right, it should not be. Could you share the presentation file and code example reproducing the issue, please?

10

I will try to provide a sample to reproduce this exact issue

11

@jnvoigt,
Our development team has been working on the issues you described. As a workaround to avoid the CellUnsupportedDataException error, please try to set your calculated data for charts as an external workbook receiving from Aspose.Cells:

chart.ChartData.SetExternalWorkbook(dataPath + "externalWorkbook.xlsx");

More details: Set External Workbook
API Reference: ChartData Methods

12

Hello, were using slide in a service setting, using the file system, is not an option for us.
Does this also work with ChartData.WriteWorkbookStream, now?

We had the issue that, Chart is triggering the recalculation even, when the Workbook was overriden.
In a newer feature we also had the issue that recalculation error is also triggered even after removing every formular, but for this we have no good sample now

13

@jnvoigt,
ChartData.WriteWorkbookStream method can be used for overriding chart data as well. Did I understand correctly that this method also causes recalculation for chart data?

14

@jnvoigt,
The workbook can be processed with Aspose.Cells and uploaded back to the Aspose.Slides. Our development team prepared an example based on your code examples: SampleIFix.zip (43.6 KB). Please try it on your side.

15

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

16

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

17

The issues you have found earlier (filed as SLIDESNET-42560) have been fixed in Aspose.Slides for .NET 22.3 (ZIP, MSI).
You can check all fixes on the Release Notes page.
You can also find the latest version of our library on the Product Download page.