防止PHPExcel在写入文件时计算值 [英] Prevent PHPExcel to calculate values when writing to file

问题描述

使用类PHPExcel将数据写入Excel文件中时.

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

这是在编写器工作时发生的，而不是在向单元格$this->sheet->setCellValue()

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

我从不使用功能PHPExcel_Cell->getCalculatedValue()写入Excel，而仅使用$this->sheet->setCellValue('A1', $value).

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

我不需要在Excel文件中计算任何公式.只是将DB2数据导出到该文件.

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

很明显，$value可能包含诸如= + - > <的公式字符(因为单元格AF20955确实导致了>的致命错误)，但这不应解释为公式，而只能解释为字符串的一部分.我该如何解决这个问题?

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?

推荐答案

默认情况下，保存时会调用PHPExcel计算引擎，但是您可以告诉编写者不要通过调用来应用它

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

在调用保存之前

当您通过调用setCellValue()或类似方法设置单元格值时，PHPExcel将尝试识别数据类型，并使用值绑定器在内部进行适当设置(与MS Excel完全相同).

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.

如果要确保将它们视为字符串而不是公式，则最简单的方法是使用setCellValueExplicit().

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

这篇关于防止PHPExcel在写入文件时计算值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！