Wrox Programmer Forums
Go Back   Wrox Programmer Forums > XML > XSLT
|
XSLT General questions and answers about XSLT. For issues strictly specific to the book XSLT 1.1 Programmers Reference, please post to that forum instead.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the XSLT section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old February 20th, 2009, 10:50 AM
Registered User
 
Join Date: Feb 2009
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default Generic XSL to Excel

Hi, I have spent way too much tme on this and clearly don't know what I am doing. I am an XSLT newbie.

I have a .NET application generating a data adaptor of various recordsets that I need to load to Excel. One dataset per Excel tab. What I need to be able to do is to transform the first row of the dataset node names (i.e. my Excel column headers) then tranform all the rows for the data values. I never know the number of "columns" so the XSL needs to be able to dynamically adjust. Actually, I usually know the first 3 or four columns which need individual styling which is why I pull them separately with position()

Here is the sort of code I was playing with and feel free to call me an idiot - this is a bit alien to me.

<xsl:template match="/NewDataSet/usp_InfoMetrics_ReportTab1">
<Worksheet name="Staging31">
<Table x:FullColumns="1" x:FullRows="1">
<Row>
<xsl:for-each select="/NewDataSet/usp_InfoMetrics_ReportTab1[1]/*">
<xsl:choose>
<xsl:when test="position() = 1">
<Cell>
<Data ss:Type="String">
<xsl:value-of select="local-name()"/>
</Data>
</Cell>
</xsl:when>
<xsl:when test="position() = 2">
<Cell>
<Data ss:Type="String">
<xsl:value-of select="local-name()"/>
</Data>
</Cell>
</xsl:when>
<xsl:when test="position() = 3">
<Cell>
<Data ss:Type="String">
<xsl:value-of select="local-name()"/>
</Data>
</Cell>
</xsl:when>
<xsl:otherwise>
<Cell>
<Data ss:Type="String">
<xsl:value-of select="local-name()"/>
</Data>
</Cell>

</xsl:otherwise>
</xsl:choose>
</xsl:for-each>
<xsl:apply-templates select="/NewDataSet/usp_InfoMetrics_ReportTab1[1]" />
</Row>
<Row>
<xsl:for-each select="following-sibling::node()">
<xsl:choose>
<xsl:when test="position() = 1">
<Cell>
<Data ss:Type="String">
<xsl:value-of select="."/>
</Data>
</Cell>
</xsl:when>
<xsl:when test="position() = 2">
<Cell>
<Data ss:Type="String">
<xsl:value-of select="."/>
</Data>
</Cell>
</xsl:when>
<xsl:when test="position() = 3">
<Cell>
<Data ss:Type="String">
<xsl:value-of select="."/>
</Data>
</Cell>
</xsl:when>
<xsl:otherwise>
<Cell>
<Data ss:Type="String">
<xsl:value-of select="."/>
</Data>
</Cell>

</xsl:otherwise>
</xsl:choose>
</xsl:for-each>
<xsl:apply-templates select="/NewDataSet/usp_InfoMetrics_ReportTab1[position() > 1]" />
</Row>
</Table>
</Worksheet>
</xsl:template>

The data will look somehting like this:

<NewDataSet>
<usp_InfoMetrics_ReportTab1>
<Column1>No Restriction</Column1>
<Column2>Test1</Column2>
<Column3>Test2</Column3>
<Column4>Test3</Column4>
</usp_InfoMetrics_ReportTab1>
<usp_InfoMetrics_ReportTab1>
<Column1>A lot of Restriction</Column1>
<Column2>Test6</Column2>
<Column3>Test7</Column3>
<Column4>Test8</Column4>
</usp_InfoMetrics_ReportTab1>
<usp_InfoMetrics_ReportTab1>
<Column1>More restrictions</Column1>
<Column2>Test67</Column2>
<Column3>Test77</Column3>
<Column4>Test87</Column4>
</usp_InfoMetrics_ReportTab1>
<usp_InfoMetrics_ReportTab2>
<Bill1>Bills recordset</Bill1>
<Bill2>Test1</Bill2>
<Bill3>Test2</Bill3>
<Bill4>Test3</Bill4>
</usp_InfoMetrics_ReportTab2>
<usp_InfoMetrics_ReportTab2>
<Bill1>Bills recordset second row</Bill1>
<Bill2>Test6</Bill2>
<Bill3>Test7</Bill3>
<Bill4>Test8</Bill4>
</usp_InfoMetrics_ReportTab2>
</NewDataSet>

Any pointers would be really useful, thanks.
 
Old February 21st, 2009, 09:47 AM
Friend of Wrox
 
Join Date: Feb 2009
Posts: 119
Thanks: 25
Thanked 3 Times in 3 Posts
Default

Post your example output xml and with in the input xml I will be able to solve it. Thank, John Bampton.





Similar Threads
Thread Thread Starter Forum Replies Last Post
problems with excel generated usng xsl a_gomathi XSLT 5 May 29th, 2008 01:30 PM
XSL DateTime format for Excel fmoon606 XSLT 8 May 23rd, 2008 05:35 PM
XSL for Excel fmoon606 XSLT 5 May 9th, 2008 05:31 AM
Create generic XSL Template to create table Venkatachalapathy XSLT 5 March 11th, 2008 07:49 AM
XSL Transform with xsl string NOT xsl file skin XSLT 0 June 16th, 2003 07:30 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.