开发者

Quick convert XML to Excel [closed]

Closed. This question needs to be more focused. It is not currently accepting answers.

Want to improve this question? Update the question so it focuses on one problem only by editing this post.

Closed 3 years ago.

开发者_高级运维 Improve this question

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


Why so complicated? Just open the File with File->Open choose xml and load it. See what'll happen.


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>


If you have Windows 7+, use PowerShell. It's pretty quick and easy.

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.note code to reflect the structure of your XML file.

Take for example the following contents of 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]$data = Get-Content myfile.xml

Now you can do all sorts of things like:

$data.GetElementsByTagName('note')

or simply

$data.xml.note.from


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.

There may be better ways, this is one simple way though.


  1. Open the XML file.
  2. Right click on the page and choose "Export to Microsoft Excel".
  3. Click on the excel page that opens and choose import.
  4. Excel will notify you that it will create its own schema. Hit OK.
  5. Excel will ask you where to put the data and will default to cell $A$1. Hit OK.
  6. Done :)
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜