XSL for converting all rows and columns of XSL for Export to Excel. dynamic looping

Question

I have an xml resultset which has to be converted using XSL for display into an excel spreadsheet in a vb.net application. The xml resultset has 15 columns (15 properties of client like firstname,lastname,address etc) and I don't want to hardcode the select attribute of xsl/xpath with property or xml element names. I need an XSL that can turn the xsl to rows and column without knowing the column names or any hardcoding. Making the headers bold is preferred I tried doing it and reached to somepoint but far away from the final result. Please help in accomplishing this

Here's the input XML

<?xml version="1.0" encoding="utf-8"?>  
<ClientArray>  
<Client>  
 <LastName>Bill</LastName>
 <FirstName>Gates</FirstName>
 <MiddleName/>
 <Suffix/>
 <DateOfBirth>30-May-1968</DateOfBirth>
 <PlaceOfBirth/>
 <SSN>n/a</SSN>
 <Gender>Male</Gender>
 <City>SHELTON</City>
 <State>WA</State>
 <Zip>96484</Zip>
 </Client>
<Client>
 <LastName>Warron</LastName>
 <FirstName>Buffet</FirstName>
 <MiddleName>P</MiddleName>
 <Suffix/>
 <DateOfBirth>12-Aug-1957</DateOfBirth>
 <PlaceOfBirth>Mississippi</PlaceOfBirth>
 <SSN>n/a</SSN>
 <Gender>Male</Gender>
 <City>Missi</City>
 <State>KS</State>
 <Zip>66096</Zip>
 </Client>
<Client>
 <LastName>Steev</LastName>
 <FirstName>Jobbs</FirstName>
 <MiddleName/>
 <Suffix/>
 <DateOfBirth>19-Apr-1959</DateOfBirth>
 <PlaceOfBirth>Cupertino</PlaceOfBirth>
 <SSN>n/a</SSN>
 <Gender>Male</Gender>
 <City>Cupertino</City>
 <State>CA</State>
 <Zip>96066</Zip>
 </Client>
 </ClientArray>

this is the code I have

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:template match="/">
<HTML>
<HEAD>
 <STYLE type="text/css"> TABLE{table-layout: automatic; width:100%} .tblHeader{background-color:RGB(192,192,192);font-weight:bold} .row1{background-color:RGB(204,204,255)} .row2{background-color:RGB(153,204,255)} </STYLE>
 </HEAD>
<BODY>
<TABLE border="1">

<!-- Global variable to get column count -->
   <xsl:variable name="columns" select="number(/list/@columns)"/>

<THEAD>
<TR class="tblHeader">
    <xsl:for-each select="ClientArray/Client">
        <TD>name()</TD> <!-- {Getting the xml column header here} -->
    </xsl:for-each>
 </TR>
 </THEAD>

<TBODY>
<xsl:for-each select="ClientArray/Client">
<TR>
<xsl:choose>

<xsl:when test="position() mod 2 = 1">
 <xsl:attribute name="class">row1</xsl:attribute>
 </xsl:when>
<xsl:otherwise>
 <xsl:attribute name="class">row2</xsl:attribute>
 </xsl:otherwise>
 </xsl:choose>

 <xsl:for-each select=".">
<TD>
 <xsl:value-of select="./*[count(child::*) = 0]"/>
 </TD>
 </xsl:for-each>


 </TBODY>
 </TABLE>
 </BODY>
 </HTML>

 </xsl:template>
 </xsl:stylesheet>

My desired output is

LastName FirstName MiddleName Suffix etc..... Bill Gates
Buffet Warren
etc etc

Basically the XML has to be converted to a plain table that could be exported to EXCEL. They key is I don't want any hardcoding on "select" xpath attribute so that if I add more input fields XSL works without a problem. the xsl should loop for all columns without knowing column names

I fixed your formatting -- you have to indent code 4 spaces, or SO will mangle it especially if it contains HTML or XML. — Jim Garrison
Also, please edit your question and add samples of the input XML and desired output. — Jim Garrison
You forgot to provide the source XML document and the expected result! — Dimitre Novatchev
The XSLT as currently specified is not even a well-formed XML document. Please, correct. — Dimitre Novatchev

Unknown Unknown · Accepted Answer · 2010-07-01T20:17:30

Preserving your for-each driven process:

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <xsl:template match="/">
        <HTML>
            <HEAD>
                <STYLE type="text/css"> TABLE{table-layout: automatic; width:100%} .tblHeader{background-color:RGB(192,192,192);font-weight:bold} .row1{background-color:RGB(204,204,255)} .row2{background-color:RGB(153,204,255)} </STYLE>
            </HEAD>
            <BODY>
                <TABLE border="1">
                    <THEAD>
                        <TR class="tblHeader">
                            <xsl:for-each select="*/*[1]/*">
                                <TH>
                                    <xsl:value-of select="name()"/>
                                </TH>
                                <!-- {Getting the xml column header here} -->
                            </xsl:for-each>
                        </TR>
                    </THEAD>
                    <TBODY>
                        <xsl:for-each select="ClientArray/Client">
                            <TR>
                                <xsl:attribute name="class">
                                    <xsl:choose>
                                        <xsl:when test="position() mod 2 = 1">row1</xsl:when>
                                        <xsl:otherwise>row2</xsl:otherwise>
                                    </xsl:choose>
                                </xsl:attribute>
                                <xsl:for-each select="*">
                                    <TD>
                                        <xsl:value-of select="."/>
                                    </TD>
                                </xsl:for-each>
                            </TR>
                        </xsl:for-each>
                    </TBODY>
                </TABLE>
            </BODY>
        </HTML>
    </xsl:template>
</xsl:stylesheet>

Note: I'll come back later with an "XSLT style" stylesheet.

Edit: I was in a rush. The key was for my answer to come. Sorry.

Edit 2: Added stylesheet in "XSLT style".

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <xsl:template match="/ClientArray">
        <HTML>
            <HEAD>
                <STYLE type="text/css"> TABLE{table-layout: automatic; width:100%} .tblHeader{background-color:RGB(192,192,192);font-weight:bold} .row1{background-color:RGB(204,204,255)} .row2{background-color:RGB(153,204,255)} </STYLE>
            </HEAD>
            <BODY>
                <TABLE border="1">
                    <THEAD>
                        <TR class="tblHeader">
                            <xsl:apply-templates select="Client[1]/*" mode="headers"/>
                        </TR>
                    </THEAD>
                    <TBODY>
                        <xsl:apply-templates/>
                    </TBODY>
                </TABLE>
            </BODY>
        </HTML>
    </xsl:template>
    <xsl:template match="Client">
    <TR class="row{2 - position() mod 2}">
            <xsl:apply-templates/>
        </TR>
    </xsl:template>
    <xsl:template match="Client/*" mode="headers">
        <TH>
            <xsl:value-of select="name()"/>
        </TH>
    </xsl:template>
    <xsl:template match="Client/*">
        <TD>
            <xsl:value-of select="."/>
        </TD>
    </xsl:template>
</xsl:stylesheet>

Note: These stylesheets assume that all the children of Client elements are the same and are in the same order. I'd come back with a more general solution if you want to.

XSL for converting all rows and columns of XSL for Export to Excel. dynamic looping

2 Answers