Jun 4, 2010 at 11:14 AM
Edited Jun 4, 2010 at 11:16 AM

Hi,
Im using PHPExel as an in memory spreadsheet but cant seem to get it to update itself and would be grateful for any help or advice.
When I create the sheet I populate it with some data, then receive more data and modify the sheet. The thing is that the calculations in the sheet dont seem to be updating, however when i output the sheet to an xls file they work fine. I was wondering if
there was a method that I havent found to force the spreadsheet to update? Ive tried turning the calculation caching off but it doesnt seem to help.
Im using PHPExcel 1.7.3c, PHP 5.3.21ubuntu4.2 and ubuntu 10.04
Thanks :)
A pared down version of my code looks like this, its actually quite a bit longer but these are (I hope) the pertinent bits:
//call function in my code to go and create basic phpexcel spreadsheet
function make_spreadsheet(){
$excel = new PHPExcel();
//add some vales and formulae
$excel>getActiveSheet()>setCellValue('B1', $cell>value);
...
PHPExcel_Calculation::getInstance()>clearCalculationCache();
return($excel);
}
//function where I add more to the sheet
function use_spreadsheet(){
//get basic spreadhseet
$excel = make_spreadsheet();
//add more numbers to spreadsheet
$excel>getActiveSheet()>setCellValue('B2', $cell>value);
...
PHPExcel_Calculation::getInstance()>clearCalculationCache();
//get a calculated answer
$calcAnswer = $excel>getActiveSheet()>getCellByColumnAndRow($c, $r)>getCalculatedValue();
// $calcAnswer is incorrect  not been updated
//using the following the spreadsheet outputs correctly
$objWriter = new PHPExcel_Writer_EXCEL5($excel);
$objWriter>save(str_replace('.php', 'grading.xls', __FILE__));
}


Coordinator
Jun 4, 2010 at 1:46 PM

We really need more details of the data values, and the formulae that you're using to make any judgement on this.



Hi Mark,
Thanks for replying :) Sorry, i didnt mean to be cryptic! I figured there might be something obvious that i was missing. After someone else came and had a play with what I was working on the STDEV function seems to be giving me the problem. I
had been testing by calculating the standard deviation of 3 numbers that were the same, the number 3, in Excel this gives 0, in PHPExcel i get a #!DIV/0 error. Seems to be from line 1888 in function STDEV in Functions.php, which is an if block:
// Return
if (($aCount > 0) && ($returnValue > 0)) {
return sqrt($returnValue / $aCount);
}
I was wondering about changing it to be:
// Return
if (($aCount > 0) && ($returnValue >= 0)) {
return sqrt($returnValue / $aCount);
}
Im not sure if thats bad form in maths but its consistent with excel and open office?
Steve


Coordinator
Jun 4, 2010 at 8:41 PM

This discussion has been copied to a work item. Click
here to go to the work item and continue the discussion.


Coordinator
Jun 4, 2010 at 8:45 PM

Quite correct.
The test against $returnValue should be >= rather than just > and this should apply to STDEVA, STDEVP and STDEVPA as well

