Free Support Forum - aspose.com

Correct Formula strings result in strange formula in excel

hi,

I have a problem setting a formula. The following generated formula is assigned to the formula property:
"=IF(62392<0;0;62392)“
In the end it results in a Invalid formula:”=IF(62392<0;0;62392) message from aspose.cell.

Another code I tried assigns the following string to the cell’s formula property: “=IF(62392,00<0;0;62392,00)”.
When opened in Excel the cell contains the following strange string: =IF(62392;0<‘0;0;62392’();0). Note the quotes and the brackets - don’t know where they come from.

I checked the strings. If I output the formulas as text into the cells - the formulas are correct.
When assigning the formula to the cell and I get the string value of that cell, the value seems to be correct. But after opening the file I always get a #Name? error.

The same happens with this: "=L340,3" results in =L34’0,3’(). I tried InvariantCulture and so on. But none of these have worked out so far.

I’ve used 4.0.3.5.
Could you help me and tell me what is wrong or what I am doing wrong?


Hi,

Thanks for considering Aspose.

You are using "," as decimal point. Aspose.Cells, By default use System separators with "." as decimal separator. Use "," comma instead of ";" in the formula.

I run the following code and it shows perfect results.

Workbook wkb = new Workbook();

Worksheet worksheet = wkb.Worksheets[0];

worksheet.Cells["C10"].Formula = "=IF(62392<0,0,62392)";

worksheet.Cells["C11"].Formula = "=IF(62392.01<0,0,62392.01)";

worksheet.Cells["L34"].PutValue(77);

worksheet.Cells["C12"].Formula = "=L34*0.3";

wkb.Save("d:\\testformulas1.xls",FileFormatType.Excel2003);

if you set your international setting of MS Excel like "," as Decimal separator etc. The result of above code will be shown as

62392
62392,01
23,1

Regards

Amjad Sahi

Aspose Nanjing Team

Thank you for your reply - I got it. I tried it with the separators and it works. But only if I don’t use decimal digits when they are “.00”. The following isn’t working:

Input string was not in a correct format."=IF(62392.00<0,0,62392.00)".

If there is not a .00 but a .01 decimal value, then it works.

Michael

Hi Michael,

Thanks for your report. We have found and fixed this problem. Please try this attached version.

thank you very much - it’s working.

Michael