O'Reilly logo

Excel Hacks by Raina Hawley, David Hawley

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Hack #96. Save to SpreadsheetML and Extracting Data

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 Excel spreadsheet files without using Excel (see [Hack #100] for details), one of the easiest options is to 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.

Tip

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 8-14 includes data, formulas, named ranges and cells, and some simple formatting.

A test spreadsheet for SpreadsheetML

Figure 8-14. 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 a long XML document containing the markup shown in Example 8-3. Key portions are highlighted in bold.

Example 8-3. A SpreadsheetML document

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required