Problem with formula

Topics: Developer Forum, User Forum
Aug 5, 2008 at 11:24 AM
Hello People,

When i run this line:
$objPHPExcel->getActiveSheet()->setCellValue('M'.$lastRow,"=SUMIF(M3:M".($lastRow-1).";\"<0\")");

The generated excelsheet is corrupted. When you open the generated excel file, excel offers to fix the problem. This results in an empty cell.

The formula on itself looks like this: =SUMIF(M3:M5;"<0")

In the original excelsheet that i need to reproduce with PHP the formula is in Dutch and looks like this: =SOM.ALS(N8:N16;"<0")

I tried adding the dot in between SUM and IF, but that doesn't help either.

Any suggestions?

Regards,

Sander
Developer
Aug 5, 2008 at 12:07 PM
One of your semicolons needs to be replaced by comma

Change this
$objPHPExcel->getActiveSheet()->setCellValue('M'.$lastRow,"=SUMIF(M3:M".($lastRow-1).";\"<0\")");

to this
$objPHPExcel->getActiveSheet()->setCellValue('M'.$lastRow,"=SUMIF(M3:M".($lastRow-1).",\"<0\")");

That is because you need to write the formula the way Excel stores it internally. When this kind of thing happens a good tip may be to first create the Excel sheet in Microsoft Office Excel 2007 or OpenOffice 3.0 (Beta), put in the formula from there, save it as xlsx, then read the spreadsheet using PHPExcel and echo the cell value with the formula. You can then see how it should actually be written.

Related thread:
http://www.codeplex.com/PHPExcel/Thread/View.aspx?ThreadId=23766
Aug 5, 2008 at 12:08 PM
Edited Aug 5, 2008 at 12:12 PM
After a trying and searching a few times. I found an answer to my own problem.

I had to replace the ; with ,  (for splitting the parameters in the formula).

Now the document is fully working.

edit:

Thanks koyama! ,

It is just like you said, just before i posted my reply.