Problem using formula on PHPExcel (Excel2007)

Question

When I try to set a cell value using a formula, for example: setCellValue ('C1', '= A1 + B1'), the generated file does not have a calculated value for the cells.

I have the following script:

<?php

require_once '../vendor/autoload.php';
date_default_timezone_set('America/Sao_Paulo');

$objPHPExcel = new PHPExcel();

$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->setCellValue('A1', 5)
    ->setCellValue('B1', 10)
    ->setCellValue('C1', '=A1+B1');
echo $objPHPExcel->getActiveSheet()->getCell('C1')->getCalculatedValue() . PHP_EOL;

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('a.xlsx');

When I open a.xlsx with libreoffice, cell C1 shows the string "0". The strange thing is that when I click on a cell, it actually shows "= A1 + B1", but not the final result.

How to get PHPExcel to work with formulas correctly?

Answer 1

I had the same problem with LibreOffice 5.0.5.2.

For me, the following option has changed in LibreOffice Calc:

• /.../LibreOffice Calc/Formula
• " ", "Excel 2007 " " "

https://ask.libreoffice.org/en/question/12165/calc-auto-recalc-does-not-work/