strange number format

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
Hi all,

I created a little button which when clicked will run a query on mysql db
and output the results as an excel spreadsheet.
I do this by setting the header as application excel. All works well except
for a very strange problem. Let's say a column should say 16500.22. When I
run it here from the uk, the cell will show 16500.22
When someone in Netherlands runs it, the cells shows 16,500,220,000

It looks like Excel is mixing the commas and dot somewhere along the line ?

Anyway know what I can do to keep my colleagues from holland happy ?

Many thanks


Re: strange number format

toffee wrote:
Quoted text here. Click to load it

Really as an Excel spreadsheet or a csv?

Quoted text here. Click to load it

Well, it has offcourse got to do with the fact that the dot is the
thousands-seperator, and the comma is the decimal seperator, at least here
in Holland :P.
As far as a Dutch version of Excel is concerned, an number formatted like
16500.22 is gibberish. I would have thought it would choose either to
display is as text, or as 165,000,220 (well, they'd see it as 165.000.220),
but where the extra thousand comes from I wouldn't know.

It offcourse has something to do with locale-settings. I have cracked this
one before, but fortunately for me, unfortunately for you, I'm no longer
forced to deal with MS Office, and I haven't got a clue wether is was Excel
or Windows that had to be pounded into shape.

Afaik when you import a csv instead of opening it just like an Excel
document, one can choose the decimal seperator BTW. Not very user friendely

- ask on microsoft.public.excel or better yet (as there obviously will be more dutch
aware of this issue)
- create a settings before the output how to format numbers (either in
dutch or reversed :-)

Nothing to do with PHP obviously.
Rik Wasmus

Re: strange number format

Excel spreadsheet not CSV

the extra thousand could be due to fact i have 4 decimals setup in mysql

is there a way in PHP to detect whether it's a dutch locale? maybe then i
could just format the numbers to strip the decimals

Quoted text here. Click to load it
Quoted text here. Click to load it

Re: strange number format

toffee wrote:
Quoted text here. Click to load it

PHP -> excel if not CSV is not very reliable. Allthough, for simple sheets
with only data it usually works fine.

BTW: Please use usenet as intended: indeed quote the (important part of)
message you are responding to, and put you answer _below_ it. Possibly
seperating lines as I'm doing now to respond to different parts.

Quoted text here. Click to load it

On the server, indeed, provided it's indeed configured as dutch. It's
highly dependant on the platform though, as it uses it's specific locales,
which are not an internal part of PHP. See the description and comments on . If it's configured to
use dutch numbers (or any numbers), you can check formatting of numbers
with localeconv(), and format your data accordingly.

If it's just one server, and you just want to know the visitors country,
possibilities to find out wether or not it's a cloggie are numerous allbeit
varying in reliability. You could check the HTTP_ACCEPT_LANGUAGE header for
instance, the dns they're coming from, something like GeoIP, or just ask

Quoted text here. Click to load it

A possibillity, but I doubt they'll be pleased with the loss of data :-)
Rik Wasmus

Re: strange number format

Hi toffee,

Quoted text here. Click to load it

Excel uses the "Regional and Language Settings" set up by Windows to
interpretate the given Value.

What do you use to create that excel spreadsheet?

You could try exporting a spreadsheet as you would like it to be -
including formulas and layout - as an html-file. If you now send this
file and set the Content-Type to excel this should work.

As far as I know this does not work with excel 95 and older, but it
should work with newer excel or

You may get td-elements like this:
<td class=xl76 align=right x:num="16500.22">16500.22</td>
the attribute x:num stores the original value with an point as the
decimal sign. The content of the td-element stores the representation
set in excel.


Site Timeline