如何通过Excel（支持VBA）创建包含多元素嵌套结构的XML文件？

Got it, let's tackle this problem where your Excel export is creating duplicate <term> nodes instead of grouping all <columnRef> entries under a single term. Here's a VBA solution that will generate exactly the XML structure you need from your existing spreadsheet.

Step 1: The VBA Code #

Open your Excel file, press Alt + F11 to open the VBA Editor, insert a new module, and paste this code:

Sub GenerateCustomXML()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim dataRange As Range
    Dim cell As Range
    Dim termDict As Object
    Dim termKey As String
    Dim colRefs As Collection
    Dim xmlDoc As Object
    Dim rootNode As Object
    Dim termNode As Object
    Dim customAttrsNode As Object
    Dim customAttrValueNode As Object
    Dim customAttrRefsNode As Object
    Dim colRefNode As Object
    Dim xmlPath As String
    
    ' Set the worksheet containing your data (change "Sheet1" to your sheet name)
    Set ws = ThisWorkbook.Sheets("Sheet1")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    Set dataRange = ws.Range("A2:C" & lastRow) ' Skip header row
    
    ' Use a dictionary to group column refs by term
    Set termDict = CreateObject("Scripting.Dictionary")
    
    ' Populate the dictionary with terms and their column references
    For Each cell In dataRange.Columns(1).Cells
        termKey = cell.Value
        If Not termDict.Exists(termKey) Then
            Set colRefs = New Collection
            termDict.Add termKey, colRefs
        End If
        ' Add table and column as a string pair (we'll split later)
        termDict(termKey).Add cell.Offset(0, 1).Value & "|" & cell.Offset(0, 2).Value
    Next cell
    
    ' Create XML document
    Set xmlDoc = CreateObject("MSXML2.DOMDocument")
    xmlDoc.async = False
    xmlDoc.preserveWhiteSpace = True
    
    ' Create root node (valid XML requires a single root element)
    Set rootNode = xmlDoc.createElement("terms")
    xmlDoc.appendChild rootNode
    
    ' Loop through each term in the dictionary to build XML nodes
    For Each termKey In termDict.Keys
        ' Create <term> node
        Set termNode = xmlDoc.createElement("term")
        termNode.setAttribute "name", termKey
        rootNode.appendChild termNode
        
        ' Create <customAttributes> node
        Set customAttrsNode = xmlDoc.createElement("customAttributes")
        termNode.appendChild customAttrsNode
        
        ' Create <customAttributeValue> node
        Set customAttrValueNode = xmlDoc.createElement("customAttributeValue")
        customAttrValueNode.setAttribute "customAttribute", "xyz" ' Hardcoded as per your example
        customAttrsNode.appendChild customAttrValueNode
        
        ' Create <customAttributeReferences> node
        Set customAttrRefsNode = xmlDoc.createElement("customAttributeReferences")
        customAttrValueNode.appendChild customAttrRefsNode
        
        ' Add all <columnRef> nodes for this term
        For Each colRefPair In termDict(termKey)
            Set colRefNode = xmlDoc.createElement("columnRef")
            colRefNode.setAttribute "table", Split(colRefPair, "|")(0)
            colRefNode.setAttribute "column", Split(colRefPair, "|")(1)
            customAttrRefsNode.appendChild colRefNode
        Next colRefPair
    Next termKey
    
    ' Save the XML file (change the path to your desired location)
    xmlPath = "C:\Your\Path\To\Output.xml"
    xmlDoc.Save xmlPath
    
    MsgBox "XML file generated successfully at: " & xmlPath, vbInformation
End Sub

Step 2: How to Use This Code #

1. Adjust the worksheet name: Change "Sheet1" in the code to match your actual sheet name where the data is stored.
2. Set the save path: Update the xmlPath variable to the folder where you want to save the final XML file.
3. Run the macro: Press F5 in the VBA Editor, or go back to Excel and run the GenerateCustomXML macro from the Developer tab.

Key Details #

• Dictionary grouping: The script uses a dictionary to group all <columnRef> entries by their corresponding term value, so we don't create duplicate <term> nodes.
• Hardcoded attributes: The customAttribute="xyz" is hardcoded since your example uses this value for all entries. If you need this to be dynamic (pulled from Excel), you can add an extra column to your sheet and modify the code to read it.
• XML root node: Valid XML requires exactly one root element, so the code adds a <terms> root. If you don't need this, you can adjust the code—but note that removing it will result in invalid XML.

Example Output #

Running this code on your sample data will generate the exact XML structure you need:

<terms>
  <term name="example 1">
    <customAttributes>
      <customAttributeValue customAttribute="xyz">
        <customAttributeReferences>
          <columnRef table="a" column="x"/>
          <columnRef table="b" column="x"/>
          <columnRef table="c" column="x"/>
        </customAttributeReferences>
      </customAttributeValue>
    </customAttributes>
  </term>
</terms>

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