如何用Excel遍历XML所有节点和属性

[fly_19881005]

我有一个xml, 我打算用Microsoft.XMLDOM去遍历里面的节点和属性,然后把值写到excel里面, 但是Microsoft.XMLDOM好像没方法好像hasNodes去判断是否有attribute, 然后红色那段vba代码会出错. 请问有什么别的好方法? 用On Error goto?

xml:
<section id="1" initialInstances="1" name="Initialization" orderBy="1">
<section_control_action actionId="586" description="Enable Existing Cover" targetSectionId="11">
<conditions logic="(OR A B C)">
<literal_condition actionValueExpression="587" label="A" literalValue="Y" operator="="/>
<literal_condition actionValueExpression="591" label="B" literalValue="Y" operator="="/>
<literal_condition actionValueExpression="589" label="C" literalValue="Y" operator="="/>
</conditions>
<literal_value>1</literal_value>
<activation>128</activation>
</section_control_action>
<direct_assertions name="Death Cover">
<datatype name="Text"/>
<direct_assertion actionId="587" description="Y" priority="2">
<conditions logic="A">
<literal_condition actionValueExpression="386" label="A" literalValue="DEATH" operator="="/>
</conditions>
<literal_value>Y</literal_value>
<activation>169</activation>
</direct_assertion>
<direct_assertion actionId="588" description="N" priority="1">
<literal_value>N</literal_value>
<activation>169</activation>
</direct_assertion>
</direct_assertions>
<direct_assertions name="SCI Cover">
<datatype name="Text"/>
<direct_assertion actionId="591" description="Y" priority="2">
<conditions logic="A">
<literal_condition actionValueExpression="388" label="A" literalValue="IP" operator="="/>
</conditions>
<literal_value>Y</literal_value>
<activation>169</activation>
</direct_assertion>
<direct_assertion actionId="592" description="N" priority="1">
<literal_value>N</literal_value>
<activation>169</activation>
</direct_assertion>
</direct_assertions>
<direct_assertions name="TPD Cover">
<datatype name="Text"/>
<direct_assertion actionId="589" description="Y" priority="2">
<conditions logic="A">
<literal_condition actionValueExpression="387" label="A" literalValue="TPD" operator="="/>
</conditions>
<literal_value>Y</literal_value>
<activation>169</activation>
</direct_assertion>
<direct_assertion actionId="590" description="N" priority="1">
<literal_value>N</literal_value>
<activation>169</activation>
</direct_assertion>
</direct_assertions>
<section/>


VBA:

Sub GeChildItem(xmlRoot, iRow, iCol)

Dim xmlChild

If xmlRoot.HasChildNodes Then
End If

For i = 0 To xmlRoot.ChildNodes.Length - 1
iRow = iRow + 1
Application.Cells(iRow, iCol) = xmlRoot.ChildNodes.Item(i).BaseName
Application.Cells(iRow, iCol + 1) = xmlRoot.ChildNodes.Item(i).nodeTypedValue
For j = 0 to xmlRoot.ChildNodes.Item(i).Attributes.Length - 1
Application.Cells(iRow, iCol + 2) = xmlRoot.ChildNodes.Item(i).Attributes(j).nodetypevalue
Next
Call GeChildItem(xmlRoot.ChildNodes.Item(i), iRow, iCol)
Next
End Sub

[一如既往哈]

nodetypevalue拼写错误!

[fly_19881005]

引用 1 楼 Topc008 的回复:

nodetypevalue拼写错误!

我知道....在程序里没写错.这里打错了而已. 这XML有点大和复杂..有的node是没attribute的.所以没有Attributes.Length这属性, 如果直接xmlRoot.ChildNodes.Item(i).Attributes.Length去判断的话.会有错..我用on error resume, 然后用is nothing 加上Attributes.Length>0也不能正确判断这个node是否有attribute.

[一如既往哈]

参考:

Sub Test()
    Dim xmlDoc As DOMDocument60, xmlFile As String
    xmlFile = "d:\123.xml"
    Set xmlDoc = New DOMDocument60
    xmlDoc.async = False
    xmlDoc.Load xmlFile
    If xmlDoc.parseError.errorCode <> 0 Then
        MsgBox "打开错误：" & xmlDoc.parseError.reason
        Exit Sub
    End If
    Cells.Clear
    Dim xmlNode As IXMLDOMNode, i As Integer
    Set xmlNode = xmlDoc.documentElement
    With xmlNode
        Cells(1, 1) = .baseName
        If .Attributes.Length > 0 Then
            For i = 0 To .Attributes.Length - 1
            Cells(1, i + 2) = .Attributes(i).baseName & "=" & .Attributes(i).nodeTypedValue
            Next
        End If
    End With
    GetAllNodes xmlNode, 2, 2
    Set xmlNode = Nothing
    Set xmlDoc = Nothing
End Sub
Sub GetAllNodes(ByVal xmlRootNode As IXMLDOMNode, iR As Long, iC As Long)
    Dim xmlNode As IXMLDOMNode, i As Integer, k As Integer
    For Each xmlNode In xmlRootNode.childNodes
        With xmlNode
            Cells(iR, iC) = .baseName
            If .baseName <> "" Then
                k = .Attributes.Length
                If k > 0 Then
                    For i = 0 To k - 1
                        Cells(iR, iC + i + 1) = .Attributes(i).baseName & "=" & .Attributes(i).nodeTypedValue
                    Next
                End If
                iR = iR + 1
                If .childNodes.Length > 0 Then GetAllNodes xmlNode, iR, iC + 1
            End If
        End With
    Next
End Sub

在excel的vba里执行，引用xml 6.0

[fly_19881005]

引用 3 楼 Topc008 的回复:

参考:

Sub Test()
    Dim xmlDoc As DOMDocument60, xmlFile As String
    xmlFile = "d:\123.xml"
    Set xmlDoc = New DOMDocument60
    xmlDoc.async = False
    xmlDoc.Load xmlFile
    If xmlDoc.parseError.errorCode <> 0 Then
        MsgBox "打开错误：" & xmlDoc.parseError.reason
        Exit Sub
    End If
    Cells.Clear
    Dim xmlNode As IXMLDOMNode, i As Integer
    Set xmlNode = xmlDoc.documentElement
    With xmlNode
        Cells(1, 1) = .baseName
        If .Attributes.Length > 0 Then
            For i = 0 To .Attributes.Length - 1
            Cells(1, i + 2) = .Attributes(i).baseName & "=" & .Attributes(i).nodeTypedValue
            Next
        End If
    End With
    GetAllNodes xmlNode, 2, 2
    Set xmlNode = Nothing
    Set xmlDoc = Nothing
End Sub
Sub GetAllNodes(ByVal xmlRootNode As IXMLDOMNode, iR As Long, iC As Long)
    Dim xmlNode As IXMLDOMNode, i As Integer, k As Integer
    For Each xmlNode In xmlRootNode.childNodes
        With xmlNode
            Cells(iR, iC) = .baseName
            If .baseName <> "" Then
                k = .Attributes.Length
                If k > 0 Then
                    For i = 0 To k - 1
                        Cells(iR, iC + i + 1) = .Attributes(i).baseName & "=" & .Attributes(i).nodeTypedValue
                    Next
                End If
                iR = iR + 1
                If .childNodes.Length > 0 Then GetAllNodes xmlNode, iR, iC + 1
            End If
        End With
    Next
End Sub

在excel的vba里执行，引用xml 6.0

昨晚试了下,直接定义局部整形变量L,再用Attributes.Length复制给它,然后可以判断L是否大于0而判断是否有Attribute, 今天早上看到3楼的回复, 十分有用, 结贴.谢谢了