Description
This is:
- 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?
For the float property to not contain commas but a dot character instead.
What is the current behavior?
The float is formatted using the current PHP locale.
What are the steps to reproduce?
- Set the locale to something that doesn't use
.as the decimale separator. - Create a custom property of type float.
- Write the document to xlsx.
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();
setlocale(LC_ALL, 'fr_CA');
$spreadsheet->getProperties()->setCustomProperty('my_property_name', 3.01, 'f'); //name,val,type
$writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
$writer->save('test.xlsx');Opening the file gives you this:
What features do you think are causing the issue
- Reader
- Writer
- Styles
- Data Validations
- Formula Calculations
- Charts
- AutoFilter
- Form Elements
Does an issue affect all spreadsheet file formats? If not, which formats are affected?
I don't know, it affect xlsx...
Which versions of PhpSpreadsheet and PHP are affected?
I just updated to version 1.25.
Where's the problem?
It seems to be that when you use 'f' as the type, it casts the value to a float here:
Then when the document is written it uses an XML writer to write the float here:
The xml writer in PHP spreadsheet extends PHP's built-in writer, and if you look at the parameters here you can see that the writeElement method accepts a string type as a value:
https://www.php.net/manual/en/xmlwriter.writeelement.php
When you cast a float to a string, PHP uses the locale to format it: https://stackoverflow.com/questions/17587581/php-locale-dependent-float-to-string-cast
Excel doesn't like this. So it needs to be converted to a string and formatted manually to use a . character as the decimal separator before calling writeElement.