解析从Microsoft Excel导出的XML电子表格文件

I'm trying to use VBScript to parse a XML spreadsheet file exported from Microsoft Excel. I started by trying to count number of the <Row> elements. However the script always return 0. What did I do wrong?

我正在尝试使用VBScript来解析从Microsoft Excel导出的XML电子表格文件。我开始尝试计算元素的数量。但是脚本总是返回0。我做错了什么?

Here is my VBScript file:

这是我的VBScript文件:

Set oXML = CreateObject("Microsoft.XMLDOM")

oXML.aSync = false
oXML.SetProperty "SelectionLanguage", "XPath"
oXML.SetProperty "ServerHTTPRequest", True
oXML.validateOnParse = False
oXML.resolveExternals = False

oXML.Load "_test_.xml"

MsgBox oXML.SelectNodes("//Row").length ' Return 0

WScript.Quit

' Looping through all nodes works fine
Set nodes = oXML.selectNodes("//*")    
For i = 0 to nodes.length -1 
    Msgbox nodes(i).nodeName
Next

And here is the XML file:

这是XML文件

<?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">
        <Version>14.00</Version>
    </DocumentProperties>
    <OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
        <AllowPNG/>
    </OfficeDocumentSettings>
    <Worksheet ss:Name="Sheet1">
        <Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="2" x:FullColumns="1"
        x:FullRows="1" ss:DefaultRowHeight="15">
            <Row ss:AutoFitHeight="0">
                <Cell><Data ss:Type="String">First Row</Data></Cell>
            </Row>
            <Row ss:AutoFitHeight="0">
                <Cell><Data ss:Type="String">Second Row</Data></Cell>
            </Row>
        </Table>
    </Worksheet>
</Workbook>

1 个解决方案

#1

Since there are multiple namespaces, you must define those namespaces for the XPATH. This must be done even for the default namespace. If not, you cannot get concrete elements from namespaces using XPATH. Thats why //* will work but //Row will not work because XPATH does not know which namespace Row belongs to.

由于有多个名称空间，所以必须为XPATH定义这些名称空间。即使对于默认的命名空间，也必须这样做。如果没有，则不能使用XPATH从名称空间获取具体的元素。这就是为什么//*可以工作，但//Row不行的原因，因为XPATH不知道属于哪个名称空间行。

Setting the namespaces will be done using setProperty Method. See also Second-Level DOM Properties and SelectionNamespaces Property.

将使用setProperty方法设置名称空间。请参见第二级DOM属性和选择名称空间属性。

Your example:

你的例子:

Set oXML = CreateObject("Microsoft.XMLDOM")

oXML.aSync = false
oXML.SetProperty "SelectionLanguage", "XPath"
oXML.SetProperty "ServerHTTPRequest", True
oXML.validateOnParse = False
oXML.resolveExternals = False

oXML.setProperty "SelectionNamespaces", "xmlns:d=""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"""

oXML.Load "_test_.xml"

MsgBox oXML.SelectNodes("//d:Row").length ' Return 2

' Looping through all rows in Table
Set nodes = oXML.selectNodes("//d:Table//*")    
For i = 0 to nodes.length -1 
    Msgbox nodes(i).nodeName
Next

In that example I have prefixed the default namespace with d.

在这个示例中，我将默认名称空间以d作为前缀。

#1