Tom Brown
Tom Brown

Reputation: 41

How to read excel xml spreadsheet data in Classic ASP

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

Upvotes: 0

Views: 956

Answers (1)

Tom Brown
Tom Brown

Reputation: 41

Managed to find out that it's all about the namespaces.

By adding the below lines:

Set oXMLDoc = Server.CreateObject("MSXML2.DOMDocument.6.0")
 oXMLDoc.setProperty "SelectionLanguage", "XPath"
 oXMLDoc.setProperty "SelectionNamespaces", "xmlns:myns='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'"

I could then reference the tags within the xml as follows:

Set oXMLRows = oXMLDoc.selectNodes("/myns:Workbook/myns:Worksheet/myns:Table/myns:Row")

and the XML parsing could then begin!! :0)

Regards

Tom

Upvotes: 2

Related Questions