How to read excel xml spreadsheet data in Classic ASP

Hi All

I thought it would be a straightforward xml load and loop, but for some reason I can’t read anything out of an Excel XML spreadsheet.

The content of the Excel spreadsheet, which appears to be a standardised output, is as follows:

<?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" xmlns:msxsl="urn:schemas-microsoft-com:xslt" xmlns:user="urn:my-scripts">
<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">...</DocumentProperties>
<OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">...</OfficeDocumentSettings>
<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">...</ExcelWorkbook>
<Styles>...</Styles>
<Worksheet ss:Name="Sheet1" ss:Protected="1">
<Names>
<NamedRange ss:Name="Print_Titles" ss:RefersTo="=Sheet1!C2,Sheet1!R7"/>
</Names>
<Table ss:ExpandedColumnCount="34" ss:ExpandedRowCount="32" x:FullColumns="1" x:FullRows="1" ss:DefaultRowHeight="13.2">
<Column ss:Hidden="1" ss:Width="175.2"/>
<Column ss:Width="250.20000000000002"/>
<Column ss:Width="60" ss:Span="31"/>
<Row ss:AutoFitHeight="0" ss:Height="150" ss:StyleID="s67">...</Row>
<Row ss:Height="15">
<Cell ss:StyleID="s69">
<Data ss:Type="String">W89231</Data>
</Cell>
<Cell ss:StyleID="s70">
<Data ss:Type="String">Tom Brown</Data>
<NamedCell ss:Name="Print_Titles"/>
</Cell>
<Cell ss:StyleID="s69">
<Data ss:Type="String">1E+</Data>
</Cell>
<Cell ss:StyleID="s69">
<Data ss:Type="String">1m</Data>
</Cell>

etc etc

Having read that byTagName isn’t the best method I tried the following singlenode code first:

strFilename = "1B.xml"
Set oXMLDoc = Server.CreateObject("MSXML2.DOMDocument.6.0")
oXMLDoc.async = False
oXMLDoc.Load (Server.MapPath(strFilename))
Set NodeList = oXMLDoc.documentElement.selectNodes("Workbook/Worksheet")
For Each Node In NodeList
 Response.Write Node.selectSingleNode("row/cell/data/text()")
Next

But absolutely nothing happens.

So I went back to the tried and trusted byTagName and tried the following:

strFilename = "1B.xml"
Set oXMLDoc = Server.CreateObject("MSXML2.DOMDocument.6.0")
oXMLDoc.async = False
oXMLDoc.Load (Server.MapPath(strFilename))
Set oRows = oXMLDoc.documentElement.getElementsByTagName("Row")
Response.Write oRows.length
The oRows.length feedback is zero

I just can’t seem to get into the DOM even though its a standard Excel XML spreadsheet format.

I’d really appreciate it if somebody could please explain how I can read through the rows and extract the necessary data values.

Regards

Tom

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.