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 #97. Create Spreadsheets using SpreadsheetML

While exporting spreadsheets as XML is useful, Excel also lets you import information this way, letting you create spreadsheets using SpreadsheetML.

Excel supports SpreadsheetML for both import and export, providing a complete pathway for information. You can open a SpreadsheetML document, make a few changes, and re-open it in Excel if you want. (This is the only way to edit the schemas for XML Maps, for instance.) Perhaps more importantly, though, you can generate SpreadsheetML documents from whatever data you happen to have, providing a relatively easy and automatable path for transforming raw information into Excel spreadsheets.

To demonstrate, the code in Example 8-6 will transform the XML shown in Example 8-5 of the previous hack back into Excel again using XSLT. The stylesheet in Example 8-6 uses the original spreadsheet as a template, and will produce XML very much like the SpreadsheetML you saved from Excel originally. The example leaves out some formatting so that there's a visible difference.

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.

Example 8-6. XSLT for converting the custom XML vocabulary back to SpreadsheetML

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:d="http://simonstl.com/ns/dinosaurs/" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" ...

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