CODEWITHSUNDEEP
phpphpspreadsheet
marty90
marty90

Reputation: 103

Number formatting phpspreadsheet with decimals not seen as numbers by the exported xlsx

I have a page with a simle overview of rows with the costs of flights, hotel nights and a total, and it adds a export in xlsx with phpspreadsheet. The costs need to be shown in euros, in the format: 

€ 1120,46

Or

€ 1.120,46

Eitherway would be ok.

The values of the flight costs are variable and are coming from the database, as decimal(6,2), from earlier user input. The hotel costs are a set amount (76) * number of days (based on flight dates).

I'm also using the sum function in the spreadsheet, to add the totals of the colums, but the rest of the calucations is done via PHP and added as a value for the cells.

For the cells with the numbers, using: 

...->setFormatCode(PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_CURRENCY_EUR);

or the 'FORMAT_CURRENCY_EUR_SIMPLE', have the same results, but will the €-sign behind the number, not what I need. So I'm using this:

...->setFormatCode('[$€ ]#,##0.00_-');

In the xlsx export it works great for numbers without decimal. The numbers show correctly and the sum function also works, including the €-sign at the right place.

But when the numbers contain decimals, it stops working like I want.

If I add a number like '120.45', the xlsx export shows '120.45.0' and Excel doesn't report any problem plus the sum function doesn't calculate anything.

If I add a number like '120,45, the xlsx export shows '120,45', but Excel reports that it is not a number and the sum function doesn't calculate anything. You can then click the warning triangle in Excel and make it a number. At this point it the value is added to the total by the sum function.

Both options also failing to add the €-sign.

I tried adding the numbers using these types of formatting on the PHP side:

$sheet->setCellValue('S5, number_format ($price, 2, ",", ""));

$sheet->setCellValue('S5, number_format ($price, 2, ".", ""));

$sheet->setCellValue('S5, str_replace(',','.',$price));

$sheet->setCellValue('S5, str_replace('.',',',$price));

This yielded the same results as above for the decimal values, and 'number_format(...)' made the non-decimal values also as values, so non of the numbers and sum functions were working properly.

Any idea's what this might be?

Upvotes: 0

Views: 5515

Answers (1)

Rafa Marques
Rafa Marques

Reputation: 36

I'm using the same api (phpspreadsheet) and have this problem to. I found a report on developers page and the correction that they made. The issue was to solve a decimal problem in countries that work with a "."(dot) as a decimal separation. For us, the correction just caused more problems.

My solution is to set the last stable version (1.10.0) before the last correction at this moment (1.10.1).

I just opened a issue report and now I'm waiting the answer.

Upvotes: 2

Related Questions