Home » File Formats » Spreadsheet_Excel_Writer » Bug #3148

[2005-01-10 11:48 UTC] bugs dot php dot net at chsc dot dk

Description:
------------
Calling $worksheet->write($row, $col, 1.2) will write "1,2" as a string instead of a number when the locale for LC_NUMERIC has been set to a locale that uses "," as a decimal seperator instead of "." (e.g. da_DK).

The method Spreadsheet_Excel_Writer_Worksheet::write() tries to determine whether the argument is a number by using preg_match(). However, preg_match() takes a string argument, so the float value is converted to a string. The string uses the decimal seperator specified by the current locale. If this is ",", the string does not match the regular expression used to determine whether the argument is a number.

Why not just use is_numeric() instead of preg_match()?

[2005-11-08 03:14 UTC] xnoguer at php dot net

I can't reproduce the bug. Calling
$worksheet->write($row, $col, 1.2)
works correctly for me. Do you have a sample script for the bug?

[2005-11-08 08:28 UTC] bugs dot php dot net at chsc dot dk

I can still reproduce it with PHP 5.0.5.

<?php

require_once 'Spreadsheet/Excel/Writer.php';

$workbook = new Spreadsheet_Excel_Writer();
$worksheet = $workbook->addWorksheet('Foo');

$row = 0;
$col = 0;
$worksheet->write($row++, $col, 1);
$worksheet->write($row++, $col, 1.2);
$worksheet->write($row++, $col, '1.3');
$worksheet->write($row++, $col, strftime('%B', 1));

setlocale(LC_ALL, 'da_DK');

$row = 0;
$col = 1;
$worksheet->write($row++, $col, 2);
$worksheet->write($row++, $col, 2.2);
$worksheet->write($row++, $col, '2.3');
$worksheet->write($row++, $col, strftime('%B', 1));

$workbook->send('foobar.xls');
$workbook->close();

?>

In the resulting Excel sheet, the cells in the range A1:B3 contain numbers except B2 that contains a string ("2,2"). This is easy to see because "2,2" is left-aligned and all the other numbers are right-aligned.

The cells containing strftime() are there so that you can verify that you have the da_DK locale installed. If da_DK is installed, A4 contains "January" and B4 contains "januar".

[2005-11-08 13:18 UTC] xnoguer at php dot net

It still works correctly for me, and seems to be using the correct locale (using php 4.3.8 and Spreadsheet_Excel_Writer 0.8). What version of the package are you using?

[2005-11-10 17:09 UTC] bugs dot php dot net at chsc dot dk

I am using Spreadsheet_Excel_Writer 0.8 and PHP 5.0.5.

Perhaps PHP 4 and PHP 5 are different.

Try the following code:

<?php
var_dump("a". 1.2);
var_dump(preg_match("/^([+-]?)(?=\d|\.\d)\d*(\.\d*)?([Ee]([+-]?\d+))?$/", 1.1));
setlocale(LC_ALL, 'da_DK');
var_dump("b". 3.4);
var_dump(preg_match("/^([+-]?)(?=\d|\.\d)\d*(\.\d*)?([Ee]([+-]?\d+))?$/", 1.1));
var_dump(strftime('%A', 300000));
?>

Here it outputs the following:

string(4) "a1.2"
int(1)
string(4) "b3,4"
int(0)
string(6) "søndag"

The regular expression is copied from Spreadsheet_Excel_Writer_Worksheet::write(). The 0 return in line 4 is the reason that write() doesn't work for me.

[2005-11-11 18:16 UTC] xnoguer at php dot net

Ok, I see the problem now. But is_numeric() won't work either. Try:

setlocale(LC_ALL, 'da_DK');
var_dump(is_numeric("1,2"));

I'll see what I can do.

[2005-11-11 20:29 UTC] bugs dot php dot net at chsc dot dk

As long as it works with $ws->write(1.2), it's fine with me.

I would never call write() with a localized string. If people do that, they are looking for trouble. But if you can make it work, then go ahead :-)