writing formulas to a spredsheet

Topics: Developer Forum
Sep 26, 2008 at 2: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);
$objPHPExcel->getActiveSheet()->getStyle($sIndex)->applyFromArray($aFormats['arbrnfp']);

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
Bob 
Developer
Sep 26, 2008 at 11: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 11:51 AM
Hi,

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;

$sCol='M';
$sIndex = $sCol . $iCount;
$objPHPExcel->getActiveSheet()->setCellValue($sIndex, $sFormula);
$objPHPExcel->getActiveSheet()->getStyle($sIndex)->applyFromArray($aFormats['arbrnfp']);

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
Bob

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

Kind Regards
Bob
Developer
Sep 29, 2008 at 9:36 PM
You are affected by this bug in PHPExcel 1.6.3
http://www.codeplex.com/PHPExcel/WorkItem/View.aspx?WorkItemId=7536

Please download latest source code and test again:
http://www.codeplex.com/PHPExcel/SourceControl/ListDownloadableCommits.aspx


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

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

Kind Regards
Bob