将xml转换为xls [英] convert xml to xls

问题描述

我正在寻找有关如何实现将基于Web的XML数据导入电子表格的方法的想法.理想的解决方案是在每次打开电子表格时都很少或没有用户交互(即，我希望不必告诉人们运行宏)来更新电子表格.

I'm looking for ideas on how to implement a way to get web based XML data into a spreadsheet. The ideal solution would update the spreadsheet every time it's opened with little or no user interaction (.i.e, I'd prefer to not have to tell people to run a macro).

但是，我的第一个想法(可能是我最终会想到的)是一个Perl脚本，该脚本下载XML，对其进行解析并吐出一个电子表格-这不会更新电子表格，我将拥有以某种类型的时间表生成文档(是的，我更喜欢完全自动化并编写自己的工作代码:)).我(想想)想要的是可以接受URI的XML的odbc，这可能是理想的.这样，也许我可以将XPath映射到列，并且电子表格每次打开时都会自动更新.或者，也许在VBA中有一种获取URI并解析XML并在打开工作簿时运行它的方法?

however, my first thought (and probably what I'll end up going with) is a Perl script that downloads the XML, parses it, and spits out a spreadsheet - this won't update the spreadsheet and I'll have to generate the document on some type of schedule (yes, i prefer complete automation and coding myself out of jobs :) ). what i (think i) want is odbc for XML that would accept a URI, that would probably be ideal. this way, maybe i could map the XPath to columns and the spreadsheet would update automagically every time it was opened. or, maybe there's a way in VBA to get a URI and parse XML and have it run when the workbook is opened?

推荐答案

使用DOM解析XML文件，然后将相关值吐到工作表上.这是一个简单的工作示例，可以帮助您入门:

Parse the XML file using DOM, then spit out the relevant values onto your sheet. Here's a trivial working example to get you started:

Dim d As DOMDocument
Dim n As IXMLDOMNode
Dim s As String

Set d = New DOMDocument
d.async = False 
d.Load "http://www.democracynow.org/podcast-video.xml" ' or "C:\myfile.xml"

Set n = d.SelectSingleNode("//channel/description")
s = n.Text
Range("A1") = s

此早期绑定需要如下参考集:工具>参考...> Microsoft XML

This early binding requires a reference set as follows: Tools > References... > Microsoft XML

请注意， d.async = False 会强制加载完成，然后再继续操作；这在加载远程文件时尤其重要.

Note that d.async = False forces the load to complete before proceeding further; this is especially important when loading remote files.

您要

每次在很少或没有用户交互的情况下打开电子表格时都进行更新(即，我宁愿不必告诉人们运行宏)."

update the spreadsheet every time it's opened with little or no user interaction (ie, i'd prefer to not have to tell people to run a macro)."

然后将宏放入 Workbook_Open 事件中！那就是它的目的.

Then put the macro in the Workbook_Open event! That's what it's there for.

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