Get equation text of chart trendline - Values mismatch

Flavio.montali · June 25, 2023, 6:50pm

Hello,

we are using Aspose.Cells to read the equation of a trendline in an Excel file from its label, to do this we have followed the instructions reported in the Aspose documentation (https://docs.aspose.com/cells/net/get-equation-text-of-chart-trendline/). When we read the equation of the trendline from our excel file the value get by Aspose does not match the value written in the Excel.
Below I report both values:

Excel value: y = 2,73412452858917E-14x5 - 1,36684786729484E-10x4 + 2,50451351052486E-07x3 - 2,09907409068621E-04x2 + 6,76259404128956E-02x + 1,83817780754589E+01
Value get by Aspose: y = 2,73412452881114E-14x5 - 1,36684786740373E-10x4 + 2,50451351071089E-07x3 - 2,09907409081871E-04x2 + 6,76259404171546E-02x + 1,83817780751106E+01

As you can see above the values read by Aspose are the same of the excel for the first 9 digits after the comma, from the 10th digit to the 14th values are different.
Can you please help us in finding a solution to get the right value?
Attached the Excel file we are speaking of (zipped), from which we have to get the values of the Trendline Label.
Thank you
FlavioNuovo file—CORTE C—Dati_Input_Curve (2).zip (93.0 KB)

ahsaniqbalsidiqui · May 27, 2021, 2:43am

@Flavio.montali,
We have tried to use your sample Excel file with the following sample code but get exception while accessing the trend line. Could you please share your runnable sample console application for our reference to reproduce the issue here?

We have tried the following sample code.

// Create workbook object from source Excel file
Workbook workbook = new Workbook("Nuovo file---CORTE C---Dati_Input_Curve (2).xlsx");

// Access the first worksheet
Worksheet worksheet = workbook.Worksheets[0];

// Access the first chart inside the worksheet
Chart chart = worksheet.Charts[0];

// Calculate the Chart first to get the Equation Text of Trendline
chart.Calculate();

// Access the Trendline
Trendline trendLine = chart.NSeries[0].TrendLines[0];

// Read the Equation Text of Trendline
Console.WriteLine("Equation Text: " + trendLine.DataLabels.Text);

Flavio.montali · May 27, 2021, 7:38am

Hello,

thanks for the prompt reply, we have adjusted the sample code you’ve used in order to resolve the exception you have received; basically the name of the series that has the trendline associated is the one reported below ("Elaborazione curva erogazione per polinomio "), the blank spaces in the name of the series is not an error.
With this code the opening of the trendline should not give an error.

// Create workbook object from source Excel file
Workbook workbook = new Workbook(“Nuovo file—CORTE C—Dati_Input_Curve (2).xlsx”);

// Access the first worksheet
Worksheet worksheet = workbook.Worksheets[0];

// Access the first chart inside the worksheet
Chart chart = worksheet.Charts[0];

// Calculate the Chart first to get the Equation Text of Trendline
chart.Calculate();

// Access the Trendline
Aspose.Cells.Charts.Trendline trendLine = chart.NSeries.Where(x => x.DisplayName.Equals("Elaborazione curva erogazione per polinomio
")).FirstOrDefault().TrendLines[0];

// Read the Equation Text of Trendline
Console.WriteLine("Equation Text: " + trendLine.DataLabels.Text);

ahsaniqbalsidiqui · May 27, 2021, 9:05am

@Flavio.montali,
We were able to observe the issue but we need to look into it more. We have logged the issue in our database for investigation and for a fix. Once, we will have some news for you, we will update you in this topic.

This issue has been logged as

CELLSNET-48103 - Get equation text of chart trendline - Values mismatch

Flavio.montali · June 1, 2021, 6:56am

Hello, is there any update? we have a detail more to share: by converting the label of the charts in text (I managed to do this by replacing the commas decimal separator included in the trendline equation with dots) the trendline equation is read correctly.
Unfortunately is not an applicable work-around in our scenario so this could not work in our production environment, but seems like the problem is related to the scientific format of the trendline equation.
Hope this helps.
Regards

amjad.sahi · June 1, 2021, 8:11am

@Flavio.montali,

I am afraid, your issue is not resolved yet. We are evaluating your issue in details. Once we have an update on it or have an ETA on it, we will let you know here.

Flavio.montali · June 1, 2021, 8:25am

Thanks,

please this feature is really important for us.
Also a temporary work-around is welcome at first.

amjad.sahi · June 1, 2021, 9:01am

@Flavio.montali,

We understand your concerns. We will try to provide you the fixed version within 5-7 days or so.

Moreover, there is no workaround to cope with it temporary.

amjad.sahi · June 25, 2023, 6:51pm

@Flavio.montali,

We evaluated your issue in details. The equation of trendline is obtained by calculation not by read (unless the text of trendline label is modified manually), and the accuracy cannot be same as MS Excel. By the way, we test by function LINEST in Excel, its result is also not the same as the label in the chart.

The attachment (TrendLineSample.zip) is a sample project on how we calculate the equation. By using BigDecimal instead of double to make the result more accurate. Hope, this helps a bit.
TrendLineSample.zip (104.7 KB)
LINEST Test.png (60.3 KB)