473,597 Members | 2,113 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

strange number format

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
4 2016
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.publi c.excel or better yet
microsoft.publi c.nl.office.exc el (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

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.comwro te in message
news:76******** *************** ***@news2.tudel ft.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.publi c.excel or better yet
microsoft.publi c.nl.office.exc el (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_LAN GUAGE 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
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

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
2928
by: martin.mrazek | last post by:
Hello, I wanted to install Number::Format module bud ended up with following error message. Does anybody have any suggestion what the cause could be?? Could that be due to presence of perldl in my comp? Martin # make cp Format.pm blib/lib/Number/Format.pm Manifying blib/man3/Number::Format.3pm
3
2422
by: John H. | last post by:
Hi, Why if set a DataTable in Crystal Report Net Table with method SetDataSource, 'ReportDocument.SetDataSource(oDataTable)', lost the Number and Currency format with the decimal digits and Currency symbol?
0
1094
by: moondog | last post by:
I would like my project to have its version numbers in the 1.2.3.4 format; however, whenever I change the "version" property in my setup deployment project I get this error "C:\myprogram\Setup_Projects\xyz\Setup - xyz.vdproj Invalid product version '1.2.3.4'. Must be of format '##.##.####' It does accept a version number with only two decimal points: 1.2.34 However, my company would like to see a four number format version number:...
0
1834
by: richasaraf | last post by:
Hi all, Please help me out... i want to convert the to_char into number format.... please let em know how to do it... for eg: i have a statement : to_char(p_number, 'S000000') this converts the number into proper format.... now i want to convert tht 'S000000' char back to the original number p_number..... Can anyone tell me how to do it please :confused: Thanks a lot
1
1256
by: ADA | last post by:
Hi all, I have a funny problem. I have a function in Windows Application, which builds dataset in run time, adds data into the dataset and returns XML. One of dataset columns has System.Double type. If I try to insert into this column number in format "###0.00", in the output XML I'll recieve for example "123.45". It works fine. But, if I try to run the same code from the webmethod , I recieve error number format (without "."), for...
0
2498
by: Wayne | last post by:
I am charting data from a query that consists of a Date/Time field and a Number field. The Date/Time field is the x scale on my chart. Sometimes data is collected from different times during the one day. I need to be able to see these times as different time points on the x scale of my chart. To accomplish this I am using the following statement as the row source for my chart: "SELECT (Format(,"DDDDD HAMPM")),Max() AS FROM GROUP BY...
10
13706
by: Dixie | last post by:
I am appending some new fields to a table in vba and when I append a number field with is a byte, it does not inherit any format. I want it to be the General Number format, but it is blank. I have tried to change the format with the following code, but it does not work. What is wrong with this code and how can I make that byte number field have the General Number format? Call...
6
2915
by: psbasha | last post by:
I have a input format XX:YY:ZZ and the start value may be 'n' digit numbers for X,Y,Z. So I have to assign the numbers based on the above format ( Format may change) Say Example - 1 Inputs :
1
4582
by: michaelw118 | last post by:
Hi, I hope someone can give me some solutions to these problems I faced. I imported some data to Excel. Then in Access, I table-linked to this file. I have two fields which in Excel is in number format but in Access, these fields property is text and the values in these fields become "#Num!". Others number format fields were okay, only these two fields (or particular columns?). But if I linked directly to the datasource, it seems the...
0
7969
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
7886
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
8272
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
8035
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
1
5847
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
3886
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
3927
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1494
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
1238
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.