Betsy Rolland
LIS 600 Independent Study
Transforming XML to Microsoft XML
Terry Brooks
Winter 2006

Harvest XML from a Microsoft Excel XML (SpreadsheetML) File

The instructions below will walk you through creating a plain XML file from SpreadsheetML.

Documents:

Steps:

  1. Open your Excel file or use Office.xls. Save as spreadsheetML or use OfficeML.xml. The data looks like this:


  2. Create a new XSLT file.

  3. Insert the following header and processing instructions at the top:

  4. The data we want to cull from this spreadsheetML is in Rows 2-4, is all regular and is all in the data portion of the structure. This stylesheet (adapted from Office 2003 XML) simply iterates through the elements (Workbook->Worksheet->Table) until it gets to the rows. We do not need to harvest any information from those top three elements, so they are simply ignored. When the stylesheet gets to the level of the row, we process only rows 2-4, skipping the header row. It would also be possible to process the first row, getting the header names and using those as the names of the XML elements.


  5. To harvest our data and put it into our XML structure, we create a template to match each Row element, then fill our XML with the data in the spreadsheetML, placing one bit of cell data in each XML element. For example, the element "LastName" gets the data from Cell[1].


  6. The XML that results from this transformation looks like this: