Home > Software design >  Phpspreadsheet formula not accepted
Phpspreadsheet formula not accepted

Time:05-17

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.

CodePudding user response:

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, Cell\DataType::TYPE_FORMULA);
$sheet->setCellValue('A5', $formula2);

as you can see in the documentation use a , for separating operands, not a ;

  • Related