Formatting Excel cells using XSL

Vanessa

unread,

Nov 25, 2004, 3:11:03 PM11/25/04

to

How can I format the cells in the Excel? In the XSL I use format-number(),
using 2 decimais, and when I have an integer number or a number with just one
decimal, it is showed without two decimais. It works in the IE, but not in
the Excel.

Just an example:
123,45 = OK
123,4 = it should be 123,40
123 = it should be 123,00

Thanks in advance,
Vanessa

Neil Smith [MVP Digital Media]

unread,

Nov 25, 2004, 5:32:16 PM11/25/04

to

Sure, you need to set up an Excel style to do this :

As a child element of <ss:Workbook> add

<ss:Styles>
<ss:Style ss:ID="s10">
<NumberFormat ss:Format="Currency"/>
</ss:Style>
<ss:Style ss:ID="s11">
<NumberFormat ss:Format="#,#0" />
</ss:Style>
</ss:Styles>

And for each cell in the worksheet which needs the style, use

The format is used standard Excel number format templates, the
difference between the two styles is that Currency (a pre-defined
type) automatically changes the cell to red text if the value is below
zero, and the second style is comma separated with no special sign
treatment.

Also worth noting, the ss:ID can be anything really, but should be
unique within the spreadsheet (you can't have two of <ss:Style
ss:ID="s10"> for example)

Cheers - Neil

Vanessa

unread,

Nov 26, 2004, 11:15:06 AM11/26/04

to

It didn't work. Sorry, but I started working with XLS recently. Here I posted
the xls file and the place where I need to format the value with decimals in
the Excel. I need the invoice total in Brazilian-currency format
(###.###.##0,00). If I use the "format-number" function, it works in the IE,
but in the Excel doesn't show decimals if the number doesn't have them. See
the comments where the value must be showed.

<?xml version='1.0' encoding='ISO-8859-1' ?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"

xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" >

<xsl:output method="html" encoding="ISO-8859-1"/>
<xsl:decimal-format name="VlrBrasil" decimal-separator=","
grouping-separator="."/>
<xsl:template match="invoices">
<html>
<body>
<text>Invoices</text>
<xsl:apply-templates/>
</body>
</html>
</xsl:template>

<xsl:template match="invoices">
<text>Vendor: </text>
<xsl:value-of select="code"/>
<xsl:text> - </xsl:text>
<xsl:value-of select="name"/>
<br/>
<br/>
<table border="1">
<tr>
<th>Number</th>
<th>Costumer</th>
<th align="center">Date</th>
<th align="right">Total of Invoice</th>
</tr>
<xsl:apply-templates>
<xsl:sort select="costumer"/>
</xsl:apply-templates>
</table>
<br/>
</xsl:template>

<xsl:template match="vendor">
<xsl:for-each select="invoice">
<tr>
<td>
<xsl:value-of select="number"/>
</td>
<td>
<xsl:value-of select="customer"/>
</td>
<td align="center">
<xsl:value-of select="date"/>
</td>
<td align="right">

<xsl:value-of select="format-number(total,
'###.###.##0,00', 'VlrBrasil')" />
</td>
</tr>
</xsl:for-each>
</xsl:template>

Thank you,
Vanessa

Neil Smith [MVP Digital Media]

unread,

Nov 26, 2004, 4:19:51 PM11/26/04

to

No, it won't. Here you're formatting HTML and importing into Excel as
HTML. You Actually need to make Excel XML format files (XML
Spreadsheet) to be able to use *any* formatting AFAIK,

The documentation is here :

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnexcl2k2/html/odc_xmlss.asp

Some Examples :

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnoffpro01/html/XMLOfficeXPPartI.asp
http://www.simonstl.com/articles/officeXML/spreadsheetML.html and
http://www.simonstl.com/articles/officeXML/spreadsheetMLsample.html

But the easiest way by far is this :

Save your XML file in the desired format but choose "Save As", then
pick "XML Spreadsheet" as the file type to use (its in the same
dropdown as Lotus1-2-3 and CSV files).

Open the XML spreadsheet in a text editor and immediately you will see
how it is formatted. Now, ignore the HTML, and create an XML file
which matches the XML spreadsheet. Really you only need to generate
<Row> and <Cell> <Data> tags, the Style tags can be added to the XSLT
file

Just use standard XSL (no need for number format, we'll tell Excel how
to format the numbers using the Styles I showed you)

It should take about 2-3 hours the first time, and you'll make a
couple of mistakes. Examine the excel error log produced to find out
the error (the error log is saved in your temporary internet files).
It will be much fasted once you've tried it once.

Cheers - Neil

Vanessa

unread,

Nov 29, 2004, 7:21:04 AM11/29/04

to

Thank you!

Alan

unread,

Feb 16, 2007, 7:05:00 AM2/16/07

to

Need advice. We have a Excel template (provided by others) that we must
use as a submission form. The required data is coming from a SQL 2005
database. We want to populate the form from SQL and save the completed Excel
template.

We are struggling with the right approach. On apporach is to create an xml
map to interface with SQL. Then automate Excel to update the cells.

Another approach is to use the Excel xml file format to but we can't seem
to find any info on writing to this format.

Any suggestions?