p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   BOOK: XSLT Programmer's Reference, 2nd Edition (http://p2p.wrox.com/forumdisplay.php?f=52)
-   -   XPATH Help, Please- Excel XML Workbook (http://p2p.wrox.com/showthread.php?t=48428)

jftaylor September 28th, 2006 04:06 PM

XPATH Help, Please- Excel XML Workbook
 
Sorry for the length... I need XPATH help.

I'm trying to extract some data from an Excel workbook saved as an XML spreadsheet.

Here's a very small sample file:

<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:o="urn:schemas-microsoft-com:office:office"
 xmlns:x="urn:schemas-microsoft-com:office:excel"
 xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:html="http://www.w3.org/TR/REC-html40">
 <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
  <Author>us076006</Author>
  <LastAuthor>us076006</LastAuthor>
  <Created>2006-09-28T20:17:12Z</Created>
  <Company>3M</Company>
  <Version>11.8036</Version>
 </DocumentProperties>
 <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
  <WindowHeight>6405</WindowHeight>
  <WindowWidth>8475</WindowWidth>
  <WindowTopX>480</WindowTopX>
  <WindowTopY>90</WindowTopY>
  <ProtectStructure>False</ProtectStructure>
  <ProtectWindows>False</ProtectWindows>
 </ExcelWorkbook>
 <Styles>
  <Style ss:ID="Default" ss:Name="Normal">
   <Alignment ss:Vertical="Bottom"/>
   <Borders/>

   <Interior/>
   <NumberFormat/>
   <Protection/>
  </Style>
 </Styles>
 <Names>
  <NamedRange ss:Name="Low_Right" ss:RefersTo="=Sheet1!R2C2"/>
  <NamedRange ss:Name="Top_Left" ss:RefersTo="=Sheet1!R1C1"/>
 </Names>
 <Worksheet ss:Name="Sheet1">
  <Table ss:ExpandedColumnCount="2" ss:ExpandedRowCount="2" x:FullColumns="1"
   x:FullRows="1">
   <Row>
    <Cell><Data ss:Type="String">topLeft</Data><NamedCell ss:Name="Top_Left"/></Cell>
   </Row>
   <Row>
    <Cell ss:Index="2"><Data ss:Type="String">LowRight</Data><NamedCell
      ss:Name="Low_Right"/></Cell>
   </Row>
  </Table>
  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
   <Selected/>
   <Panes>
    <Pane>
     <Number>3</Number>
     <ActiveRow>1</ActiveRow>
     <ActiveCol>1</ActiveCol>
    </Pane>
   </Panes>
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
 </Worksheet>
</Workbook>


I'm looking for a way to capture all the Cell nodes that have data. I've tried "//Cell" in the select parameter in for-each, fully specifying the path, and a number of different templates matches, but can't nail it down. Specifically, I also want to capture Data element values based on the sibling "NamedCell" element.

It's possible that the parsing tool is not capable (MSXSL.exe)

Any help will be greatly appreciated!!!

mhkay September 28th, 2006 04:37 PM

//Cell won't select anything, because the Cell elements are in namespace urn:schemas-microsoft-com:office:spreadsheet. You need to select them as //ss:Cell, with the prefix ss bound to "urn:schemas-microsoft-com:office:spreadsheet".

Michael Kay
http://www.saxonica.com/
Author, XSLT Programmer's Reference and XPath 2.0 Programmer's Reference

jftaylor September 28th, 2006 08:47 PM

Thanks for the fast answer Michael, Multiple namespaces are not something I've dealt with before. I'll give that a try...


All times are GMT -4. The time now is 08:09 PM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.