PHPexcel formula IFERROR

问题:

I'm using PHP excel 1.8.0. I have searched everywhere but I don't understand why following error occures and my excel file is corrupted.

Uncaught exception 'PHPExcel_Calculation_Exception' with message 'Worksheet!K2 -> Formula Error: An unexpected error occured'

I have to use ISO 8859-2 encoding

$formula = iconv('ISO-8859-2', 'UTF-8//IGNORE//TRANSLIT', '=IFERROR(IF(OR(C'.$row.'="string1";C'.$row.'="strin2");D'.$row.';" ");" ")');
$objPHPExcel->getActiveSheet()->setCellValue($where, $formula);

I tried setting formula without =. After that when I open my excel file all formulas are set properly without = mark. Then if I add it manually to the formula, it works. But with code like you can see it, formulas are broken. I have Also tried adding = after I used iconv, but nothing has changed file was still broken. I would be glad if someone could help me with this problem.

回答1:

From the PHPExcel documentation:

Write a formula into a cell

Inside the Excel file, formulas are always stored as they would appear in an English version of Microsoft Office Excel, and PHPExcel handles all formulae internally in this format. This means that the following rules hold:

• Decimal separator is '.' (period)
• Function argument separator is ',' (comma)
• Matrix row separator is ';' (semicolon)
• English function names must be used

This is regardless of which language version of Microsoft Office Excel may have been used to create the Excel file.

You're using a semi-colon (;) as a function argument separator

回答2:

If you get problem in working of IFERROR while export then you can use

$sheet->setCellValue($cell_name, "=IF(ISERROR($val1*$val2),0,$val1*$val2)");

It works for me :)