Refreshing spreadsheet

Topics: User Forum
Jun 4, 2010 at 10:14 AM
Edited Jun 4, 2010 at 10:16 AM


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.2-1ubuntu4.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);


//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);


//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__));

Jun 4, 2010 at 12:46 PM

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

Jun 4, 2010 at 1:56 PM

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?



Jun 4, 2010 at 7:41 PM
This discussion has been copied to a work item. Click here to go to the work item and continue the discussion.
Jun 4, 2010 at 7:45 PM

Quite correct.

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