Stop excel changing the value of numbers in european formt

S

stickleback.ne

Hi,

I'm generating an excel report using xml transformed by xslt into html
(but saved as a .xls file) that is sent out to a large number of my
business's clients, mainly in Europe.The specification for the report
is that numbers should be shown in European format (. for thousand
seperators and , for decimal point). All was fine until I noticed
that one column of figures had a value that was sometimes was 10 times
larger than it should have been. After investigating further it
appears that if the number is greater than 1 with at least 4 decimal
places, excel treats the decimal point as a thousands seperator and
moves it.

E.g the correct value was 2,2567, excel turned it into 22,567

The easiest way to reproduce is to create an html document that just
shows the value 1,2345 & then drag it into excel.

I know I can change the international settings and all will then work
but because this is being sent out to many external people I want to
ensure they all see the correct value. How can I force excel to show
the number I want correctly?

Thanks for any help,

Stickleback
 
S

Sheeloo

The easiest way (and I believe the right way) is to create the Excel file in
the format of the location you are creating the Excel file in...

So if your system uses comma as thousand separator and dot as decimal then
use that... when any one with European settings (. for thousand seperators
and , for decimal point) opens the workbook, Excel will display the correct
value with the correct format...

If you are sending in XML then your definition should indicate the number
format so that any one reading it can apply appropriate transformations...
 
S

stickleback.ne

Thanks for the reply Sheeloo but unfortunately the people that have
requested this work always want the values shown in european format
regardless of how the actual user viewing the data has their excel set
up. So I suppose what I'm really asking for is 'is there a way of
flagging data in a html formatted file so that when it is opened by
excel it treats it as text (rather than a number) & displays the value
unaltered?' i.e. when opening a html document that contains

<HTML><HEAD><TITLE>test</TITLE></HEAD><BODY><table><tr><td>1,3456</
td></tr></table></BODY></HTML>

in Excel it shows 1,3456 rather than 13,456 regardless of the settings
in Excel.

Thanks,

Stickleback
 
S

stickleback.ne

Thanks for the reply Sheeloo but unfortunately the people that have
requested this work always want the values shown in european format
regardless of how the actual user viewing the data has their excel set
up. So I suppose what I'm really asking for is 'is there a way of
flagging data in a html formatted file so that when it is opened by
excel it treats it as text (rather than a number) & displays the value
unaltered?' i.e. when opening a html document that contains

<HTML><HEAD><TITLE>test</TITLE></HEAD><BODY><table><tr><td>1,3456</
td></tr></table></BODY></HTML>

in Excel it shows 1,3456 rather than 13,456 regardless of the settings
in Excel.

Thanks,

Stickleback
 
S

stickleback.ne

Just worked it out.

You need to include in the html the following style :-

<style>
..xl24
{mso-style-parent:style0;
mso-number-format:"\@";
white-space:normal;}
</style>

and then the data line should be changed to :-

<td class=xl24>1,3456</td>

I hope that helps someone in the future.

Stickleback
 
S

stickleback.ne

Just worked it out.

You need to include in the html the following style :-

<style>
..xl24
{mso-style-parent:style0;
mso-number-format:"\@";
white-space:normal;}
</style>

and then the data line should be changed to :-

<td class=xl24>1,3456</td>

I hope that helps someone in the future.

Stickleback
 
S

stickleback.ne

Just worked it out.

You need to include in the html the following style :-

<style>
..xl24
{mso-style-parent:style0;
mso-number-format:"\@";
white-space:normal;}
</style>

and then the data line should be changed to :-

<td class=xl24>1,3456</td>

I hope that helps someone in the future.

Stickleback
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Numbers changing between sheets 13
add rounded numbers using the rounded value 2
Addition in Excel 2
Fixed Decimals in Excel 2007 4
To stop the rounding of numbers 1
Stop Excel 2007 from Changing Column Widths 4
Import of text file - numbers destroyed! 2
Excel Automatically Changing Decimals to Text as Default 4

Top