快速将 XML 转换为 Excel
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2631544/
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
Quick convert XML to Excel
提问by Bajji
What is the quickest (as in least effort, not super performance) way to convert 112K rows in XML to a Excel view.
将 XML 中的 112K 行转换为 Excel 视图的最快方法是什么（最省力，不是超级性能）。
采纳答案by pgfearo
If you're using Excel 2007 and want to use XSLT, your best bet would probably be use the EXPath Zip Modulefeatures to modify an existing Excel .xslx file.
如果您使用的是 Excel 2007 并希望使用 XSLT，那么最好的办法可能是使用EXPath Zip 模块功能来修改现有的 Excel .xslx 文件。
My preferred option, however, would be to use a small Excel VBA Macro.
但是，我的首选选项是使用小型 Excel VBA 宏。
I've included sample code below for a VBA procedure called 'load' - this sample uses the XML DOM, so all 112K rows of your XML will be first loaded into memory, but if performance isn't an issue its simpler than the SAX alternative.
我在下面包含了一个名为“加载”的 VBA 过程的示例代码 - 该示例使用 XML DOM，因此 XML 的所有 112K 行将首先加载到内存中，但如果性能不是问题，它比 SAX 更简单选择。
You would need to modify xpathToExtractRowto suit your XML input structure. There is also an assumption that the immediate child nodes of the XML row element contain the cell data you wish to import as text nodes, if not, you will need to use a SelectNodecall to get the data you require.
您需要修改xpathToExtractRow以适合您的 XML 输入结构。还有一个假设，即 XML 行元素的直接子节点包含您希望作为文本节点导入的单元格数据，如果没有，您将需要使用SelectNode调用来获取您需要的数据。
Private dom As DOMDocument60
私有 dom 作为 DOMDocument60
Public Sub load()
公共子加载（）
Dim nodeList As IXMLDOMNodeList
Dim nodeRow As IXMLDOMNode
Dim nodeCell As IXMLDOMNode
Dim rowCount As Integer
Dim cellCount As Integer
Dim rowRange As Range
Dim cellRange As Range
Dim sheet As Worksheet
Dim xpathToExtractRow As String
xpathToExtractRow = "/feed/row"
Set dom = New DOMDocument60
dom.load ("c:\test\source.xml")
Set sheet = ActiveSheet
Set nodeList = dom.SelectNodes(xpathToExtractRow)
rowCount = 0
For Each nodeRow In nodeList
rowCount = rowCount + 1
cellCount = 0
For Each nodeCell In nodeRow.ChildNodes
cellCount = cellCount + 1
Set cellRange = sheet.Cells(rowCount, cellCount)
cellRange.Value = nodeCell.Text
Next nodeCell
Next nodeRow
End Sub
结束子
Sample Input XML:
示例输入 XML：
<?xml version="1.0" encoding="utf-8"?>
<feed>
<row>
<firstname>joe</firstname>
<lastname>smith</lastname>
<country>jamaica</country>
</row>
<row>
<firstname>bill</firstname>
<lastname>coots</lastname>
<country>uk</country>
</row>
</feed>
回答by Friedrich
Why so complicated? Just open the File with File->Open choose xml and load it. See what'll happen.
为什么这么复杂？只需使用 File->Open 打开文件，选择 xml 并加载它。看看会发生什么。
回答by Roland Penner
If you have Windows 7+, use PowerShell. It's pretty quick and easy.
如果您使用的是 Windows 7+，请使用 PowerShell。这非常快速和容易。
One-liner:
单线：
([xml](Get-Content myfile.xml)).xml.note | Export-Csv myoutput.csv
For the one-liner to work you need to modify the .xml.notecode to reflect the structure of your XML file.
要使单行工作正常，您需要修改.xml.note代码以反映 XML 文件的结构。
Take for example the following contents of myfile.xml:
以myfile.xml的以下内容为例：
<xml>
<note>
<to>Tove</to>
<from>Jani</from>
<heading>Reminder</heading>
<body>Don't forget me this weekend!</body>
</note>
<note>
<to>Jason</to>
<from>Alice</from>
<heading>Help</heading>
<body>I can't figure this out.</body>
</note>
</xml>
You can assign the XML to a variable like this:
您可以将 XML 分配给这样的变量：
[xml]$data = Get-Content myfile.xml
Now you can do all sorts of things like:
现在你可以做各种各样的事情，比如：
$data.GetElementsByTagName('note')
or simply
或者干脆
$data.xml.note.from
回答by Eric
- Open the XML file.
- Right click on the page and choose "Export to Microsoft Excel".
- Click on the excel page that opens and choose import.
- Excel will notify you that it will create its own schema. Hit OK.
- Excel will ask you where to put the data and will default to cell $A$1. Hit OK.
- Done :)
- 打开 XML 文件。
- 右键单击页面并选择“导出到 Microsoft Excel”。
- 单击打开的 Excel 页面并选择导入。
- Excel 将通知您它将创建自己的架构。点击确定。
- Excel 会询问您将数据放在哪里，并将默认为单元格 $A$1。点击确定。
- 完毕 ：）
回答by dcp
Probably just read the XML in some high level language (JAVA, C#, etc. all have such facilities), write the file out as a .csv file, and then import it into excel using the Data->Import feature.
可能只是用某种高级语言（JAVA、C# 等都有这样的功能）读取 XML，将文件写为 .csv 文件，然后使用数据->导入功能将其导入到 excel 中。
There may be better ways, this is one simple way though.
可能有更好的方法，尽管这是一种简单的方法。
