With phpspreadsheet, i can’t put a formula in a cell. I get an error : L43 -> Formula Error: An unexpected error occurred
While when i put this formula in an excel file directly it’s working nice.
My formula is : “=IF(SUM(J2:J41)<>SUM(L2:L41);”1″;”2″)”.
There is my code :
$sheet->setCellValue($cell, $formula);
I also test with
$sheet->setCellValueExplicit($cell, $formula, DataType::TYPE_FORMULA);
But i doesn’t work too.
I was thinking that the error was because of the quotes in the formula so i add addslashes php function
$formula = addslashes("=IF(SUM(J2:J41)<>SUM(L2:L41);"1";"2")");
And i test with numbers
$formula = "=IF(SUM(J2:J41)<>SUM(L2:L41);1;2)";
When i went in the code source of the library, if found that the character which generate the error was the semicolon (;).
I don’t if the origine of the problem is my formula or the library.
Any help is welcome. Thank you.
Advertisement
Answer
hope that helps :https://phpspreadsheet.readthedocs.io/en/latest/topics/recipes/#write-a-formula-into-a-cell something what will do what you want:
$formula1 = "=IF(SUM(J2:J41)<>SUM(L2:L41),"1","2")";
$formula2 = "=IF(SUM(J2:J41)<>SUM(L2:L41),1,2)";
$sheet->setCellValueExplicit('A4', $formula2, CellDataType::TYPE_FORMULA);
$sheet->setCellValue('A5', $formula2);
as you can see in the documentation use a , for separating operands, not a ;