Programming Excel with VBA and .NET

Transform XML into an XML Spreadsheet

You can also use XSLT or other tools to transform XML files created outside of Excel into XML spreadsheets. In this way, you can create native Excel documents from your own applications.

For instance, the following abbreviated XML represents a customer order created outside of Excel:

<?xml version="1.0"?>
<!-- SimpleOrder.xml -->
<Orders>
<Order>
    <ID>1002</ID>
    <BillTo>
        <Address>
            <Name>Joe Magnus</Name>
            <Street1>1234 Made Up Place</Street1>
            <City>Somewhere</City>
            <State>FL</State>
            <Zip>33955</Zip>
        </Address>
    </BillTo>
    <ShipTo>
        <Address>...</Address>
    </ShipTo>
    <Line>
        <Number>20</Number>
        <Description>Mahogany Tiller</Description>
        <Quantity>1</Quantity>
        <UnitPrice>95.00</UnitPrice>
        <Taxible>Yes</Taxible>
        <Total>95.00</Total>
    </Line>
    <Line>...</Line>
    <Total>
        <SubTotal>540.00</SubTotal>
        <Tax>3.24</Tax>
        <Due>543.24</Due>
    </Total>
</Order>
</Orders>

To convert this XML into an XML spreadsheet, create XSLT that creates the following nodes and processing instruction:

1. The mso-application processing instruction that identifies this file as an XML spreadsheet.

2. A root workbook node that defines the Microsoft Office namespaces.

3. A styles node defining the cell formatting to display in the worksheet. Styles include number formats, such as currency, percentage, or general number.

4. A worksheet node for each order.

5. Column nodes to set the width of the columns on the worksheet.

6. Row, cell, and data nodes for the order items you want to include in the worksheet.

Some of the ...