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. (Last updated on: November 16, 2018).
Unfortunately, activation email could not send to your email. Please try again.
Syncfusion Feedback

Colunm Format -- Formula

Thread ID:





100119 Jul 6,2011 09:12 AM UTC Jul 7,2011 07:37 AM UTC WPF 1
Tags: XlsIO
Asked On July 6, 2011 09:12 AM UTC


I've 2 problems.

First, in a column I set values like 1-01, 1-02.. 1-100, and the first values are show like a day in excel, can i set there are String representations by code ?

Second, I try having a formula value, I do this code for test :

sheetPoint.Range[numRow + 1, 13].Value2 = dwgObj.DrawingPosition4.X;
sheetPoint.Range[numRow + 1, 14].Value2 = dwgObj.DrawingPosition4.Y;

sheetPoint.Range[numRow + 1, 15].Formula = "=M" + (numRow + 1) + "-N" + (numRow + 1);

String tmp = sheetPoint.Range[numRow + 1, 15].Value;
Object tmp2 = sheetPoint.Range[numRow + 1, 15].Value2;
double tmp3 = sheetPoint.Range[numRow + 1, 15].FormulaNumberValue;
String tmp4 = sheetPoint.Range[numRow + 1, 15].FormulaStringValue;

I set breakpoint on the last line, FormulaNumberValue is always at 0.0. In excel, column O have the good values. What do I need to force evaluation of the Formula ?


Sridhar [Syncfusion]
Replied On July 7, 2011 07:37 AM UTC

Hi Arnard,

Thank you very much for using Syncfusion products.

Problem 1:

I have used the following code as per your requirement to display the values 1-01, 1-02.. 1-100 in the 2nd column.

for (int i = 1; i < 100; i++)
sheet.Range[ i,2].Value = "1-0" + i.ToString();
sheet.Range[ i,2].Value = "1-" + i.ToString();

Here we can use two options.

1.sheet.Range[ i,2].Value is store the values as in Data Time format.
2.sheet.Range[ i,2].text is store the values as string in the given format.

Please use the above options at your side and let us know if these helps you.

Problem 2:

When we are calculating the formulas and the calculated values are not yet stored in the input file. So you are getting the 0 as the calculated value. Our XlsIO we only trigger the MSExcel to calculate the formulas available in the sheet, this can accessed only if it stored in the file. To avoid this we have added a runtime calculation mechanism EnableSheetCalculations() method to calculate the formula value at run time.

And for your requirement, we have the property called CalcualtedValue which returns the calculated string value for the given formula.
Use these property at your side and convert the value to the desired data format.

Code Snippet:

//Enable the Sheet Calculations to the worksheet.

//Getting the calculated Value
String tmp4 = sheet.Range["O6"].CalculatedValue;
double tmp3 = Convert.ToDouble(tmp4);

//or use the following code which do the same
double double_value = Convert.ToDouble(sheet.Range["O6"].CalculatedValue);

We have created the sample at our side to illustrate the above problems at our side and attached here for your reference, Please try the sample at your side and let us know if these helps you.

Please let us know if you require any further clarifications.




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.

Please sign in to access our forum

or the page will be automatically redirected to sign-in page in 10 seconds.

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