Search code examples
asp.netxsltexport-to-excel

ASP.NET: How to convert schema to Excel xsl

I have the following xml schema:

<?xml version="1.0" encoding="utf-8"?>
    <xsd:schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xsd="http://www.w3.org/2001/XMLSchema" attributeFormDefault="unqualified" elementFormDefault="qualified" version="1.0">
    <xs:element name="PercentTimeReport">
        <xs:complexType>
            <xs:sequence>
                <xs:element name="ParamStartDate" type="xs:dateTime" />
                <xs:element name="ParamEndDate" type="xs:dateTime" />
                <xs:element name="ParamQuarterInt" type="xs:unsignedByte" />
                <xs:element name="ParamProjID" nillable="true" />
                <xs:element name="ParamStaffID" nillable="true" />
                <xs:element name="ParamPercentRange" type="xs:unsignedByte" />
                <xs:element name="Items">
                    <xs:complexType>
                        <xs:sequence>
                            <xs:element maxOccurs="unbounded" name="Item">
                                <xs:complexType>
                                    <xs:sequence>
                                        <xs:element name="ID" type="xs:unsignedShort" />
                                        <xs:element name="EmployeeName" type="xs:string" />
                                        <xs:element name="StaffID" type="xs:unsignedShort" />
                                        <xs:element name="Status" type="xs:string" />
                                        <xs:element name="Date" type="xs:dateTime" />
                                        <xs:element name="Department" type="xs:string" />
                                        <xs:element name="DepartmentCode" type="xs:string" />
                                        <xs:element name="Project" type="xs:string" />
                                        <xs:element name="ProjectID" type="xs:unsignedByte" />
                                        <xs:element name="Hours" type="xs:unsignedByte" />
                                        <xs:element name="HoursPerWeek" type="xs:decimal" />
                                        <xs:element name="PercentTime" type="xs:decimal" />
                                        <xs:element name="ActualContact" type="xs:boolean" />
                                        <xs:element name="Body" type="xs:string" />
                                        <xs:element name="Issue" type="xs:string" />
                                        <xs:element name="Activity" type="xs:string" />
                                    </xs:sequence>
                                </xs:complexType>
                            </xs:element>
                        </xs:sequence>
                    </xs:complexType>
                </xs:element>
            </xs:sequence>
        </xs:complexType>
    </xs:element>
</xsd:schema>

and I'd like to generate an excel file that only shows the Items table in an ASP.NET 2.0 Web application. I really don't understand this code that I found (see below), or XSLT enough to get the output format that I need. Does anyone know XSLT, that could tell me how to modify the XSLT below to:

1) Hide the elements other than Items (e.g. ParamStartDate, ParamEndDate, etc.).

2) Output the table with the nested "Items" complex element.

Currently, the xsl below produces each of the elements as column headers, and the Items cell contains all the items on one row. I essentially need to go one level deeper.

<xsl:template match="/">
    <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
        xmlns:o="urn:schemas-microsoft-com:office:office"
        xmlns:x="urn:schemas-microsoft-com:office:excel"
        xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
        xmlns:html="http://www.w3.org/TR/REC-html40">
        <xsl:apply-templates/>
    </Workbook>
</xsl:template>

<xsl:template match="/*">
    <Worksheet>
        <xsl:attribute name="ss:Name">
            <xsl:value-of select="local-name(/*/*)"/>
        </xsl:attribute>
        <Table x:FullColumns="1" x:FullRows="1">
            <Row>
                <xsl:for-each select="*[position() = 1]/*">
                    <Cell>
                        <Data ss:Type="String">
                            <xsl:value-of select="local-name()"/>
                        </Data>
                    </Cell>
                </xsl:for-each>
            </Row>
            <xsl:apply-templates/>
        </Table>
    </Worksheet>
</xsl:template>

<xsl:template match="/*/*">
    <Row>
        <xsl:apply-templates/>
    </Row>
</xsl:template>


<xsl:template match="/*/*/*">
    <Cell>
        <Data ss:Type="String">
            <xsl:value-of select="."/>
        </Data>
    </Cell>
</xsl:template>

Desired Excel Output (Only show Items complexType as Table):

   ID   EmployeeName    StaffID    ....     Issue   Activity
   1     John Smith       231      ....      text     text
   2     Kate Henderson   101      ....      text2    text3
   .                               ....
   .                               ....
   N                               ....

Current Output:

   ParamStartDate   ParamEndDate   ....    ParamPercentRange   Items
    '01/01/2010'    '04/01/2010'   ....            6           '1John Smith231...texttext....'

As always, any help greatly appreciated.

Thank You

P.S. Alejandro, here's the output with your changes:

<?xml version="1.0" encoding="utf-8"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:html="http://www.w3.org/TR/REC-html40">
<Worksheet ss:Name="PercentTimeReport">
<Table x:FullColumns="1" x:FullRows="1">
<Row />
</Table>
</Worksheet>
</Workbook>

Here is a sample of my original output:

<?xml version="1.0" encoding="utf-8"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:html="http://www.w3.org/TR/REC-html40" xmlns:msxsl="urn:schemas-microsoft-com:xslt" xmlns:user="urn:my-scripts">
    <Worksheet ss:Name="PercentTimeReport">
    <Table x:FullColumns="1" x:FullRows="1">
        <Row>
            <Cell><Data ss:Type="String">ParamStartDate</Data></Cell><Cell><Data ss:Type="String">ParamEndDate</Data></Cell><Cell><Data ss:Type="String">ParamQuarterInt</Data></Cell><Cell><Data ss:Type="String">ParamPercentRange</Data></Cell><Cell><Data ss:Type="String">Items</Data></Cell>
        </Row>
        <Row>
            <Cell><Data ss:Type="String">2010-07-01T00:00:00</Data></Cell><Cell><Data ss:Type="String">2010-09-30T00:00:00</Data></Cell>....</Cell>
        </Row>
    </Table>
    </Worksheet>
</Workbook>
Solution

  • Inside the template that match /* your context (following the schema you've privided) is PercentTimeReport root element. It seems that you want to iterate trougth child elements of PercentTimeReport/Items/Item.

    So change:

    <xsl:for-each select="*[position() = 1]/*">

    For:

    <xsl:for-each select="Items/Item[1]/*">

    And this:

    <xsl:apply-templates/>

    For:

    <xsl:apply-templates select="Items/Item"/>

    And this patterns:

    <xsl:template match="/*/*">   

<xsl:template match="/*/*/*">

    For:

    <xsl:template match="Item">   

<xsl:template match="Item/*">

    Edit: This input (following schema)

    <PercentTimeReport>
    <ParamStartDate>ParamStartDate</ParamStartDate>
    <ParamEndDate>ParamEndDate</ParamEndDate>
    <ParamQuarterInt>ParamQuarterInt</ParamQuarterInt>
    <ParamProjID>ParamProjID</ParamProjID>
    <ParamStaffID>ParamStaffID</ParamStaffID>
    <ParamPercentRange>ParamPercentRange</ParamPercentRange>
    <Items>
        <Item>
            <ID>ID1</ID>
            <EmployeeName>EmployeeName1</EmployeeName>
            <StaffID>StaffID1</StaffID>
            <Status>Status1</Status>
            <Date>Date1</Date>
            <Department>Department1</Department>
            <DepartmentCode>DepartmentCode1</DepartmentCode>
            <Project>Project1</Project>
            <ProjectID>ProjectID1</ProjectID>
            <Hours>Hours1</Hours>
            <HoursPerWeek>HoursPerWeek1</HoursPerWeek>
            <PercentTime>PercentTime1</PercentTime>
            <ActualContact>ActualContact1</ActualContact>
            <Body>Body1</Body>
            <Issue>Issue1</Issue>
            <Activity>Activity1</Activity>
        </Item>
        <Item>
            <ID>ID2</ID>
            <EmployeeName>EmployeeName2</EmployeeName>
            <StaffID>StaffID2</StaffID>
            <Status>Status2</Status>
            <Date>Date2</Date>
            <Department>Department2</Department>
            <DepartmentCode>DepartmentCode2</DepartmentCode>
            <Project>Project2</Project>
            <ProjectID>ProjectID2</ProjectID>
            <Hours>Hours2</Hours>
            <HoursPerWeek>HoursPerWeek2</HoursPerWeek>
            <PercentTime>PercentTime2</PercentTime>
            <ActualContact>ActualContact2</ActualContact>
            <Body>Body2</Body>
            <Issue>Issue2</Issue>
            <Activity>Activity2</Activity>
        </Item>
        <Item>
            <ID>ID3</ID>
            <EmployeeName>EmployeeName3</EmployeeName>
            <StaffID>StaffID3</StaffID>
            <Status>Status3</Status>
            <Date>Date3</Date>
            <Department>Department3</Department>
            <DepartmentCode>DepartmentCode3</DepartmentCode>
            <Project>Project3</Project>
            <ProjectID>ProjectID3</ProjectID>
            <Hours>Hours3</Hours>
            <HoursPerWeek>HoursPerWeek3</HoursPerWeek>
            <PercentTime>PercentTime3</PercentTime>
            <ActualContact>ActualContact3</ActualContact>
            <Body>Body3</Body>
            <Issue>Issue3</Issue>
            <Activity>Activity3</Activity>
        </Item>
        <Item>
            <ID>ID4</ID>
            <EmployeeName>EmployeeName4</EmployeeName>
            <StaffID>StaffID4</StaffID>
            <Status>Status4</Status>
            <Date>Date4</Date>
            <Department>Department4</Department>
            <DepartmentCode>DepartmentCode4</DepartmentCode>
            <Project>Project4</Project>
            <ProjectID>ProjectID4</ProjectID>
            <Hours>Hours4</Hours>
            <HoursPerWeek>HoursPerWeek4</HoursPerWeek>
            <PercentTime>PercentTime4</PercentTime>
            <ActualContact>ActualContact4</ActualContact>
            <Body>Body4</Body>
            <Issue>Issue4</Issue>
            <Activity>Activity4</Activity>
        </Item>
    </Items>
</PercentTimeReport>

    With this stylesheet (Yours plus my changes)

    <xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:html="http://www.w3.org/TR/REC-html40">
    <xsl:template match="/">
        <Workbook>
            <xsl:apply-templates/>
        </Workbook>
    </xsl:template>
    <xsl:template match="/*">
        <Worksheet>
            <xsl:attribute name="ss:Name">
                <xsl:value-of select="local-name(/*/*)"/>
            </xsl:attribute>
            <Table x:FullColumns="1" x:FullRows="1">
                <Row>
                    <xsl:for-each select="Items/Item[1]/*">
                        <Cell>
                            <Data ss:Type="String">
                                <xsl:value-of select="local-name()"/>
                            </Data>
                        </Cell>
                    </xsl:for-each>
                </Row>
                <xsl:apply-templates select="Items/Item"/>
            </Table>
        </Worksheet>
    </xsl:template>
    <xsl:template match="Item">
        <Row>
            <xsl:apply-templates/>
        </Row>
    </xsl:template>
    <xsl:template match="Item/*">
        <Cell>
            <Data ss:Type="String">
                <xsl:value-of select="."/>
            </Data>
        </Cell>
    </xsl:template>
</xsl:stylesheet>

    Output:

    <Workbook xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:html="http://www.w3.org/TR/REC-html40">
    <Worksheet ss:Name="ParamStartDate">
        <Table x:FullColumns="1" x:FullRows="1">
            <Row>
                <Cell>
                    <Data ss:Type="String">ID</Data>
                </Cell>
                <Cell>
                    <Data ss:Type="String">EmployeeName</Data>
                </Cell>
                <Cell>
                    <Data ss:Type="String">StaffID</Data>
                </Cell>
                <Cell>
                    <Data ss:Type="String">Status</Data>
                </Cell>
                <Cell>
                    <Data ss:Type="String">Date</Data>
                </Cell>
                <Cell>
                    <Data ss:Type="String">Department</Data>
                </Cell>
                <Cell>
                    <Data ss:Type="String">DepartmentCode</Data>
                </Cell>
                <Cell>
                    <Data ss:Type="String">Project</Data>
                </Cell>
                <Cell>
                    <Data ss:Type="String">ProjectID</Data>
                </Cell>
                <Cell>
                    <Data ss:Type="String">Hours</Data>
                </Cell>
                <Cell>
                    <Data ss:Type="String">HoursPerWeek</Data>
                </Cell>
                <Cell>
                    <Data ss:Type="String">PercentTime</Data>
                </Cell>
                <Cell>
                    <Data ss:Type="String">ActualContact</Data>
                </Cell>
                <Cell>
                    <Data ss:Type="String">Body</Data>
                </Cell>
                <Cell>
                    <Data ss:Type="String">Issue</Data>
                </Cell>
                <Cell>
                    <Data ss:Type="String">Activity</Data>
                </Cell>
            </Row>
            <Row>
                <Cell>
                    <Data ss:Type="String">ID1</Data>
                </Cell>
                <Cell>
                    <Data ss:Type="String">EmployeeName1</Data>
                </Cell>
                <Cell>
                    <Data ss:Type="String">StaffID1</Data>
                </Cell>
                <Cell>
                    <Data ss:Type="String">Status1</Data>
                </Cell>
                <Cell>
                    <Data ss:Type="String">Date1</Data>
                </Cell>
                <Cell>
                    <Data ss:Type="String">Department1</Data>
                </Cell>
                <Cell>
                    <Data ss:Type="String">DepartmentCode1</Data>
                </Cell>
                <Cell>
                    <Data ss:Type="String">Project1</Data>
                </Cell>
                <Cell>
                    <Data ss:Type="String">ProjectID1</Data>
                </Cell>
                <Cell>
                    <Data ss:Type="String">Hours1</Data>
                </Cell>
                <Cell>
                    <Data ss:Type="String">HoursPerWeek1</Data>
                </Cell>
                <Cell>
                    <Data ss:Type="String">PercentTime1</Data>
                </Cell>
                <Cell>
                    <Data ss:Type="String">ActualContact1</Data>
                </Cell>
                <Cell>
                    <Data ss:Type="String">Body1</Data>
                </Cell>
                <Cell>
                    <Data ss:Type="String">Issue1</Data>
                </Cell>
                <Cell>
                    <Data ss:Type="String">Activity1</Data>
                </Cell>
            </Row>
            <Row>
                <Cell>
                    <Data ss:Type="String">ID2</Data>
                </Cell>
                <Cell>
                    <Data ss:Type="String">EmployeeName2</Data>
                </Cell>
                <Cell>
                    <Data ss:Type="String">StaffID2</Data>
                </Cell>
                <Cell>
                    <Data ss:Type="String">Status2</Data>
                </Cell>
                <Cell>
                    <Data ss:Type="String">Date2</Data>
                </Cell>
                <Cell>
                    <Data ss:Type="String">Department2</Data>
                </Cell>
                <Cell>
                    <Data ss:Type="String">DepartmentCode2</Data>
                </Cell>
                <Cell>
                    <Data ss:Type="String">Project2</Data>
                </Cell>
                <Cell>
                    <Data ss:Type="String">ProjectID2</Data>
                </Cell>
                <Cell>
                    <Data ss:Type="String">Hours2</Data>
                </Cell>
                <Cell>
                    <Data ss:Type="String">HoursPerWeek2</Data>
                </Cell>
                <Cell>
                    <Data ss:Type="String">PercentTime2</Data>
                </Cell>
                <Cell>
                    <Data ss:Type="String">ActualContact2</Data>
                </Cell>
                <Cell>
                    <Data ss:Type="String">Body2</Data>
                </Cell>
                <Cell>
                    <Data ss:Type="String">Issue2</Data>
                </Cell>
                <Cell>
                    <Data ss:Type="String">Activity2</Data>
                </Cell>
            </Row>
            <Row>
                <Cell>
                    <Data ss:Type="String">ID3</Data>
                </Cell>
                <Cell>
                    <Data ss:Type="String">EmployeeName3</Data>
                </Cell>
                <Cell>
                    <Data ss:Type="String">StaffID3</Data>
                </Cell>
                <Cell>
                    <Data ss:Type="String">Status3</Data>
                </Cell>
                <Cell>
                    <Data ss:Type="String">Date3</Data>
                </Cell>
                <Cell>
                    <Data ss:Type="String">Department3</Data>
                </Cell>
                <Cell>
                    <Data ss:Type="String">DepartmentCode3</Data>
                </Cell>
                <Cell>
                    <Data ss:Type="String">Project3</Data>
                </Cell>
                <Cell>
                    <Data ss:Type="String">ProjectID3</Data>
                </Cell>
                <Cell>
                    <Data ss:Type="String">Hours3</Data>
                </Cell>
                <Cell>
                    <Data ss:Type="String">HoursPerWeek3</Data>
                </Cell>
                <Cell>
                    <Data ss:Type="String">PercentTime3</Data>
                </Cell>
                <Cell>
                    <Data ss:Type="String">ActualContact3</Data>
                </Cell>
                <Cell>
                    <Data ss:Type="String">Body3</Data>
                </Cell>
                <Cell>
                    <Data ss:Type="String">Issue3</Data>
                </Cell>
                <Cell>
                    <Data ss:Type="String">Activity3</Data>
                </Cell>
            </Row>
            <Row>
                <Cell>
                    <Data ss:Type="String">ID4</Data>
                </Cell>
                <Cell>
                    <Data ss:Type="String">EmployeeName4</Data>
                </Cell>
                <Cell>
                    <Data ss:Type="String">StaffID4</Data>
                </Cell>
                <Cell>
                    <Data ss:Type="String">Status4</Data>
                </Cell>
                <Cell>
                    <Data ss:Type="String">Date4</Data>
                </Cell>
                <Cell>
                    <Data ss:Type="String">Department4</Data>
                </Cell>
                <Cell>
                    <Data ss:Type="String">DepartmentCode4</Data>
                </Cell>
                <Cell>
                    <Data ss:Type="String">Project4</Data>
                </Cell>
                <Cell>
                    <Data ss:Type="String">ProjectID4</Data>
                </Cell>
                <Cell>
                    <Data ss:Type="String">Hours4</Data>
                </Cell>
                <Cell>
                    <Data ss:Type="String">HoursPerWeek4</Data>
                </Cell>
                <Cell>
                    <Data ss:Type="String">PercentTime4</Data>
                </Cell>
                <Cell>
                    <Data ss:Type="String">ActualContact4</Data>
                </Cell>
                <Cell>
                    <Data ss:Type="String">Body4</Data>
                </Cell>
                <Cell>
                    <Data ss:Type="String">Issue4</Data>
                </Cell>
                <Cell>
                    <Data ss:Type="String">Activity4</Data>
                </Cell>
            </Row>
        </Table>
    </Worksheet>
</Workbook>