xml - 如何在 Excel 中打开 XML 文件

Question

我想用以下功能对我的按钮进行编程:

打开并选择一个 xml 文件，然后在“table2”中写入指标名称 ID M1-M10 及其所属值。

我成功打开并选择了一个 xml 文件:

Private Sub btn_load_xml_Click()
'-----------Open file---------------------------
Dim Filter As String, Title As String
Dim FilterIndex As Integer
Dim Filename As Variant

' File filters
Filter = "XML Files (*.xml),*.xml"
' Default Filter to *.*

FilterIndex = 3
' Set Dialog Caption
Title = "Select a File to Open"

' Select Start Drive & Path
ChDrive ("C")
ChDir ("C:")
With Application
    ' Set File Name to selected File
    Filename = .GetOpenFilename(Filter, FilterIndex, Title)
    ' Reset Start Drive/Path
    ChDrive (Left(.DefaultFilePath, 1))
    ChDir (.DefaultFilePath)
End With

' Exit on Cancel
If Filename = False Then
    Exit Sub
End If

' Open File   
MsgBox Filename, vbInformation, "File Opened" ' This can be removed
'------------- Load XML in table2------------------

'------------- END Load XML in table2------------------
End Sub

但是我如何加载 XML 文件并使用它？

这是我的 XML 结构

<?xml version="1.0" encoding="UTF-8" ?>

<sourcemonitor_metrics>
  <project version="3.3">
    <project_name>metric_auswertung</project_name>
    <project_directory>C:\Users\SULAS\Desktop\Metric_auswertung</project_directory>
    <project_language>C</project_language>

    <ignore_headers_footers>false</ignore_headers_footers>
    <export_raw_numbers>false</export_raw_numbers>
    <metric_names name_count="13">
      <metric_name id="M0" type="number">Lines</metric_name>
      <metric_name id="M1" type="number">Statements</metric_name>
      <metric_name id="M2" type="percent" divisor="M1">Percent Branch Statements</metric_name>
      <metric_name id="M3" type="percent" divisor="M0">Percent Lines with Comments</metric_name>
      <metric_name id="M4" type="number">Functions</metric_name>
      <metric_name id="M5" type="average" divisor="M4">Average Statements per Function</metric_name>
      <metric_name id="M6" type="string">Line Number of Most Complex Function</metric_name>
      <metric_name id="M7" type="string">Name of Most Complex Function</metric_name>
      <metric_name id="M8" type="maximum">Complexity of Most Complex Function</metric_name>
      <metric_name id="M9" type="string">Line Number of Deepest Block</metric_name>
      <metric_name id="M10" type="maximum">Maximum Block Depth</metric_name>
      <metric_name id="M11" type="average" values="block_depths">Average Block Depth</metric_name>
      <metric_name id="M12" type="average">Average Complexity</metric_name>
    </metric_names>
    <checkpoints checkpoint_count="1">
      <checkpoint checkpoint_name="Metric_Auswertung" ignore_blank_lines="false" modified_complexity="true" checkpoint_date="2013-02-25">
        <files file_count="3">
          <file file_name="Mcu - Kopie.c">
            <metrics metric_count="13">
              <metric id="M0">603</metric>
              <metric id="M1">183</metric>
              <metric id="M2">26,2</metric>
              <metric id="M3">23,2</metric>
              <metric id="M4">11</metric>
              <metric id="M5">24,6</metric>
              <metric id="M6">321</metric>
              <metric id="M7">Mcu_GetPllStatus()</metric>
              <metric id="M8">15</metric>
              <metric id="M9">235</metric>
              <metric id="M10">6</metric>
              <metric id="M11">1,97</metric>
              <metric id="M12">6,00</metric>
            </metrics>
          </file>
        </files>
      </checkpoint>
    </checkpoints>
  </project>
</sourcemonitor_metrics>

Answer 1

您可以使用 MSXML 直接处理 XML 文档。有关完整引用，请参阅 MSXML ，然后查看DOM reference尤其。要最轻松地使用 MSXML，请打开 VBA 编辑器并转到“工具”>“引用”。在“Microsoft XML, vX.0”旁边添加复选标记，其中 X 是可用的最新版本。

“如何使用 XML”超出了本问题的范围，但以下是帮助您入门的基本 VBA:

Sub btn_load_xml_Click()

    ' Get file name ...

    Dim oDoc As New MSXML2.DOMDocument60
    Dim xMetricNames As IXMLDOMNodeList
    Dim xMetricName As IXMLDOMElement
    Dim xMetrics As IXMLDOMNode
    Dim xMetric As IXMLDOMElement
    Dim mtID As String, mtName As String, mtValue As String

    ' Load from file
    oDoc.Load FileName

    ' Select needed nodes
    Set xMetrics = oDoc.SelectSingleNode("//project/checkpoints/checkpoint/files/file/metrics")
    Set xMetricNames = oDoc.SelectNodes("//project/metric_names/metric_name")
    For Each xMetricName In xMetricNames
        mtName = xMetricName.Text
        mtID = xMetricName.getAttribute("id")
        mtValue = xMetrics.SelectSingleNode("metric[@id='" & mtID & "']").Text

        ' Do whatever you want with these values
    Next

    Set oDoc = Nothing

End Sub