Prevent PHPExcel to calculate values when writing to file

Question

When writing my data in an Excel file with class PHPExcel.

This is happening when the writer works, not when adding data to the cells $this->sheet->setCellValue()

Fatal error: Uncaught exception 'PHPExcel_Calculation_Exception' with message 'Adressen!AF20955 -> Formula Error: Unexpected operator '>'' in \Cell.php:300 Stack trace: 
#0 \PHPExcel\Worksheet.php(754): PHPExcel_Cell->getCalculatedValue()
#1 \PHPExcel\Writer\Excel2007\Worksheet.php(373): PHPExcel_Worksheet->calculateColumnWidths()
#2 \PHPExcel\Writer\Excel2007\Worksheet.php(80): PHPExcel_Writer_Excel2007_Worksheet->_writeCols(Object(PHPExcel_Shared_XMLWriter), Object(PHPExcel_Worksheet))
#3 \PHPExcel\Writer\Excel2007.php(304): PHPExcel_Writer_Excel2007_Worksheet->writeWorksheet(Object(PHPExcel_Worksheet), Array, false)
#4 \excel.php(131): PHPExcel_Writer_Excel2007->save('...')
#5 \excel. in \PHPExcel\Cell.php on line 300

I never use the function PHPExcel_Cell->getCalculatedValue() writing to my Excel but only $this->sheet->setCellValue('A1', $value).

I don't need to calculate any formula in my Excel file. Just exporting DB2 data to this file.

It is obviously possible that $value contains formula characters like = + - > < (as the cell AF20955 did causing the fatal error with >) but this should not be interpreted as formula but only as part of string. How can I solve the problem?

No idea, it is a database field, very hard to find out what it is exactly.... If it's needed I can search for the value — Perocat, Oct 30 '15 at 15:16
then try/catch the write call and have the catch spit out whatever `$value` was at that point. — Marc B, Oct 30 '15 at 15:17
`try { $this->sheet->setCellValue('A1', $value) } catch (exception $e) { die("setCell barfed with value: $value"); }` — Marc B, Oct 30 '15 at 15:18
As written in my question: This is happening when the writer works, not when adding data to the cells `$this->sheet->setCellValue()` — Perocat, Oct 30 '15 at 15:28

score 23 · Accepted Answer · answered Oct 30 '15 at 18:45

23

The PHPExcel calculation engine is called by default when you save, but you can tell the writer not to apply it by calling

$objWriter->setPreCalculateFormulas(false);

before the call to save

When you set a cell value with a call to setCellValue() or similar, PHPExcel will attempt to identify the datatype and set it appropriately internally (in exactly the same way as MS Excel) using the value binder. Cells that contain a = as the first character will be considered as formulae by this.

If you want to ensure that they are treated as strings instead of formulae, then the easiest approach is to use setCellValueExplicit() instead.

answered Oct 30 '15 at 18:45

Mark Baker

209,507
32
346
385

1

Thank you! Using `setCellValueExplicit()` is working well. I also tried using only `$objWriter->setPreCalculateFormulas(false);` but the error persist! – Perocat Oct 30 '15 at 19:16
3

If you're using autowidth columns, then PHPExcel needs to execute any formulae, even if setPreCalculateFormulas is set to false, in order to calculate the column width because that's based on the actual formatted, displayed value for the cell – Mark Baker Oct 31 '15 at 08:49
Thanks man! That solved all my problems while using a xls model file. – Victor Moura Nov 11 '15 at 19:16
If you are using LaravelExcel, this might help: https://github.com/Maatwebsite/Laravel-Excel/pull/708 – chiliNUT Jan 19 '18 at 00:03

Prevent PHPExcel to calculate values when writing to file

1 Answers1

Linked