Office 2003 XML

Extracting Information from XML Spreadsheets

When the spreadsheet data arrives in a form like Example 7-3, it’s easy to extract the data using tools like XSLT. All the cells in the area used contain data, and it’s just a simple table. If, for example, we wanted to extract the data in this spreadsheet and produce a much lighter XML document containing just the data, the stylesheet might look like that shown in Example 7-4.

Example 7-4. A simple stylesheet for extracting data from Excel tables

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns="http://simonstl.com/ns/dinosaurs/" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" > <xsl:output method="xml" omit-xml-declaration="yes" indent="yes" encoding="US- ASCII"/> <xsl:template match="/"> <xsl:apply-templates select="ss:Workbook"/> </xsl:template> <xsl:template match="ss:Workbook"> <dinosaurs> <xsl:apply-templates select="ss:Worksheet[@ss:Name = 'Sheet1']"/> </dinosaurs> </xsl:template> <xsl:template match="ss:Worksheet"> <xsl:apply-templates select="ss:Table" /> </xsl:template> <xsl:template match="ss:Table"> <xsl:apply-templates select="ss:Row[position( ) &gt; 1]" /> </xsl:template> <xsl:template match="ss:Row"> <sale> <IDnum><xsl:apply-templates select="ss:Cell[1]" /></IDnum> <critter><xsl:apply-templates select="ss:Cell[2]" /></critter> <price><xsl:apply-templates select="ss:Cell[3]" /></price> <quantity><xsl:apply-templates select="ss:Cell[4]" /></quantity> <total><xsl:apply-templates ...