O'Reilly logo

Office 2003 XML by Evan Lenz, Mary McRae, Simon St. Laurent

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

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 ...

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