PHPExcel with operators

2
votes

I am using PHPExcel library: https://github.com/PHPOffice/PHPExcel

I whant to set a formula for the C1 cell:

$objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A1', 5)
            ->setCellValue('B1', 6)
            ->setCellValue('C1', "=SUM(A1,B1)");

That works fine.

Once I change the formula to one that has + or - operators, for example:

    ->setCellValue('C1', "=A1-B1");

instead of

    ->setCellValue('C1', "=SUM(A1,B1)");

I get the following error:

Illegal string offset 'value' in \phpexcel\PHPExcel\Calculation.php on line 2976

and then

Array to string conversion in \phpexcel\PHPExcel\Calculation.php on line 3010

Looking at the Calculation.php code - I find out that it cuts the references A1 and B1 to A and B.

Any idea how to fix it? Thank you!

  • Remark1: Looks like it doesn't matter but I prefer to mention that I have a spanish version of Excel installed on the computer.

  • Remark2: In the examples I found on PHPExcel GitHub all the formulas don't have operators, only functions are used.

2
phpphpexcel
Unable to replicate: I've just tested against version 1.7.0 through 1.7.9 using your example. Is your formula really as simple as this? Can you identify where the calculation engine cuts these values from A1 and B1 to A and B? - Mark Baker
05FeatureDemo contains a number of formulas using operators, including =E11+E12 as well as =E11*0.21 and more complex formulas with operators such as =IF(D6<>"",C6*D6,"") - Mark Baker
What version of PHPExcel are you using? line 2976 of Calculation.php in version 1.79 should only be accessed if you've specified a cell range (e.g. A1:B1), but doesn't access value in any way (although line 2974 does) - Mark Baker
Concerning your last comment - that happens because of cutting the row number A1->A and then it's a cell range (A - means the A column) - Alexander
Updating to the current version solved the problem. Thank you :). Do you know where can I find the version number? In PHPExcel.php file there is no number, just: @version ##VERSION##, ##DATE## - Alexander

2 Answers

0
votes

I encountered the exact same error trying to use any math operators such as addition, multiplication, division, or subtraction using PHPExcel version 1.7.6. I upgrading to the latest version fixed the problem for me.

0
votes

I know that's more than two years ago but i have the same error (but just the first, with "...line 2976") and find something for you

Any idea how to fix it?

@Alexander @Mark Baker For those who can't update, or for Mark Baker, it seems to work with "(" and ")"

So like this :

->setCellValue('C1', "=(A1-B1)");