strange number format

toffee

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

T

Dec 18 '06 #1

Rik

toffee wrote:

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.

Really as an Excel spreadsheet or a csv?

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 ?

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
though.

Either:
- ask on microsoft.public.excel or better yet
microsoft.public.nl.office.excel (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

Dec 18 '06 #2

toffee

Excel spreadsheet not CSV

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

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

"Rik" <lu************@hotmail.comwrote in message
news:76**************************@news2.tudelft.nl ...

toffee wrote:

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.

Really as an Excel spreadsheet or a csv?

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 ?

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

though.

Either:
- ask on microsoft.public.excel or better yet
microsoft.public.nl.office.excel (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

Dec 18 '06 #3

Rik

toffee wrote:

Excel spreadsheet not CSV

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.

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

is there a way in PHP to detect whether it's a dutch locale?

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
http://nl2.php.net/manual/en/function.setlocale.php. 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
them.

maybe
then i could just format the numbers to strip the decimals

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

Dec 18 '06 #4

Heiko Richler

Hi toffee,

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 ?

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 openoffice.org.

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.

Heiko

Dec 19 '06 #5