PHPExcel：编写单元格数据类型问题时出现问题

Question

I have PHP code to export the data to excel. Used PHPExcel library for the same.

PHPExcel library Version 1.7.6

We encountered a problem while writing the following value ==PD==[HW]RECEIVING CRC ERRORS

When I open the Excel manually and set the cell data type as TEXT it is accepting this value.

But while trying to generate the excel using PHPExcel library, getting an error as below exception 'Exception' with message 'L14 -> Formula Error: Unexpected operator '=''

I tried to solve this issue by setting the data type of the cell as STRING, but no luck... Tried below ways to set the cell data type...

#first try
$activeSheet->setCellValueExplicit($symptomColumn.$rowCount, $val, PHPExcel_Cell_DataType::TYPE_STRING);

#second try
$activeSheet->getCell($symptomColumn.$rowCount)->setValueExplicit($val, PHPExcel_Cell_DataType::TYPE_STRING);

#third try
$activeSheet->getCell($symptomColumn.$rowCount)->setDataType(PHPExcel_Cell_DataType::TYPE_STRING);

#fourth try
$activeSheet->getStyle($symptomColumn.$rowCount)
                                    ->getNumberFormat()
                                    ->setFormatCode(
                                        PHPExcel_Style_NumberFormat::FORMAT_GENERAL
                                    );

#fifth try
$activeSheet->getStyle($symptomColumn.$rowCount)
                                    ->getNumberFormat()
                                    ->setFormatCode(
                                        PHPExcel_Style_NumberFormat::FORMAT_TEXT
                                    );

Can anyone please help me to resolve the issue while writing the text "==PD==[HW]RECEIVING CRC ERRORS" to the cell while creating an excel using PHPExcel library?

Thanks in Advance...

Answer 1

If PHPExcel encounters a cell where the first character of content is an =, then it considers that cell to contain an Excel formula, and will try to evaluate it as such. If it isn't actually a formula, then it will throw an exception like this. There is no simple solution in PHPExcel, other than to suggest that you add a leading space (or other character) before the =. PHPExcel is no longer a supported library, and this bug will not be fixed (especially not in the older version that you are running).

The latest master branch of the successor library PHPSpreadsheet does contain a fix for this, allowing you to set the style of the cell to quoted text. This will identify that this is not a formula to the calculation engine, and is similar to what you are recommended to do in MS Excel itself.

$workSheet->setCellValue('A2', "==PD==[HW]RECEIVING CRC ERRORS");
$workSheet->getCell('A2')->getStyle()->setQuotePrefix(true);