Getting formula instead of text

[Diana Lim]

Hi Support,

In my excel sheet, sheet.Range("D1")contains a formula that calculates out a value,

However when in code, sheet.Range("D1").Text returns me the formula instead of the text or value.

How can i access the calculated value instead of the formula?

[Melba Winshia]

Hi Diana,

Thanks for your interest in Essential XlsIO.

You can get the calculated value using IRange.FormulaNumberValue. Please refer the following code snippet to achieve this:

[C#]

// Get the calculated value
double val=mySheet.Range["D1"].FormulaNumberValue;

Here is the sample for your reference:

http://websamples.syncfusion.com/samples/XlsIO.Web/4.4.0.51/F59791/main.htm

Kindly let me know if you have any other questions.

Thanks,
Melba

[Diana Lim]

Hi Melba,

Using the precompiled sample, I was able to the result I wanted.

However, when I tried to recreate the function through by creating a new project, I'm unable to get the result.





WebSite10.zip

[Melba Winshia]

Hi Diana,

Thanks for the update.

I was able to reproduce the problem. However if we resave the workbook using MS Excel then everything works fine. Here is the sample for your reference:

http://websamples.syncfusion.com/samples/XlsIO.Web/4.4.0.51/F59791_VB/main.htm

Could you please let us know how the workbook (SampleOne.xls) was created (using Ms Excel or XlsIO)? This would help us in investigating further on this issue.

Note:
----
Please note that XlsIO does not do the calculation. The calculation is done by Excel when the file is opened using MS Excel and the value that XlsIO retrieves are the results of those calculation. So, in order for XlsIO to retrieve the values correctly, the workbook has to be saved using MS Excel atleast once.

Thanks,
Melba

[Diana Lim]

Hi Melba,

I've modified the file so that it is created using XlsIO.

However, as you can see from the sample, it will not be able to read the FormulaNumberValue.

As our interest in getting this software lies in the fact that it "bypasses" excel, it would not make much sense for us to use excel to save the file and re-open it to get the value. If that were the case, we would use excel to code straightaway.

WebSite11.zip

[Melba Winshia]

Hi Diana,

Thanks for the update.

The computed values can be retrieved by using Essential Calculate along with Essential XlsIO. Please refer the following code snippet to achieve this:

[C#]
'Values
sheet.Range("J10").Number = 0.70787
sheet.Range("J2").Number = 56093

'Formula
For i As Integer = 2 To RetDT.Rows.Count + 1
sheet.Range("D" & i).Formula = "=$J$2*EXP($J$10*C" & i & ")"
Next

' Calculate using Essential Calculate
Me.RefreshCalcEngine(workbook, True)

'Read computed Formula Value.
Me.txtFomulaNumber.Text = sheet.Range("D2").FormulaNumberValue.ToString()

Here is the modified sample using Essential XlsIO and Essential Calculate

WebSite11.zip

And also, Could you please refer to the sample that is shipped with Essential XlsIO, which could be found at following Url

C:\Program Files\Syncfusion\Essential Studio\4.4.0.51\Web\xlsio.web\Samples\2.0\CalculationEngine\XlsIOCalcEngine

Note:
----
You have used FormulaArray. At present Syncfusion.Calculate.CalcWorkbook does not evaluate Array Formulas. Already we have logged a feature request regarding this issue.

Could you please refer our online documentation to know the library functions that are shipped in the Essential Calculate library?

http://www2.syncfusion.com/library/default.aspx

[Essential Calculate->Base->Function Reference Section]

Kindly let me know if you have any other questions.

Thanks,
Melba

[Diana Lim]

Hi Melba,

Thanks for your help!

Just to clarify, if Syncfusion.Calculate.CalcWorkbook does evaluate Array Formulas, but the Logest excel function is not supported, would that mean that I would still be unable to reach the results that I need?

[Melba Winshia]

Hi Diana,

Thanks for the update.

Currently Essential Calculate does not provide support for Logest function. I have created feature request regarding this issue. Sorry for the inconvenience.

Kindly let me know if you have any other questions.

Thanks,
Melba

[Diana Lim]

Thanks for your help!

[Melba Winshia]

Hi Diana,

Thanks for the update.

Kindly let me know if you have any other questions.

Thanks,
Melba