Formatting general cell

[sjefke09]

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?

Do not format general cells.

What is the current behavior?

In some files, a cell with the format 'General' is formatted.

What are the steps to reproduce?

Some excel files, have named the format 'GENERAL' instead of 'General'. Because the compare in PhpSpreadsheet is case sensitive, it will be formatted. As result, the value is 'GENERAL' instead of the real cell value.
This occurs when the files are created with Apache OpenOffice instead of Microsoft Excel.

Current code [PhpSpreadsheet\Style\NumberFormat\Formatter.php on line 111]:

// For 'General' format code, we just pass the value although this is not entirely the way Excel does it,
// it seems to round numbers to a total of 10 digits.
if (($format === NumberFormat::FORMAT_GENERAL) || ($format === NumberFormat::FORMAT_TEXT)) {
     return $value;
}

Could be (working for us):

// For 'General' format code, we just pass the value although this is not entirely the way Excel does it,
// it seems to round numbers to a total of 10 digits.
if (strtolower($format) === strtolower(NumberFormat::FORMAT_GENERAL) || strtolower($format) === strtolower(NumberFormat::FORMAT_TEXT)) {
    return $value;
}

The same code could be found on PhpSpreadsheet\Style\NumberFormat\Formatter.php on line 168.
There might be some other places where this bug occurs (or maybe also with other formatting options?).

Which versions of PhpSpreadsheet and PHP are affected?

v1.18.0

[oleibman]

Can you upload a file that demonstrates this problem? I've used Apache OpenOffice to save a file, but I don't see GENERAL, in any configuration of lower/upper case,

[sjefke09]

passe partouts.xls
Sure, should have done this right away ;)

[oleibman]

I've downloaded your file. I don't see what the problem is. I was able to read it in using PhpSpreadsheet, and write it out (as both xlsx and xls) without seeing any difference between those and the original. What am I missing?

[sjefke09]

Did you open it first? Because when you open the file and save it, the problem is (most of the times) gone.
If you use the original file and use the toArray method for sheet 1, some values will be 'GENERAL' instead of the real value it should be. This is the code I use:

$excelFile = \PhpOffice\PhpSpreadsheet\IOFactory::load($filePath);
$sheet = $excelFile->getSheetByName('Blad1');
$array = $sheet->toArray();

echo $array[1][4];

The result should be '21', but I get 'GENERAL' as result.

[oleibman]

I do duplicate that result. Interesting. getValue and getCalculatedValue are returning the correct result, but getFormattedValue and toArray are not.

[PowerKiKi]

Fixed in 1.19.0