读取在 Excel 中创建的 XML 文件

Read an XML file created in Excel

提问人：LARC 提问时间：11/14/2023 最后编辑：LARC 更新时间：11/15/2023 访问量：65

问：

我正在尝试读取在 Excel 中创建的 XML 文件（xlsx 到 XML），但是当我尝试读取我在 XML 中提供节点地址的部分中的节点时出现错误，错误如下......

System.Xml.XPath.XPathException： ''/工作簿 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' 具有 令牌无效。

我的代码如下。

    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 vb.net 节点

评论

0赞 Ken White 11/14/2023
请不要上传代码/数据/错误的图像。仅当没有其他方法可以演示问题（例如 UI 或网页中的呈现问题）时，才应使用图像。
1赞 LARC 11/14/2023
@ken-white，对不起，图像已停用并添加了XML文件代码，问候。
0赞 Enigmativity 11/15/2023
为什么不使用现代而不是旧的呢？XDocumentXmlDocument

答：

1赞 Think2826 11/15/2023 #1

我在下面的方法中访问了“ActiveRow”。应用它

 Try

         Dim myOpenDialog As New OpenFileDialog

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

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

            Dim documentoxml As XmlDocument

            documentoxml = New XmlDocument
            Dim strPath As String = myOpenDialog.FileName

            documentoxml.Load(strPath)

            Dim nsManager As New XmlNamespaceManager(documentoxml.NameTable)
            nsManager.AddNamespace("ss", "urn:schemas-microsoft-com:office:spreadsheet")
            nsManager.AddNamespace("x", "urn:schemas-microsoft-com:office:excel")


            Dim activeRowNode As XmlNode = documentoxml.SelectSingleNode("/ss:Workbook/ss:Worksheet/x:WorksheetOptions/x:Panes/x:Pane/x:ActiveRow", nsManager)

            If activeRowNode IsNot Nothing Then
               Dim activeRowValue As Integer
               If Integer.TryParse(activeRowNode.InnerText, activeRowValue) Then

                  Console.WriteLine("ActiveRow Value: " & activeRowValue)
               Else
                  Console.WriteLine("ActiveRow value cannot be read")
               End If
            Else
               Console.WriteLine("ActiveRow Element not found")
            End If

         End If
      Catch ex As Exception
         MsgBox(ex.ToString)
      End Try

评论

0赞 Enigmativity 11/15/2023
OP 没有无用的异常处理代码。为什么要添加它？
1赞 Shahram Alemzadeh 11/15/2023 #2

对于这种特殊情况，无需使用命名空间管理器。只需使用或：name()local-name()

dim ActiveRow As xmlnode = documentoxml.selectsinglenode("//*[name()='ActiveRow']")

上一个：声明方法始终引发异常的属性，以满足BC42105

下一个：使用具有不同名称的相同类型的多个元素反序列化 JSON