[XSL-LIST Mailing List Archive Home] [By Thread] [By Date] [Recent Entries] [Reply To This Message]

RE: Number formating (format-number) and string to num

Subject: RE: Number formating (format-number) and string to number function problem.
From: "Michael Kay" <mike@xxxxxxxxxxxx>
Date: Thu, 6 Jul 2006 16:41:13 +0100
string to num
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:  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

PURCHASE STYLUS STUDIO ONLINE TODAY!

Purchasing Stylus Studio from our online shop is Easy, Secure and Value Priced!

Buy Stylus Studio Now

Download The World's Best XML IDE!

Accelerate XML development with our award-winning XML IDE - Download a free trial today!

Don't miss another message! Subscribe to this list today.
Email
First Name
Last Name
Company
Subscribe in XML format
RSS 2.0
Atom 0.3
Site Map | Privacy Policy | Terms of Use | Trademarks
Free Stylus Studio XML Training:
W3C Member
Stylus Studio® and DataDirect XQuery ™are products from DataDirect Technologies, is a registered trademark of Progress Software Corporation, in the U.S. and other countries. © 2004-2013 All Rights Reserved.