IF-statement throws exception

[Christian Rygg]

I am generating an Excel file in an ASP.Net application, and to begin with, a formula was:

=E7/C6

All was working fine. Unfortunately C6 can be zero at times, so I changed it to:

=IF(C6 = 0,0,E7/C6)

Now I get this exception (thrown by the line where I set the formula) when I run the code:

Expression error. Formula string can't be empty
at Syncfusion.XlsIO.Implementation.FormulaUtil.ParseString(String strFormula, IWorksheet sheet, Hashtable indexes, Int32 i, Hashtable hashWorksheetNames, ExcelParseFormulaOptions options, Int32 iCellRow, Int32 iCellColumn)
at Syncfusion.XlsIO.Implementation.FormulaUtil.ParseString(String strFormula, IWorksheet sheet, Hashtable indexes, Int32 i, Hashtable hashWorksheetNames, ExcelParseFormulaOptions options, Int32 iCellRow, Int32 iCellColumn)
at Syncfusion.XlsIO.Implementation.FormulaUtil.ParseOperandString(String operand, IWorkbook parent, IWorksheet sheet, Hashtable indexes, Int32 i, Hashtable hashWorksheetNames, ExcelParseFormulaOptions options, Int32 iCellRow, Int32 iCellColumn)
at Syncfusion.XlsIO.Implementation.FormulaUtil.CreateIfFunction(Int32 iRefIndex, String strFormula, Int32 bracketIndex, IWorkbook parent, IWorksheet sheet, Hashtable hashWorksheetNames, ExcelParseFormulaOptions options, Int32 iCellRow, Int32 iCellColumn)
at Syncfusion.XlsIO.Implementation.FormulaUtil.CreateFunction(String strFormula, Int32 bracketIndex, IWorkbook parent, IWorksheet sheet, Hashtable indexes, Int32 index, Hashtable hashWorksheetNames, ExcelParseFormulaOptions options, Int32 iCellRow, Int32 iCellColumn)
at Syncfusion.XlsIO.Implementation.FormulaUtil.ParseString(String strFormula, IWorksheet sheet, Hashtable indexes, Int32 i, Hashtable hashWorksheetNames, ExcelParseFormulaOptions options, Int32 iCellRow, Int32 iCellColumn)
at Syncfusion.XlsIO.Implementation.FormulaUtil.ParseString(String strFormula, IWorksheet sheet, Hashtable hashWorksheetNames, Int32 iCellRow, Int32 iCellColumn, Boolean bR1C1)
at Syncfusion.XlsIO.Implementation.RangeImpl.SetFormula(String value, Hashtable hashWorksheetNames, Boolean bR1C1)
at Syncfusion.XlsIO.Implementation.RangeImpl.OnValueChanged(String old, String value)
at Syncfusion.XlsIO.Implementation.RangeImpl.set_Value(String value)
at Syncfusion.XlsIO.Implementation.RangeImpl.set_Formula(String value)
at PrEnq.Plugins.Standard.ProjectReport.Add80PercentGroup(IWorksheet ws, Int32 startRow, Int32 startCol, String name, Hashtable values) in C:\Prosjekter\ProPartner\PrEnq\StandardPlugins\ProjectReport.cs:line 185

What is wrong with my formula?

[Christian Rygg]

Just wanted to say that I have debugged the application, and the string which is written to the Formula-object is exactly as I pasted it in the first post - there is no logic error which makes it empty or anything like that :)

[Administrator]

In a "German Excel" you have to use
"=WENN(C6 = 0;0;E7/C6)"

Maybe you use the wrong separator!?
Try the ";" instead of ","...

[Christian Rygg]

No, already went down that path ;) Started with using ; (in another formula) as this is the separator I'm used to from Norwegian Excel - but it didn't work... With a , it worked just fine. So I don't think that is my problem :(

[Melba Winshia]

Hi Chris,

XlsIO don't support spaces inside formulas. After removing spaces, the formulas were parsed without any problems. Please don't use spaces inside the formulas. Here is the sample for your reference.

Sample:

Formula_String.zip

Please take a look at the sample above and let me know if you have any other question.

Thanks,
Melba

[Christian Rygg]

Thanks! That worked a charm! :)

[Melba Winshia]

Hi Chris,

Thanks for the update.Glad to know that it works fine.

Regards,
Melba