We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy.
Unfortunately, activation email could not send to your email. Please try again.
Syncfusion Feedback

Getting formula instead of text

Thread ID:

Created:

Updated:

Platform:

Replies:

59791 Apr 22,2007 09:59 PM Apr 26,2007 03:22 AM ASP.NET Web Forms (Classic) 9
loading
Tags: XlsIO
Diana Lim
Asked On April 22, 2007 09:59 PM

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 [Syncfusion]
Replied On April 23, 2007 02:56 AM

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
Replied On April 23, 2007 05:03 AM

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 [Syncfusion]
Replied On April 23, 2007 06:58 AM

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
Replied On April 23, 2007 10:01 PM

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 [Syncfusion]
Replied On April 24, 2007 06:36 AM

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
Replied On April 24, 2007 11:02 PM

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 [Syncfusion]
Replied On April 25, 2007 04:32 AM

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
Replied On April 25, 2007 10:11 PM

Thanks for your help!

Melba Winshia [Syncfusion]
Replied On April 26, 2007 03:22 AM

Hi Diana,

Thanks for the update.

Kindly let me know if you have any other questions.

Thanks,
Melba

CONFIRMATION

This post will be permanently deleted. Are you sure you want to continue?

Sorry, An error occured while processing your request. Please try again later.

Warning Icon You are using an outdated version of Internet Explorer that may not display all features of this and other websites. Upgrade to Internet Explorer 8 or newer for a better experience.Close Icon

;