writing formulas to a spredsheet

Topics: Developer Forum
Sep 26, 2008 at 1:39 PM
Hi All,

I am new to php spredsheet engine/excel writer and have been working on a project to out put formated data to an excel spredsheet. I am also wrting formulas to calculate the difference between two cloumns.

At the moment I have the data out putting to columns correctly, but the column where I out put the formula is displaying the formula rather than the desired result. I have copied and pasted the code for wrting the formula below.

$objPHPExcel->getActiveSheet()->setCellValue($sIndex, $sFormula);

below is an example of the put, the final colum display the formula as a string rather than running it and displaying the result, how would I go about correcting this please. 

-7.67% -10.16% -1.12% =I6 - E6
21.58% 6.73% -6.41% =I7 - E7

Many Thanks in Advance for your help
Sep 26, 2008 at 10:34 PM
Hmm... this should really not be happening! I tried various test, but couldn't reproduce the bug. I'm not sure either whether you're using Excel2007 or Excel5 writer. Would it be possible for you to post a complete test script so this can be reproduced?
Sep 29, 2008 at 10:51 AM

I have been looking at this and found that the promblem is not the formula or the code that out puts the formula. The problem is with my formatting code. 

$sFormula = "=" .$currentNetSale ."-" .$oldNetSale;

$sIndex = $sCol . $iCount;
$objPHPExcel->getActiveSheet()->setCellValue($sIndex, $sFormula);

I am using formats from arrays held in a formating.php lib file. The code that calls the format for the cells in question is highlighted above and the code for the format is below. I have tracked down the problem to the final line of the format code. I can not see anything wrong with it and I am using this format in other columns without any issues, although those values are calculated by my code rather than a formula.

$aFormats['arbrnfp']['font']['name'] = 'tahoma';
$aFormats['arbrnfp']['font']['size'] = 10;
$aFormats['arbrnfp']['alignment']['horizontal'] = PHPExcel_Style_Alignment::HORIZONTAL_RIGHT;
$aFormats['arbrnfp']['borders']['right']['style'] = PHPExcel_Style_Border::BORDER_THIN;
$aFormats['arbrnfp']['numberformat']['code'] = '#,##0.00%;[Red]-#,##0.00%';

Many Thanks in Advance for your help

Sep 29, 2008 at 12:58 PM
forgot to add that I am using  Excel5 writer.

Kind Regards
Sep 29, 2008 at 8:36 PM
You are affected by this bug in PHPExcel 1.6.3

Please download latest source code and test again:

Sep 30, 2008 at 9:34 AM
Thanks again, 

I will download and update the source files and re-run my code.

Kind Regards