Stop excel changing the value of numbers in european formt
 

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

Stop excel changing the value of numbers in european formt
 

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


" wrote:

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

Stop excel changing the value of numbers in european formt
 

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<TITLEtest</TITLE</HEAD<BODY<table<tr<td1,3456</
td</tr</table</BODY</HTML

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

Thanks,

Stickleback

Stop excel changing the value of numbers in european formt
 

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<TITLEtest</TITLE</HEAD<BODY<table<tr<td1,3456</
td</tr</table</BODY</HTML

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

Thanks,

Stickleback

Stop excel changing the value of numbers in european formt
 

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=xl241,3456</td

I hope that helps someone in the future.

Stickleback

Stop excel changing the value of numbers in european formt
 

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=xl241,3456</td

I hope that helps someone in the future.

Stickleback

Stop excel changing the value of numbers in european formt
 

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=xl241,3456</td

I hope that helps someone in the future.

Stickleback

Stop excel changing the value of numbers in european formt
 

Good that you got the solution...



" wrote:

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=xl241,3456</td

I hope that helps someone in the future.

Stickleback