Custom Property of type Float is improperly formatted when locale uses non-dot character to denote decimal place.

[adjenks]

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?

1. Set the locale to something that doesn't use . as the decimale separator.
2. Create a custom property of type float.
3. 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:

PhpSpreadsheet/src/PhpSpreadsheet/Document/Properties.php

Line 523 in 35b42cc

return (float) $propertyValue;

Then when the document is written it uses an XML writer to write the float here:

PhpSpreadsheet/src/PhpSpreadsheet/Writer/Xlsx/DocProps.php

Line 218 in 35b42cc

case Properties::PROPERTY_TYPE_FLOAT:

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.

[oleibman]

Are you using Php7.3/7.4? Php8 changed to use "Locale-independent float to string casting". So, if it is feasible for you to use Php8, that might eliminate your problem more quickly than waiting for a change to PhpSpreadsheet. (Which is not to say I won't investigate further.)

[adjenks]

Oh... That's news to me. Thanks for letting me know. I'm on 7.4 right now. We plan on switching to 8 soon anyhow.
I can manage for now by switching the property type to string and passing a string though.

[adjenks]

Good work! So fast!