从 XML 中提取数据字段到 Excel
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9925108/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Extract data fields from XML into Excel
提问by Joe
I have a huge Excel spreadsheet that contains records of Customers where each column is a field. There's a field called Demographics which contains survey results of Customers and it's entirely in XML format. That is each Customer has a survey result on their demographic info like Gender, Marital Status, Income, Age, etc. which is given in XML format. It is notable that the whole XML is like a big chunk of text in a cell of the spreadsheet which I can't use to analyze the data.
我有一个巨大的 Excel 电子表格，其中包含客户的记录，其中每一列都是一个字段。有一个名为 Demographics 的字段，它包含客户的调查结果，它完全采用 XML 格式。也就是说，每个客户都有一个关于他们的人口统计信息的调查结果，如性别、婚姻状况、收入、年龄等，以 XML 格式给出。值得注意的是，整个 XML 就像电子表格单元格中的一大块文本，我无法用来分析数据。
The problem is now I want to extract the demographic data of each Customer and present it as fields in the same spreadsheet, i.e., each Customer has an Age, Gender, etc. How can I do this? I have tried to Google this but it seems like nobody has the same problem. I'm using Excel 2007/2010.
现在的问题是我想提取每个客户的人口统计数据并将其显示为同一个电子表格中的字段，即每个客户都有年龄、性别等。我该怎么做？我试过谷歌这个，但似乎没有人有同样的问题。我正在使用 Excel 2007/2010。
回答by tohuwawohu
If the XML has a flat structure like this:
如果 XML 具有这样的平面结构：
you could just convert the "XML" column into an XML file and re-import it into your excel sheet. To do so, just
您可以将“XML”列转换为 XML 文件，然后将其重新导入到您的 Excel 工作表中。为此，只需
copy & paste the XML column into a text editor,
将 XML 列复制并粘贴到文本编辑器中，
<customer><age>34</age><gender>m</gender></customer>
<customer><age>38</age><gender>f</gender></customer>
add a opening/closing root element,
添加一个打开/关闭根元素，
<customers>
<customer><age>34</age><gender>m</gender></customer>
<customer><age>38</age><gender>f</gender></customer>
</customers>
and save as customers.xml.
并另存为customers.xml。
Now. import it back into excel, using XML-> Importfrom the Developer ToolsRibbon. Based on the "flat" XML structure, Excel will create columns for the single values:
现在。其重新导入到Excel中，使用XML- >Import从Developer Tools功能区。基于“平面”XML 结构，Excel 将为单个值创建列：
Of course this will work only if the XML structure is suitable, but if it does, it works without writing a macro or parsing the "XML" text content manually.
当然，这只有在 XML 结构合适的情况下才有效，但如果确实如此，则无需编写宏或手动解析“XML”文本内容即可工作。
回答by SivD
Open one of the xml form - go to file menu - share - export to excel - follow the instructions. In the export to excel you will find that, you can add all the xml forms if they are identical and export the data into one excel file in one go.
打开其中一个 xml 表单 - 转到文件菜单 - 共享 - 导出到 excel - 按照说明进行操作。在导出到excel中你会发现，你可以把所有相同的xml表格都添加进去，然后将数据一次性导出到一个excel文件中。
