Formulas not working due to precalculateformulas

Topics: Developer Forum, User Forum
Aug 24, 2007 at 3:34 PM
I've spend most of my day scratching my head because a very simple =SUM() consistently came out a 0. Eventually I drilled down to Excell2007/Worksheet.php#_writeCell which if the cell is a formular as of 1.4.5 now checks if getPreCalculateFormulas() comes back true (which it always will by default as no setting is otherwise performed) and then stored the precalculated value as the value of the cell (which is always 0).

Removing this to the functionality of 1.4.0 resolves the problem although it does cause a a "save changes?" dialog when you close the file in excel. But at least my =SUM() works again.
Coordinator
Aug 27, 2007 at 4:17 PM
You can setPreCalculateFormulas(false) to mimic 1.4.0 behaviour.

This option has been added because:
- Excel2007 behaves correctly and re-calculates formulas on opening
- Compatibility pack does not... It only does when adding dummy data into formula precalculated value, as we did untill 1.4.0

Regards,
Maarten
Aug 30, 2007 at 7:36 PM
First of all thank you, this is a great work you are doing!

I use formulas that contain references to cells in other sheets, using Excel 2003 and the Compatibility Pack.
Version 1.4.0 produced a document with formula re-calculation on opening. To have 1.4.5 produce the same I had to apply 2 changes:

  • in Writer/Excel2007/Worksheet.php comment out line 784 --> $objWriter->writeElement('v', '0'); This ensures that when using setPreCalculateFormulas(false); the same behaviour as 1.4.0 is preserved i.e. don't add a <v>0</v> tag.

  • in Writer/Excel2007/Workbook.php comment out line 87 --> $this->_writeBookViews($objWriter, $pPHPExcel); Again this is required to reproduce the 1.4.0 bahaviour. Somehow adding the <workbookView> tag to workbook.xml is breaking the formula re-calculation on opening, It also has the effect of selecting all of the sheet tabs on opening and positioning on the last sheet. This is removed when commenting out the line.

Here is a sample code that generates a document that can reproduce the above:

set_include_path(get_include_path() . PATH_SEPARATOR . '../PHPExcell-1.4.5/Classes/');

include 'PHPExcel.php';
include 'PHPExcel/Writer/Excel2007.php';
$out_filename = "test.xlsx";
$objPHPExcel = new PHPExcel();

$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->setTitle('Foglio 1');
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, 1, 10);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, 1, 20);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2, 1, '=SUM(A1:B1)');
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2, 3, "='Foglio 2'!C1");

$objPHPExcel->createSheet();
$objPHPExcel->setActiveSheetIndex(1);
$objPHPExcel->getActiveSheet()->setTitle('Foglio 2');
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, 1, 30);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, 1, 40);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2, 1, '=SUM(A1:B1)');

$objPHPExcel->setActiveSheetIndex(0);

$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
$objWriter->setPreCalculateFormulas(false);
$objWriter->save($out_filename);
Coordinator
Aug 31, 2007 at 8:49 AM


francescom wrote:
  • in Writer/Excel2007/Worksheet.php comment out line 784 --> $objWriter->writeElement('v', '0'); This ensures that when using setPreCalculateFormulas(false); the same behaviour as 1.4.0 is preserved i.e. don't add a <v>0</v> tag.

  • in Writer/Excel2007/Workbook.php comment out line 87 --> $this->_writeBookViews($objWriter, $pPHPExcel); Again this is required to reproduce the 1.4.0 bahaviour. Somehow adding the <workbookView> tag to workbook.xml is breaking the formula re-calculation on opening, It also has the effect of selecting all of the sheet tabs on opening and positioning on the last sheet. This is removed when commenting out the line.

There you say it: these things are breaking formula re-calculation in Excel2003 and the compatibility pack. But, if you apply the changes you just did, formula re-calculation in Excel2007 is broken... Pity these two don't behave the same way!

Would it be useful to add a "behaviour" switch in PHPExcel? I.e. you can create OpenXML for Excel2007 and for Excel2003 compatibility pack by setting a boolean switch?
Aug 31, 2007 at 9:51 AM
I checked now and my test document above (after applying my 2 changes) recalculates on opening also with Excel2007. But of course I didn't try with more complex documents. So yes, if we come to a point where we see a real incompatibility between Excel2007 and compatibility pack a behaviour switch would be useful.
Coordinator
Aug 31, 2007 at 11:04 AM
This discussion has been copied to a work item. Click here to go to the work item and continue the discussion.