RE: [xsl] Number formating (format-number) and string to number function problem.

Subject: RE: [xsl] Number formating (format-number) and string to number function problem.
From: "Michael Kay" <mike@xxxxxxxxxxxx>
Date: Thu, 6 Jul 2006 16:41:13 +0100
As far as I can see, Excel expects to see numeric cells formatted something
like this:

<Cell ss:StyleID="s50"><Data
ss:Type="Number">4.0582000000000003</Data></Cell> 

I suspect you are trying to format the number in the way it appears on the
screen when Excel displays the spreadsheet. That's determined by format and
style information, not by the number itself. To generate the number, I think
the standard XSLT number-to-string conversion will almost certainly work
fine. I've no idea how you generate style information in the XML, but
looking at XML output by Excel will give you some clues. If all else fails,
there's probably some documentation somewhere.

Michael Kay
http://www.saxonica.com/


> -----Original Message-----
> From: Karen Yang [mailto:kyang94@xxxxxxxxx] 
> Sent: 06 July 2006 16:01
> To: xsl-list@xxxxxxxxxxxxxxxxxxxxxx
> Subject: [xsl] Number formating (format-number) and string to 
> number function problem.
> 
> Hello,
> 
> I'm converting the XML to Excel using XSLT 1.0, and now I'm 
> having a problem with the number result in Excel.
> 
> For example, I have a number stored as 1234.5678, and I'd like to see
> 1,234.57 as the final format, so I used format-number() 
> function, but that function would convert the result to 
> string instead of a number, so when I use <Cell><Data 
> ss:Type="Number"><xsl:value-of select="format-number(., 
> '###,.00')"/></Data></Cell>
> 
> The generated Excel would have problem opening, but if I change to:
> 
> <Cell><Data ss:Type="String"><xsl:value-of 
> select="format-number(., '###,.00')"/></Data></Cell>
> 
> The file could be opened, but all the numbers are stored as 
> string, so there would be a warning sign (little triangle on 
> the left upper
> corner) showed up in every cell that is number value but string type.
> 
> I also tried to use "number()" function as shown below, but 
> it's very strange that the result would be "NaN" for most values.
> 
> <Cell><Data ss:Type="String"><xsl:value-of 
> select="number(format-number(., '###,.00'))"/></Data></Cell>
> 
> So can sb. let me know what I should do in this situation? My 
> main goal is to have the number formated as ###..00.
> 
> And if I don't format the numbers,
> <Cell><Data ss:Type="Number"><xsl:value-of select="."/></Data></Cell>
> 
> It would work just fine....
> 
> Since I'd like to wrap my current project up by today, I'd 
> appreciate any of your outputs.
> 
> Thanks in advance for your help,
> 
> Karen.

Current Thread