Excel 2003 Programming: A Developer's Notebook

Transform XML into a 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.

Note

XML is a two-way street. Getting XML into Excel is as important as knowing how to get it out.

How to do it

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>
             <Taxable>Yes</Taxable>
             <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 ...