PHPExcel Worksheet!A2 -> Formula Error: Unexpected ,

Question

After few hours spend on the PHPExcel error Worksheet!A2 -> Formula Error: Unexpected , I found out that

$sheet->setCellValue($col . $row, $value);

throws me this error if I try to insert string =ER=, s.r.o. into the cell. As I removed first = sign it start to work. However weird it looks =ER= is the name of real company. And I want to write it as a string into the cell.

The question is which signs and how should be escaped to avoid this behavior of PHPExcel? I expected PHPExcel will escape this cases. Like whet I send data to DB...

Answer 1

You will need to start the cell with a single quote ' to avoid that leading equal sign being treated as the start of a sum. Use:

$sheet->setCellValue($col . $row, "'" . $value);

If the quote causes problems for other values like dates and number you can test $value to see if it starts with = and, if it does add the single quotes otherwise leave it alone.

$sheet->setCellValue($col . $row,(substr($value,0,1) == '=' ? "'" . $value : $value));