博客

在有些的工程里面，需要使用excel进行解析xml文件，并且进行内容的提取

并对其中的内容进行相关的提取和使用，本文主要使用excel中的vba进行对xml的识别和操作，主要是代码

XML的格式说明


https://www.runoob.com/xml/xml-intro.html

Sub test001()
'.........define the function .
'...............function(file,row,column) ........
On Error Resume Next
Dim xmlDom  As New MSXML2.DOMDocument60
Dim xmlNode1, xmlNode2, xmlNode3 As MSXML2.IXMLDOMNode
Dim xmlNodeList As MSXML2.IXMLDOMNodeList
folder_location = ThisWorkbook.Path
a = xmlDom.Load(folder_location & "\1.xml")
Set root = xmlDom.DocumentElement 'get the root
With root.ChildNodes(0)
    For i = 0 To .ChildNodes.Length
    
    'max the valuea
        row_a = Range("A10000").End(xlUp).row + 1
        Row_B = Range("B10000").End(xlUp).row + 1
        Row_c = Range("C10000").End(xlUp).row + 1
        Row_d = Range("D10000").End(xlUp).row + 1
        If row_a >= Row_B And row_a >= Row_c And row_a >= Row_d Then
        row_X = row_a
        ElseIf Row_B >= row_a And Row_B >= Row_c And Row_B >= Row_d Then
        row_X = Row_B
        ElseIf Row_c >= row_a And Row_c >= Row_B And Row_c >= Row_d Then
        row_X = Row_c
        ElseIf Row_d >= row_a And Row_d >= Row_B And Row_d >= Row_c Then
        row_X = Row_d
        
        End If
        
        Worksheets(1).Cells(row_X, 1) = .ChildNodes(i).Attributes.getNamedItem("name").NodeValue
        For j = 0 To .ChildNodes(i).ChildNodes.Length
            Worksheets(1).Cells(row_X + j, 2) = .ChildNodes(i).ChildNodes(j).Attributes.getNamedItem("name").NodeValue
            For k = 0 To .ChildNodes(i).ChildNodes(j).ChildNodes.Length
                Worksheets(1).Cells(row_X + j + k, 3) = .ChildNodes(i).ChildNodes(j).ChildNodes(k).Attributes.getNamedItem("name").NodeValue
                Worksheets(1).Cells(row_X + j + k, 4) = .ChildNodes(i).ChildNodes(j).ChildNodes(k).Attributes.getNamedItem("Value").NodeValue
                Worksheets(1).Cells(row_X + j + k, 5) = .ChildNodes(i).ChildNodes(j).ChildNodes(k).Attributes.getNamedItem("OID").NodeValue
            Next
        Next
        
    Next
End With

End Sub