XML Hacks

Create and Process SpreadsheetML

Since Excel XP, Excel has included an XML export option. SpreadsheetML provides an XML representation of your spreadsheets, complete with formatting and formula information.

Although there are several ways to read and write Excel spreadsheet files without using Excel, one of the easiest options is to import and export XML files that use Microsoft’s SpreadsheetML vocabulary. SpreadsheetML isn’t complete—most notably, charts and VBA code are omitted—but it does represent the core components of a spreadsheet, including formulas, named ranges, and formatting.

This hack uses Excel features that are available only in Excel XP and Excel 2003 on Windows. Earlier versions of Excel do not support this, and neither do current or announced Macintosh versions of Excel.

The easiest way to get started with SpreadsheetML is to save a spreadsheet as XML. The spreadsheet shown in Figure 3-20 includes data, formulas, named ranges and cells, and some simple formatting.

Figure 3-20. A test spreadsheet for SpreadsheetML

If you save the spreadsheet using the XML Spreadsheet (*.xml) format, which you can access by selecting File → Save As, you’ll get an XML document named SpreadsheetML.xml containing the markup shown in Example 3-22. Key portions are highlighted in bold.

Example 3-22. A SpreadsheetML document

<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
               <Workbook ...