Number format error

[TifionMitquos]

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?

Write decimal numbers like "1201,23" on a table.

What is the current behavior?

The number is writen like "1201.23.0" and was formated as string

What are the steps to reproduce?

This error occurs only on countries that use "," (comma) as a decimal separator (onlu to wtite a .xlsx file).
When the version 1.10.1 was writen, a decimal problem with a "." (dot) as a separator was solved (at PhpSpreadsheet/Writer/Xlsx/Worksheet.php - line 1146) but at countries that use a "," (comma - like Brazil or some european countries), the decimal number comes with a wrong formatation.
To solve, I had to use the 1.10.0 version on my composer file.

To replicate the bug, set "," (comma) as a decimal separator and "." (dot) as thousand separator on your OS

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 ->setCellValue('1201,2300'); //formated as number

Other error like this:
https://stackoverflow.com/questions/59393839/number-formatting-phpspreadsheet-with-decimals-not-seen-as-numbers-by-the-export

Which versions of PhpSpreadsheet and PHP are affected?

PhpSpreadsheet: 1.10.1

[rkyoku]

I can confirm I experiment the same bug since I updated the lib.

However, I do encounter said bug only when I pass in a float value to setCellValueByColumnAndRow.

To fix this (until you do), I do this (convert float to string):

$oSheet->setCellValueByColumnAndRow($iCol, $iRow, strval($fVal));

But I am not sure this is the best way to do it... Well, at least it fixes the bug.

[rkyoku]

EDIT: actually it does not fix everything. Now the numbers are converted to strings in Excel, and I can no longer sum them or whatever, and the numberformat I am using has no effect:

// FIXME Convert float to string because of bug in PhpSpreadsheet
$oSheet->setCellValueByColumnAndRow($iCol, $iRow, strval($fVal));
// No longer has any effect
$oFormat
   ->getNumberFormat()
   ->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_CURRENCY_EUR_SIMPLE)
;

[rkyoku]

The only solution is to downgrade to 1.10.0

[LinkingYou]

We have the same problem here. With 1.10.0 it works without any problems.

[niekkouwenberg]

This issue is caused by #1262

In the original code there is a str_replace:

// force point as decimal separator in case current locale uses comma
$objWriter->writeElement('v', str_replace(',', '.', $cellValue));

But in the pull request, some code was added before this comma-to-point translation, forcing a ".0" after the value:

if (strpos($cellValue, '.') === false) {
    $cellValue = $cellValue . '.0';
}

Apart from this being very very weird code. The force decimal should be added after converting commas's.

[niekkouwenberg]

Duplicate of #1282. In the following commit this was already fixed: d90ba7a

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs.
If this is still an issue for you, please try to help by debugging it further and sharing your results.
Thank you for your contributions.

[oleibman]

Fixed by PR #1282 in January 2020.