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
– Jim Garrison, Commented Jun 30, 2010 at 22:18
Also, please edit your question and add samples of the input XML and desired output. — Jim Garrison
– Jim Garrison, Commented Jun 30, 2010 at 22:19
You forgot to provide the source XML document and the expected result! — Dimitre Novatchev
– Dimitre Novatchev, Commented Jul 1, 2010 at 1:14
The XSLT as currently specified is not even a well-formed XML document. Please, correct. — Dimitre Novatchev
– Dimitre Novatchev, Commented Jul 1, 2010 at 1:16

score 1 · Accepted Answer · 2010-07-02 14:15:39Z

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.

Hi Alejandro, The solution you gave me was excellent/great. It worked 100% for me. Thanks a lot. You saved me a lifetime. I will let you know if I need more refinement. I think I need. Thanks again
@Enggr: I'm glad this helps you. Remember to provide input and output in your questions. Also, if this is the correct solution for you, you should mark as answer in order to help other people.
Ok, I will provide input/output from now. I will mark this as answer to help other people. (Also people will know you are very kind to put the effort and provide the answer :) )

user268396 · Accepted Answer · 2010-06-30 22:12:03Z

0

What is the code you currently have (tried) ? What does the input code like? What should the ouput code look like?

It's going to be rather difficult to answer if you don't supply some details. ;)

answered Jun 30, 2010 at 22:12

user268396

12.1k2 gold badges33 silver badges26 bronze badges

1 Comment

Enggr Over a year ago

Hi, I added the input XML data. The output code works the xsl:for-each ./*[count(child::*) = 0] and displayes proper column name in the result, but I don't know how to get subsequent columns in the result. Please provide me a solution if you have. Thanks in advance to all

Collectives™ on Stack Overflow

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

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related