导出到XML时合并元素 [英] Merge elements when exporting to XML

问题描述

我正在尝试将Excel中的表导出到XML文件.我创建了一个架构，但是并没有得到我期望的结果.这是我的表格的一个简单示例:我的表格.

I'm trying to export a table in Excel to an XML file. I created a schema but I don’t have exactly the result I expect. Here is a simple example of my table: my table.

我的模式:

<?xml version="1.0" encoding="UTF-8"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="list">
    <xs:complexType>
        <xs:sequence minOccurs="0" maxOccurs="2">
             <xs:element name="Group" type="groupType" minOccurs="0" maxOccurs="unbounded" />
        </xs:sequence>
    </xs:complexType>
</xs:element>

<xs:complexType name="groupType">
    <xs:sequence>
        <xs:element name="City" type="cityType" />
    </xs:sequence>
    <xs:attribute name="name" />
</xs:complexType>
<xs:complexType name="cityType">
    <xs:sequence>
        <xs:element name="value" type="xs:string" />
    </xs:sequence>
    <xs:attribute name="name" />
</xs:complexType>

这是我导出到XML时得到的结果:

This is the result I get when I export to XML:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<list>
    <Group name="groupA">
        <City name="Dublin">
            <value>yes</value>
        </City>
    </Group>
    <Group name="groupA">
        <City name="Prague">
            <value>yes</value>
        </City>
    </Group>
    <Group name="groupA">
        <City name="Sofia">
            <value>no</value>
        </City>
    </Group>
    <Group name="groupA">
        <City name="Tunis">
            <value>yes</value>
        </City>
    </Group>
    <Group name="groupB">
        <City name="Paris">
            <value>no</value>
        </City>
    </Group>
    <Group name="groupB">
        <City name="Lisbon">
            <value>no</value>
        </City>
    </Group>
    <Group name="groupB">
        <City name="Madrid">
            <value>no</value>
        </City>
    </Group>
</list>

这还不错，但是我想在同一节点中合并具有相同组名(groupA和groupB)的行.换句话说，我希望得到以下结果:

It's not bad, but I would like to merge the rows which have the same group name (groupA and groupB) in the same node. In other terms, I would like to have this result:

<list>
    <Group name="groupA">
        <City name="Dublin">
            <value>yes</value>
        </City>
        <City name="Prague">
            <value>yes</value>
        </City>
        <City name="Sofia">
            <value>no</value>
        </City>
        <City name="Tunis">
            <value>yes</value>
        </City>
    </Group>
    <Group name="groupB">
        <City name="Paris">
            <value>no</value>
        </City>
        <City name="Lisbon">
            <value>no</value>
        </City>
        <City name="Madrid">
            <value>no</value>
        </City>
    </Group>
</list>

我该如何在架构中完成所需的结果?

What should I do in my schema to make the result like I want?

推荐答案

Excel仅能直接以表格形式导出XML.这是一系列数据行(记录)，其中每个数据字段都只有一个元素.它无法导出列表列表.即:一个项目列表具有第二个项目列表.请参阅 https://support. office.com/zh-CN/article/Export-XML-data-0b21f51b-56d6-48f0-83d9-a89637cd4360 .

Excel is only able directly exporting XML in table form. This is a sequence of data rows (records) in which each data field has exactly one element. It is not able exporting list of lists. That is: One list of items has a second list of items. See https://support.office.com/en-us/article/Export-XML-data-0b21f51b-56d6-48f0-83d9-a89637cd4360.

但是您想要列表列表.一组小组，每个小组都有一个城市序列.该架构为:

But you want list of lists. A sequence of Groups, each with a sequence of Cities. The schema would be:

<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:element name="list">
    <xs:complexType>
      <xs:sequence>
        <xs:element name="Group" maxOccurs="unbounded">
          <xs:complexType>
            <xs:sequence>
              <xs:element name="City" maxOccurs="unbounded">
                <xs:complexType>
                  <xs:sequence>
                    <xs:element type="xs:string" name="value"/>
                  </xs:sequence>
                  <xs:attribute type="xs:string" name="name"/>
                </xs:complexType>
              </xs:element>
            </xs:sequence>
            <xs:attribute type="xs:string" name="name"/>
          </xs:complexType>
        </xs:element>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>

但是根据记录，不能使用Excel的XML导出功能直接将其导出.

But as documented this can't be exported directly with Excel's XML export feature.

因此，我建议为此使用VBA.下面将根据显示的数据创建所需的XML.

So I suggest using VBA for this. The following will create the needed XML from the shown data.

Sub testXLStoXML()

 Set oXMLDoc = CreateObject("MSXML2.DOMDocument")
 Set oPI = oXMLDoc.createProcessingInstruction("xml", "version=""1.0"" encoding=""UTF-8"" standalone=""yes""")
 Set oRoot = oXMLDoc.createNode(1, "list", "")
 oXMLDoc.appendChild oRoot
 oXMLDoc.InsertBefore oPI, oXMLDoc.ChildNodes.Item(0)

 With ActiveSheet

  lRow = 2
  sGroupName = ""

  Do While .Cells(lRow, 1).Value <> ""

   sGroupName = .Cells(lRow, 1).Value
   Set oElmGroup = oXMLDoc.createNode(1, "Group", "")
   oXMLDoc.DocumentElement.appendChild oElmGroup
   Set oAttr = oXMLDoc.createNode(2, "name", "")
   oAttr.NodeValue = sGroupName
   oElmGroup.setAttributeNode oAttr

   Do While .Cells(lRow, 1).Value = sGroupName

    Set oElmCity = oXMLDoc.createNode(1, "City", "")
    Set oAttr = oXMLDoc.createNode(2, "name", "")
    oAttr.NodeValue = .Cells(lRow, 2).Value
    oElmCity.setAttributeNode oAttr

    Set oElmValue = oXMLDoc.createNode(1, "Value", "")
    oElmValue.appendChild oXMLDoc.createTextNode(.Cells(lRow, 3).Value)

    oElmCity.appendChild oElmValue
    oElmGroup.appendChild oElmCity

    lRow = lRow + 1

   Loop

  Loop

 End With

 MsgBox oXMLDoc.XML

 oXMLDoc.Save "test.xml"

End Sub

运行宏时，具有显示数据的工作表必须是活动的工作表.

The Worksheet with the shown data must be the active worksheet while the macro is running.

这篇关于导出到XML时合并元素的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！