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.
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:
mso-applicationprocessing instruction that identifies this file as an XML spreadsheet.
Workbooknode that defines the Microsoft Office namespaces.
Stylesnode defining the cell formatting to display in the worksheet. Styles include number formats, such as Currency, Percentage, or General number.
Worksheetnode for each order.
Columnnodes to set the width of the ...