读取Excel生成的XML时XPath报错，如何正确访问ActiveRow节点？

架构老林

2026-7-6

如何正确读取Excel生成的XML中的指定节点？

我尝试读取由Excel生成的XML文件（由XLSX转换而来），但在指定XML节点路径读取节点时出现错误，错误信息如下：

System.Xml.XPath.XPathException: ''/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"/WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel"/Panes/Pane' has an invalid token.'

我的VB.NET代码如下：

Dim cadA As String = "Workbook xmlns=" & Chr(34) & "urn:schemas-Microsoft-com:office:spreadsheet" & Chr(34) & " "
Dim cadB As String = "xmlns:o=" & Chr(34) & "urn:schemas-Microsoft-com:office:office" & Chr(34) & " "
Dim cadC As String = "xmlns:x=" & Chr(34) & "urn:schemas-Microsoft-com:office:Excel" & Chr(34) & " "
Dim cadD As String = "xmlns:ss=" & Chr(34) & "urn:schemas-microsoft-com:office:spreadsheet" & Chr(34) & " "
Dim cadE As String = "xmlns:html=" & Chr(34) & "http://www.w3.org/TR/REC-html40" & Chr(34)
Dim cadF As String = "WorksheetOptions " & "xmlns=" & Chr(34) & "urn:schemas-microsoft-com:office:excel" & Chr(34)
Dim node As String = ""
Dim main As String = cadA & cadB & cadC & cadD & cadE
Dim myOpenDialog As New OpenFileDialog
Dim varNodes, varNodesB, varNodesC, varNodesD, varNodesE, varNodesF, varNodesG As Integer

myOpenDialog.Filter = "XML Files (*.xml*)|*.xml"

If myOpenDialog.ShowDialog = Windows.Forms.DialogResult.OK Then

    Dim documentoxml As XmlDocument
    Dim nodelist As XmlNodeList
    Dim nodo As XmlNode
    documentoxml = New XmlDocument
    Dim strPath As String = myOpenDialog.FileName
    strPathOpened = strPath
    documentoxml.Load(strPath)

    nodelist = documentoxml.SelectNodes("/" & main & "/" & cadF & "/Panes" & "/Pane")
    For Each nodo In nodelist
        TextBox1.Text = nodo.ChildNodes(0).InnerText
        node = nodo.ChildNodes(1).InnerText
    Next
End if

Excel生成的XML代码如下，我想要读取其中的ActiveRow子节点：

<?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>Power BI</Author>
  <LastAuthor>TEST</LastAuthor>
  <Created>2016-07-06T08:22:49Z</Created>
  <LastSaved>2023-11-10T18:35:26Z</LastSaved>
  <Version>16.00</Version>
 </DocumentProperties>
 <OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
  <AllowPNG/>
 </OfficeDocumentSettings>
 <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
  <WindowHeight>5460</WindowHeight>
  <WindowWidth>16224</WindowWidth>
  <WindowTopX>32767</WindowTopX>
  <WindowTopY>32767</WindowTopY>
  <ProtectStructure>False</ProtectStructure>
  <ProtectWindows>False</ProtectWindows>
 </ExcelWorkbook>
 <Styles>
  <Style ss:ID="Default" ss:Name="Normal">
   <Alignment ss:Vertical="Bottom"/>
   <Borders/>
   <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000"/>
   <Interior/>
   <NumberFormat/>
   <Protection/>
  </Style>
  <Style ss:ID="s17">
   <NumberFormat ss:Format="Short Date"/>
  </Style>
  <Style ss:ID="s22">
   <NumberFormat ss:Format="Standard"/>
  </Style>
 </Styles>
 <Worksheet ss:Name="Sheet1">
  <Table ss:ExpandedColumnCount="9" ss:ExpandedRowCount="6" x:FullColumns="1"
   x:FullRows="1" ss:DefaultRowHeight="14.4">
   <Row>
    <Cell><Data ss:Type="String">Applied filters:&#10;Description is</Data></Cell>
   </Row>
   <Row ss:Index="3">
    <Cell><Data ss:Type="String">Description</Data></Cell>
    <Cell><Data ss:Type="String">Product ID</Data></Cell>
    <Cell><Data ss:Type="String">Cost</Data></Cell>
    <Cell><Data ss:Type="String">Currency</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="String">Test A</Data></Cell>
    <Cell><Data ss:Type="String">ID 1</Data></Cell>
    <Cell ss:StyleID="s22"><Data ss:Type="Number">1000</Data></Cell>
    <Cell><Data ss:Type="String">USD</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="String">Test B</Data></Cell>
    <Cell><Data ss:Type="String">ID 2</Data></Cell>
    <Cell ss:StyleID="s22"><Data ss:Type="Number">2000</Data></Cell>
    <Cell><Data ss:Type="String">USD</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="String">Test C</Data></Cell>
    <Cell><Data ss:Type="String">ID 3</Data></Cell>
    <Cell ss:StyleID="s22"><Data ss:Type="Number">3000</Data></Cell>
    <Cell><Data ss:Type="String">MXN</Data></Cell>
   </Row>
  </Table>
  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
   <PageSetup>
    <Header x:Margin="0.3"/>
    <Footer x:Margin="0.3"/>
    <PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
   </PageSetup>
   <Selected/>
   <Panes>
    <Pane>
     <Number>3</Number>
     <ActiveRow>3</ActiveRow>
    </Pane>
   </Panes>
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
 </Worksheet>
</Workbook>

问题原因

你错误地将XML节点的命名空间属性直接拼接到XPath路径中，这不符合XPath的语法规则，导致解析报错。正确的做法是使用XmlNamespaceManager来管理命名空间，然后在XPath中通过前缀引用对应的命名空间。

解决步骤

定义命名空间前缀和对应的URI：根据XML中的命名空间，给每个URI分配一个简短的前缀，方便在XPath中使用。
创建并配置XmlNamespaceManager：将命名空间前缀和URI添加到管理器中。
使用带命名空间前缀的XPath查询节点：在SelectNodes或SelectSingleNode方法中传入命名空间管理器。

修改后的VB.NET代码

Dim myOpenDialog As New OpenFileDialog
Dim node As String = ""
myOpenDialog.Filter = "XML Files (*.xml*)|*.xml"

If myOpenDialog.ShowDialog = Windows.Forms.DialogResult.OK Then
    Dim documentoxml As New XmlDocument
    Dim strPath As String = myOpenDialog.FileName
    strPathOpened = strPath
    documentoxml.Load(strPath)

    ' 创建命名空间管理器
    Dim nsManager As New XmlNamespaceManager(documentoxml.NameTable)
    ' 添加Workbook的默认命名空间，前缀设为ss
    nsManager.AddNamespace("ss", "urn:schemas-microsoft-com:office:spreadsheet")
    ' 添加WorksheetOptions所在的命名空间，前缀设为x
    nsManager.AddNamespace("x", "urn:schemas-microsoft-com:office:excel")

    ' 使用带前缀的XPath查询Pane节点
    Dim nodelist As XmlNodeList = documentoxml.SelectNodes("/ss:Workbook/ss:Worksheet/x:WorksheetOptions/x:Panes/x:Pane", nsManager)
    For Each nodo As XmlNode In nodelist
        ' 直接通过节点名称获取ActiveRow，避免依赖ChildNodes索引（更稳定）
        Dim activeRowNode As XmlNode = nodo.SelectSingleNode("x:ActiveRow", nsManager)
        If activeRowNode IsNot Nothing Then
            node = activeRowNode.InnerText
            TextBox1.Text = node ' 直接显示ActiveRow的值
        End If
    Next
End If

关键说明

不要在XPath中直接写节点的属性（比如xmlns="..."），XPath通过前缀映射命名空间来识别不同命名空间下的节点。
使用SelectSingleNode直接获取ActiveRow节点比依赖ChildNodes索引更可靠，避免节点顺序变化导致的错误。
确保命名空间的URI和XML中的完全一致，包括大小写（比如XML中的x命名空间是urn:schemas-microsoft-com:office:excel，要完全匹配）。

内容的提问来源于stack exchange，提问作者LARC

读取Excel生成的XML时XPath报错，如何正确访问ActiveRow节点？

问题原因 #

解决步骤 #

修改后的VB.NET代码 #

关键说明 #

问题原因

解决步骤

修改后的VB.NET代码

关键说明