将XML快速转换为Excel [英] Quick convert XML to Excel

问题描述

将XML中的112K行转换为Excel视图的最快速（最少的努力，而不是超级性能）的方式是什么？

解决方案

如果您使用Excel 2007并希望使用XSLT，最好的选择可能是使用 EXPath Zip模块功能可以修改现有的Excel .xslx文件。

然而，我的首选选项是使用小型Excel VBA宏。

我已经包括下面的示例代码用于称为加载的VBA过程 - 此示例使用XML DOM，因此您的XML的所有112K行将首先加载到内存中，但如果性能不是比SAX替代方法简单的问题。 p>

您需要修改 xpathToExtractRow 以适应您的XML输入结构。还有一个假设：XML行元素的直接子节点包含要作为文本节点导入的单元格数据，否则，您将需要使用 SelectNode 调用获取您需要的数据。

私有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
 
设置dom =新建DOMDocument60 
 dom.load（c：\test\source.xml）
设置表= ActiveSheet 
设置nodeList = dom.SelectNodes（xpathToExtractRow）
 
 rowCount = 0 
对于每个nodeRow在nodeList 
 rowCount = rowCount + 1 
 cellCount = 0 
对于每个nodeCell在nodeRow.ChildNodes 
 cellCount = cellCount + 1 
设置cellRange = sheet.Cells（rowCount，cellCount）
 cellRange.Value = nodeCell.Text 
下一个nodeCell 
下一个nodeRow 
  

End Sub

示例输入XML：

 <？xml version =1.0encoding =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> 
  

What is the quickest (as in least effort, not super performance) way to convert 112K rows in XML to a Excel view.

解决方案

If you're using Excel 2007 and want to use XSLT, your best bet would probably be use the EXPath Zip Module features to modify an existing Excel .xslx file.

My preferred option, however, would be to use a small Excel VBA Macro.

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.

You would need to modify xpathToExtractRow to 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 SelectNode call to get the data you require.

Private dom As 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 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>

这篇关于将XML快速转换为Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！