Load xltx, update data and save to PDF - charts empty

GemBox.Spreadsheet
1

Hi

I’m currently testing the spreadsheet library. I created an Excel template file (XLTX) that contains blank cells, some cells with formulas, and some charts with references to those blank cells.

I then load the template with “ExcelFile.Load(…)” which works fine. Filling the empty cells with numeric values ​​also works fine. I found that I need to call “worksheet.Calculate()” to update the cells with formulas. When I save the workbook to PDF or HTML, the cells with formulas are updated and contain the correct values but the charts don’t - they stay blank. So there must be a similar method to call to trigger the charts to update, but I can’t find it. Would you please point me in the right direction?

Thanks,

Frank

2

Hi Frank,

What type of chart do you have?

Note that some types (like 3D charts) are currently supported only through preservation, please refer to the following help page:
https://www.gemboxsoftware.com/spreadsheet/docs/charts.html

If you are using a supported chart type, can you please save your output to XLSX and send it to us?

Regards,
Mario

3

Good morning,

I’m using plain 2D Pi-Charts - which are predefined in the template file. I happy to provide anything you need. Where can I upload the files?

Just to make sure you understand my issue. It is not the output XLSX that is the problem, it is PDF and HTML. I guess when opening up the Excel file, an update of the charts is triggered. But when I export to PDF and HTML this does obviously not work in those formats.

Cheers,

Frank

4

Hi Frank,

The charts should be updated automatically.
The reason I’m asking for output XLSX is that we’ll probably be able to reproduce your issue by using that file.
You can send us the file via email or by submitting a support ticket, see the Contact page.

Regards,
Mario

5

Hi Mario,

thanks for comming back to me so quickly.

I just realized a funny thing. I created the XLTX in MS Office (Excel) with the previously described behaviour. For my development I’m working in virtual machines and there I’m using Libre Office. I just realized, when I open the XLTX (created in Excel) in Libre Office and save it back to a XLTX file, and use this template file then in Gembox.Spreadsheet, the charts are updated when saving the file to PDF/HTML.

I’ll create a support ticket later and send you the files, now I have a ton of meetings…

Cheers,

Frank

6

@everyone

Mario provided me with a solution. If anyone else has this isse with graphs not being updated properly, try this:

            foreach (var chart in worksheet.Charts)
            {
                foreach (var series in chart.Series)
                {
                    if ((series != null) && (series.ValuesReference != null))
                    {
                        series.ValuesReference = series.ValuesReference.TrimStart('(').TrimEnd(')');
                    }
                }
            }

It seems that leading/trailing brackets in the value references seem to cause the issue (Excel added them, LibreOffice did not).