CODEWITHSUNDEEP
phpphpexcel
Lucia
Lucia

Reputation: 4767

Php excel formula not running in saved spreadsheet

I'm using php excel codeplex, I'm filling a cell like this:

$objWorksheet->setCellValue('B12', "='Other sheet'!D38");

I save the file successfully, when I open it the formula is there, but it doesn't render the calculated value. If I copy and paste the formula to another cell it runs ok, so, it's not a problem of the formula's syntax. How can I force the formula to execute before saving? I have tried:

\PHPExcel_Calculation::getInstance($objPHPExcel)->disableCalculationCache();
\PHPExcel_Calculation::getInstance($objPHPExcel)->clearCalculationCache();

Without success...

Upvotes: 6

Views: 7386

Answers (3)

Deric Lima
Deric Lima

Reputation: 2092

For those using Laravel Excel, you can easily set up calculation during export in the excel.php config file. Just set the option calculate to true.

/*
|--------------------------------------------------------------------------
| Pre-calculate formulas during export
|--------------------------------------------------------------------------
*/
'calculate'                   => true,

Upvotes: 1

van hoai
van hoai

Reputation: 21

I added this code and it is working for me.

PHPExcel_Calculation::getInstance($excelObj)->disableCalculationCache();
PHPExcel_Calculation::getInstance($excelObj)->clearCalculationCache();
$writer = PHPExcel_IOFactory::createWriter($excelObj, 'Excel2007');

$writer->setPreCalculateFormulas(true);
$writer->save($output_file);

Upvotes: 2

Lucia
Lucia

Reputation: 4767

This is a sample code to solve the problem:

$spreadsheet = new \PHPExcel();
$writer = new \PHPExcel_Writer_Excel2007($spreadsheet);

//Do things with the $spreadsheet

//This is the solution, do it before saving
$writer->setPreCalculateFormulas(); 
$writer->save($saving_filepath);

Upvotes: 17

Related Questions