XML to XLS using java

Question

How can I map the metadata to data. For example I only want LastName and Email from the xml file into the xls file. How can I select LastName and email from xml file and convert that into two column XLS file columns being Lastname and email. Thank you

XML Document

<root>
    <metadata>
        <item name="Last Name" type="xs:string" length="182"/>
        <item name="First Name" type="xs:string" length="182"/>
        <item name="Class Registration #" type="xs:decimal" precision="19"/>
        <item name="Email" type="xs:string" length="422"/>
        <item name="SacLink ID" type="xs:string" length="92"/>
        <item name="Term Desc" type="xs:string" length="62"/>
        <item name="Status Code" type="xs:string" length="6"/>
    </metadata>
    <data>
        <row>
            <value>XXX</value>
            <value>xxxx</value>
            <value>xxx</value>
            <value>xxx</value>
            <value>xxx</value>
            <value>xx</value>
            <value>xx</value>
        </row>
        <row>
            <value>xxy</value>
            <value>xx</value>
            <value>xx</value>
            <value>xx</value>
            <value>xx</value>
            <value>xx</value>
            <value>xx</value>
        </row>
    </data>
</root>

Answer 1

You might use an XSL transform for this, outputting in CSV format which is loadable by Excel.

If you want to write a program using C# 4.0 and Office 2008/10, it's also easier than ever to leverage the interop capabilities - have a look at the C# Samples, in the office samples.

Answer 2

This stylesheet:

<xsl:stylesheet version="1.0"
 xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
 xmlns="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:x="urn:schemas-microsoft-com:office:excel">
    <xsl:param name="pColumnNames" select="'Last Name,Email'"/>
    <xsl:key name="kItemByPosition" match="item" 
             use="count(preceding-sibling::item)+1"/>
    <xsl:template match="/">
        <xsl:processing-instruction name="mso-application">
            <xsl:text>progid="Excel.Sheet"</xsl:text>
        </xsl:processing-instruction>
        <Workbook>
            <Worksheet ss:Name="Email Table">
                <Table x:FullColumns="1" x:FullRows="1">
                    <xsl:apply-templates/>
                </Table>
            </Worksheet>
        </Workbook>
    </xsl:template>
    <xsl:template match="metadata|row">
        <Row>
            <xsl:apply-templates/>
        </Row>
    </xsl:template>
    <xsl:template match="item|value">
        <xsl:if test="contains(concat(',',$pColumnNames,','),
                               concat(',',key('kItemByPosition',
                                              position())/@name,','))">
            <Cell>
                <Data ss:Type="String">
                    <xsl:apply-templates select="@name|node()"/>
                </Data>
            </Cell>
        </xsl:if>
    </xsl:template>
</xsl:stylesheet>

Output:

<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" 
          xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" 
          xmlns:x="urn:schemas-microsoft-com:office:excel">
    <Worksheet ss:Name="Email Table">
        <Table x:FullColumns="1" x:FullRows="1">
            <Row>
                <Cell>
                    <Data ss:Type="String">Last Name</Data>
                </Cell>
                <Cell>
                    <Data ss:Type="String">Email</Data>
                </Cell>
            </Row>
            <Row>
                <Cell>
                    <Data ss:Type="String">XXX</Data>
                </Cell>
                <Cell>
                    <Data ss:Type="String">xxx</Data>
                </Cell>
            </Row>
            <Row>
                <Cell>
                    <Data ss:Type="String">xxy</Data>
                </Cell>
                <Cell>
                    <Data ss:Type="String">xx</Data>
                </Cell>
            </Row>
        </Table>
    </Worksheet>
</Workbook>