ChartData.SetRange Throws InvalidOperationException: Numeric Data Not Found

bernd.podhradsky · April 6, 2022, 1:17pm

Hi!

We have a very strange issue when updating the data for a chart in a PowerPoint representation. Unfortunately we cannot easily provide a source-code with a test case, because we only have this issue occurring in one customer environment (it works in our own environments with the exact same data, PowerPoint file and source-code).

What happens is this:
image.png (27.5 KB)

We know that you cannot reproduce this issue without a sample application, but this is currently very difficult to achieve, because it only happens (consistently) in the mentioned customer environment. But maybe you have an idea what is involved in your SetRange method and if you have any idea what could cause the issue (e.g. does it depend on regional settings of the computer when interpreting number formats etc.)?

Kind Regards,
Bernd

andrey.potapov · February 22, 2021, 5:40pm

@bernd.podhradsky
Thank you for the issue description. What version of Aspose.Slides are you using?

bernd.podhradsky · February 22, 2021, 6:12pm

Thanks for your reply. We are using 21.2 (the newest, as far as I know) for .NET.

andrey.potapov · February 22, 2021, 7:04pm

@bernd.podhradsky
We have investigated the issue. The exception with “Numeric data not found” message is thrown if there are no numeric cells in a specified range of cells only. We found no dependencies on regional settings or other environment settings.

bernd.podhradsky · February 23, 2021, 12:57am

Hi!

Thanks for your answer. I think we have found the issue: the formula we are setting in the SetData method references “Sheet1”. This, however, only works in English versions of PowerPoint. Our customer has a german version which is why the sheet name cannot be found. Is there any way to reference the first sheet differently or to find out the name of the sheet through the API?

Kind Regards,
Bernd

andrey.potapov · February 23, 2021, 9:46am

@bernd.podhradsky
Aspose.Slides does not require PowerPoint installation of any versions. Could you share more details about using worksheet names and language settings on your end, please?

bernd.podhradsky · February 23, 2021, 10:01am

Sure. If you create a PPTX with the German version of PowerPoint and you add an MSO Chart, the worksheet that contains the data is called “Tabelle1” or something like that. If you create the PPTX with an English version of PowerPoint and you do the same, the worksheet is called “Sheet1”.

In the formula which is passed to the SetRange method, we use Sheet1 as the worksheet reference. However, this only works for PPTX created using an English PowerPoint and it fails when using PPTX that have been created in other languages. Unfortunately there is now way to get the name of the worksheet in a PPTX (we exported the workbook to XLSX by using the export function and checked manually that the sheet names are indeed different depending on the language).

Bernd

andrey.potapov · February 23, 2021, 11:51am

@bernd.podhradsky
Thank you for the additional details. I have logged the issue in our tracking system with ID SLIDESNET-42449. Our development team will implement the described ability. You will be notified when it is added to Aspose.Slides.

andrey.potapov · July 23, 2022, 9:08am

@bernd.podhradsky,
Please try to use a workaround to pass a formula to SetRange method as below:

string sheetName = chart.ChartData.ChartDataWorkbook.GetCell(0, 0, 0).ChartDataWorksheet.Name;
string formula = sheetName + "!A2:B4";
chart.ChartData.SetRange(formula);

API Reference: IChartDataWorkbook Interface, IChartDataWorksheet Interface

agollamandala · May 25, 2021, 6:53pm

Hi @Andrey_Potapov ,
We are planning on upgrading our aspose slides (.net) version to the latest (currently on 20.7.0) but are facing this issue. getting “Numeric data not found” error when we do ChartData.SetRange. we do have some null records (not numeric/empty) to the end of the array but the same code works with versions till 20.9.0. any version greater than 20.9.0 is throwing the exception.

Can you please help.
Thanks,
Ashwin

andrey.potapov · May 25, 2021, 9:21pm

@agollamandala,
Welcome to our community! Thank you for the query. Could you share the code example reproducing the error, please? If you have used any presentation input file, please upload it as well.

agollamandala · May 26, 2021, 2:44pm

@Andrey_Potapov

Here is a test code reproducing the error… this code works fine with versions till 20.9.0 any version above that throws an exception

    public void Test_SetRange()
    {
        int worksheetIndex = 0;
        Presentation presentation = new Presentation();
        ISlide slide = presentation.Slides[0];
        

        var chart= slide.Shapes.AddChart(ChartType.ClusteredColumn, 0, 0, 500, 300);
        IChartDataWorkbook fact = chart.ChartData.ChartDataWorkbook;
        fact.Clear(worksheetIndex);

        chart.ChartData.Categories.Add(fact.GetCell(worksheetIndex, 1, 0, "Labels 1"));
        chart.ChartData.Categories.Add(fact.GetCell(worksheetIndex, 2, 0, "Labels 2"));
        chart.ChartData.Categories.Add(fact.GetCell(worksheetIndex, 3, 0, "Labels 3"));

        var series1 = chart.ChartData.Series.Add(fact.GetCell(worksheetIndex, 0, 1, "Series1"), chart.Type);
        var series2 = chart.ChartData.Series.Add(fact.GetCell(worksheetIndex, 0, 2, "Series2"), chart.Type);


        series1.DataPoints.AddDataPointForBarSeries(fact.GetCell(worksheetIndex, 1, 1, 0.12));
        series2.DataPoints.AddDataPointForBarSeries(fact.GetCell(worksheetIndex, 1, 2, 0.2));

        series1.DataPoints.AddDataPointForBarSeries(fact.GetCell(worksheetIndex, 2, 1, 0.12));
        series2.DataPoints.AddDataPointForBarSeries(fact.GetCell(worksheetIndex, 2, 2, 0.2));
                                                                 
        series1.DataPoints.AddDataPointForBarSeries(fact.GetCell(worksheetIndex, 3, 1, null));
        series2.DataPoints.AddDataPointForBarSeries(fact.GetCell(worksheetIndex, 3, 2, null));


        chart.ChartData.SetRange($"Sheet1!$A$1:$C$4");

        presentation.Save(@"C:\\Temp\\Test1.pptx", Aspose.Slides.Export.SaveFormat.Pptx);
    }

andrey.potapov · May 26, 2021, 4:41pm

@agollamandala,
Thank you for the code example. I reproduced the error and logged the issue in our tracking system with ID SLIDESNET-42620. Our development team will investigate this case. I will let you know any news.

aspose.notifier · July 19, 2021, 7:42am

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

andrey.potapov · April 6, 2022, 1:19pm

@Atrinocospia,
Thank you for contacting support.

Both issues addressed in this forum thread are closed. If you are facing some issues when using Aspose.Slides, please share the following data in a new forum thread:

code example that reproduces the problem
input and output files
OS version where the code was executed
.NET target platform in your app
Aspose.Slides version you used

You can zip the files and upload the archive here or share a file storage link.