Crash while trying setting a cell the value "123456\n"

[giannign1]

This is:

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

What is the expected behavior?

$worksheet->setCellValueByColumnAndRow(1,1,"123456\n");
Should add the value "123456\n" (or better the number 123456) to the cell at indexes 1,1

What is the current behavior?

the DefaultValueBinder::dataTypeForValue matches (with the preg_match case) the value as a DataType::TYPE_NUMERIC and then the Cell->setValueExplicit crashes during the check if (is_string($pValue) && !is_numeric($pValue))

What are the steps to reproduce?

Try to set to a cell a value with the \n at the end, for example:
$worksheet = //Worksheet instance
$worksheet->setCellValueByColumnAndRow(1,1,"123456\n");

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

// add code that show the issue here...
$spreadsheet->getActiveSheet()->setCellValueByColumnAndRow(1,1,"123456\n");


### Which versions of PhpSpreadsheet and PHP are affected?
v1.12.0

[Vagir-dev]

Hi!
May I ask: why do you use "\n"?

[giannign1]

its a value setted custom by our users
i agree that maybe shouldn't be there.... but the lib shouldn't crash anyway, should it?

[Vagir-dev]

You right, it shouldn't.
I asked just to figure out how to help you...

[giannign1]

i cited two internal method
one (that who guess the type by the value), using theregex identify it as a number

the other one, who tries to set the value using an explicit type, doesn't recognize it as a number

IMHO the correct one is the second, so MAYBE you only need to check that regex

my two cents

(in my code i solved with a $value = trim($value, '\n') ... but this is only a case specific workaround 😅)

[Vagir-dev]

@giannign1, try this patch:

Index: src/PhpSpreadsheet/Cell/DefaultValueBinder.php
IDEA additional info:
Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
<+>UTF-8
===================================================================
--- src/PhpSpreadsheet/Cell/DefaultValueBinder.php	(revision 24be4824109f3d647c3d959075129e975530b072)
+++ src/PhpSpreadsheet/Cell/DefaultValueBinder.php	(date 1589910330697)
@@ -65,6 +65,8 @@
                 return DataType::TYPE_STRING;
             } elseif ((strpos($pValue, '.') === false) && ($pValue > PHP_INT_MAX)) {
                 return DataType::TYPE_STRING;
+            } elseif (!is_numeric($pValue)) {
+                return DataType::TYPE_STRING;
             }
 
             return DataType::TYPE_NUMERIC;

Does it solve the problem?

[Vagir-dev]

With this patch the result after run your code is:

A1 now contains string "123456" with the line break after it.

[giannign1]

Great, thank you!!

[giannign1]

when will the fix be online? :)

[Vagir-dev]

@giannign1, I don't know. I'm not a member of the PhpSpreadsheet development team. :)

[giannign1]

i was thinking of yes lol
So i should submit a PR with your commit?
Or will you do that?

[Vagir-dev]

I don't mind at all, if you'll do it. :)