Evaluating formula

[Simon Olesen]

Hello,
i have a problem evaluating a formula from a cell, with a value:

='1055'!N16

i read a few other posts on the forum:
http://www.syncfusion.com/support/forums/message.aspx?MessageID=43170

suggesting that the solution was sheet.Range[cellId].FormulaNumberValue.ToString() - but this returns "0" for me, and from what i can gather from another post on the forum (which i unfortunetly lost the link to), this is because that there is no value, because the sheet was created using XlsIO and have not yet been opened and saved by Excel - i tried opening and saving the sheet, and then it worked. but what do i do when i want to have the values calculated by formulas created in XlsIO ??

Regards,
Simon.

[Geetha M]

Hi Simon,

Thank you for your interest in Syncfusion products.

Essential XlsIO does not have a calculate engine to evaluate formulas. Alternatively, you can make use of Essential Calculate to evaluate formulas. I have created a sample doing simple addition using Essential Calculate and you can download it from your in the following link:

http://websamples.syncfusion.com/samples/XlsIO.Windows/F76315/main.htm

Further, I strongly recommend you to go through the ComputeSpecificFormula and ComputeAllFormulas samples that ships with our Essential XlsIO package.

MyDocuments\Syncfusion\EssentialStudio\{version number}\Windows\XlsIO.Windows\Samples\2.0\DataManagement\ComputeAllFormulas\

MyDocuments\Syncfusion\EssentialStudio\{version number}\Windows\XlsIO.Windows\Samples\2.0\DataManagement\ComputeSpecificFormula\

Please let me know if you have any questions.

Regards,
Geetha

[Simon Olesen]

Hello,
The ComputeAllFormulas example seemed to be exactly what i needed, but i get an error when calculating "=VLOOKUP(X3,Ark1!A2:C103,2,False)".

I have attached my example, i hope you can help clearify what i am doing wrong here.

Thanks in advance,
Simon.



SDAResumeToCSVSimple_5331299e.zip

[Geetha M]

Hi Simon,

I was not able to reproduce the problem with "=VLOOKUP(X3,Ark1!A2:C103,2,False)" rather I got the below exception when ='1055'!A16 was refreshed.

System.FormatException was caught
Message="Input string was not in a correct format."
Source="mscorlib"
StackTrace:
at System.Number.StringToNumber(String str, NumberStyles options, NumberBuffer& number, NumberFormatInfo info, Boolean parseDecimal)
at System.Number.ParseDouble(String value, NumberStyles options, NumberFormatInfo numfmt)
at System.Double.Parse(String s, NumberStyles style, NumberFormatInfo info)
at System.Double.Parse(String s)
at SDAResumeToCSV.XlsIOCalcSheet.SetValueRowCol(Object value, Int32 row, Int32 col) in

I modified the code snippet in the SetValueRowCol method in the XlsIOCalcWorkbook.cs which previously was trying to convert the text to double.

Modified code:
Double temp = 0; string temp1 = string.Empty;
try
{
temp = Double.Parse(value.ToString());
}
catch (System.FormatException)
{
excelSheet[row, col].FormulaStringValue = value.ToString();
return;
}
excelSheet[row, col].FormulaNumberValue = temp;

And now the sample works fine. Could you please let me know what kind of issue you encounter with the VLOOKUP formula?

Regards,
Geetha