[xsl] Re: XPath Against OpenXML and OpenOffice Spreadsheets?

Subject: [xsl] Re: XPath Against OpenXML and OpenOffice Spreadsheets?
From: "Laura Porter" <lporter@xxxxxxxxxxxxxxxxxxxxxxx>
Date: Wed, 13 Aug 2008 16:58:47 +0100
On Wed, 4 Jun 2008 09:47:40 -0500, Ronnie Royston wrote:

> I would be delighted to see a working example.  Can someone please
> post a stylsheet that gets the value of cell A1 from Excel 2007 file
> named "Book1.xlsx" and assign it to <xsl:variable name="excel-value"
> as="xs:string*"/>?

> This simple task is beyond my (and Google's) reach.


This thread is quite old, but back when I first saw it I was interested.
I've been working on a set of XSLTs to turn Excel 2007 spreadsheets into
XHTML for the last few months, and have ended up with a very
sophisticated project which picks up data, data formats, colours,
layouts etc very nicely.

Obviously I can't share all my work with the list due to
confidentiality, but the below should do what Ronnie wants, assuming
he's after a pure XSLT solution that can be run against a "decompiled"
Excel 2007 file (I simply unzip them and run my XSLTs on
[Content_Types].xml).

This XSLT only gets the "raw" value contained in the cell, which may not
be what's displayed to the user in Excel. Formatting the value is
possible, but obviously out of the scope of this challenge.

Regards,
Laura.

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="2.0"
	xmlns:xsl="http://www.w3.org/1999/XSL/Transform";
	xmlns:xs="http://www.w3.org/2001/XMLSchema";

xmlns:t="http://schemas.openxmlformats.org/package/2006/content-types";

xmlns:m="http://schemas.openxmlformats.org/spreadsheetml/2006/main";

xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relations
hips"

xmlns:pr="http://schemas.openxmlformats.org/package/2006/relationships";
	exclude-result-prefixes="t m pr xs r">

	<xsl:output method="text"/>

	<xsl:param name="basePath" select="'C:/work/ExcelPlugin2/Copy of
Book1'"/>

	<xsl:variable name="workbook"
select="document(concat($basePath,'/xl/workbook.xml'))/m:workbook"/>
	<xsl:variable name="workbookRels"
select="document(concat($basePath,'/xl/_rels/workbook.xml.rels'))/pr:Rel
ationships/pr:Relationship"/>

	<xsl:template match="t:Types">
		<xsl:apply-templates select="$workbook"/>
	</xsl:template>

	<xsl:template match="m:workbook">
		<xsl:apply-templates select="m:sheets/m:sheet"/>
	</xsl:template>

	<xsl:template match="m:sheet">
		<xsl:if test="position() = 1">
			<xsl:variable name="sheetName" select="@name"/>
			<xsl:variable name="rid" select="@r:id"/>
			<xsl:variable name="sheetXML"
select="document(concat($basePath,'/xl/',$workbookRels[@Id=$rid]/@Target
))"/>
			<xsl:if
test="$sheetXML/m:worksheet/m:sheetData/*">
				<xsl:variable name="topLeft"
select="$sheetXML/m:worksheet/m:sheetData/m:row[1]/m:c[1]"/>
				<xsl:variable name="result" select="if
($topLeft/@r = 'A1') then $topLeft else concat('A1 is empty, but there
is data beginning at cell ', $topLeft/@r, ', and it is: ', $topLeft)"
as="xs:string*"/>
				<xsl:value-of select="$result"/>
			</xsl:if>
		</xsl:if>
	</xsl:template>

</xsl:stylesheet>



Laura Porter
Development

-------------------------------
lporter@xxxxxxxxxxxxxxxxxxxxxxx
Limehouse Software Ltd
DDI: (020) 7566 3335
Main: (020) 7566 3320
Fax: (020) 7566 3321
Limehouse Software Ltd
4th Floor
1 London Bridge
London
SE1 9BG
Manchester Office:
3rd Floor, The Triangle, Exchange Square, Manchester M4 3TR
Tel: (0161) 240 2440, Fax: (0161) 240 2441, ISDN: 08700 119 400
Check out Limehouse Software's innovative solutions
www.limehousesoftware.co.uk - Transforming the way you publish and consult on
information
The information contained in this e-mail or in any attachments is confidential
and is intended solely for the named addressee only. Access to this e-mail by
anyone else is unauthorised. If you are not the intended recipient, please
notify Limehouse Software Ltd immediately by returning this e-mail to sender
or calling 020 7566 3320 and do not read, use or disseminate the information.
Opinions expressed in this e-mail are those of the sender and not necessarily
the company. Although an active anti-virus policy is operated, the company
accepts no liability for any damage caused by any virus transmitted by this
e-mail, including any attachments.

Current Thread