Should not automatically set cell datatype to formula when string starts with equal sign

[niekkouwenberg]

This is:

• [x] a bug report
• [ ] a feature request
• [x] not a usage question (ask them on https://stackoverflow.com/questions/tagged/phpspreadsheet or https://gitter.im/PHPOffice/PhpSpreadsheet)

What is the expected behavior?

Values starting with an equal sign are added as a string in the cell and not automatically converted to a formula.

When you want to create a formula, please use PhpOffice\PhpSpreadsheet\Cell::setValueExplicit()

This would be a breaking change.

What is the current behavior?

Values starting with an equal sign are automatically converted to formulas.

When user input is inserted into the speadsheet, this causes an Exception:

PhpOffice\PhpSpreadsheet\Calculation\Exception: Worksheet!A1 -> internal error in vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Cell/Cell.php:274
Stack trace:
#0 vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Writer/Xlsx/Worksheet.php(1070): PhpOffice\PhpSpreadsheet\Cell\Cell->getCalculatedValue()
#1 vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Writer/Xlsx/Worksheet.php(1020): PhpOffice\PhpSpreadsheet\Writer\Xlsx\Worksheet->writeCell(Object(PhpOffice\PhpSpreadsheet\Shared\XMLWriter), Object(PhpOffice\PhpSpreadsheet\Worksheet\Worksheet), 'A1', Array)
#2 vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Writer/Xlsx/Worksheet.php(76): PhpOffice\PhpSpreadsheet\Writer\Xlsx\Worksheet->writeSheetData(Object(PhpOffice\PhpSpreadsheet\Shared\XMLWriter), Object(PhpOffice\PhpSpreadsheet\Worksheet\Worksheet), Array)
#3 vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Writer/Xlsx.php(281): PhpOffice\PhpSpreadsheet\Writer\Xlsx\Worksheet->writeWorksheet(Object(PhpOffice\PhpSpreadsheet\Worksheet\Worksheet), Array, false)
[...]

What are the steps to reproduce?

Please provide a Minimal, Complete, and Verifiable example of code that exhibits the issue without relying on an external Excel file or a web server:

<?php

require __DIR__ . '/vendor/autoload.php';

// Create new Spreadsheet object
$spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();

// Set a text value starting with an equal sign
$spreadsheet->getActiveSheet()->setCellValue('A1', '=Lorem ipsum');

// Create an XLSX file
$writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
$writer->save(tempnam(sys_get_temp_dir(), 'phpxltmp'));

Which versions of PhpSpreadsheet and PHP are affected?

All.

Taken from PHPExcel issue: https://github.com/PHPOffice/PHPExcel/issues/344, reported by @remyw.

[MarkBaker]

Then you should explicitly set it as a string, rather than forcing PhpSpreadsheet to guess....

$spreadsheet->getActiveSheet()->setCellValueExplicit('A1', '=Lorem ipsum');

The current behaviour mimics the behaviour in the Excel GUI

This is quite explicitly stated in the documentation:

By default, when you call the worksheet's setCellValue() method or the cell's setValue() method, PhpSpreadsheet will use the appropriate datatype for PHP nulls, booleans, floats or integers; or cast any string data value that you pass to the method into the most appropriate datatype, so numeric strings will be cast to numbers, while string values beginning with = will be converted to a formula. Strings that aren't numeric, or that don't begin with a leading = will be treated as genuine string values.

[kczx3]

@MarkBaker would you possibly consider adding a flag to \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet->fromArray() to flag that all values should be set explicitly to avoid conversion to a formula?

EDIT: Nevermind. I sometimes struggle finding what I need in the documentation but I think I found what I am after. Using Value Binders to Facilitate Date Entry seems to have what I need. I can use the StringValueBinder class.

Modified above edit to correct "StringBuilder" to be "StringValueBinder"

[MarkBaker]

@MarkBaker would you possibly consider adding a flag to \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet->fromArray() to flag that all values should be set explicitly to avoid conversion to a formula?

No. If you want to force values that begin with a = as a string, then use a custom Value Binder