xml 功能类似于Excel中的importxml？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14165276/
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
Function Similar to importxml in Excel?
提问by wKavey
I love using Google Docs function =importxml() but would love to know if there was anything like it in Excel 2010? I cant seem to find a way for the program to automatically pull data from a linked XML file.
我喜欢使用 Google Docs 函数 =importxml() 但想知道 Excel 2010 中是否有类似的功能？我似乎找不到程序自动从链接的 XML 文件中提取数据的方法。
For example, I would love to be able to set up a column with the header "Item Name", then have the next column append the user-entered item name in the previous column to this url
例如，我希望能够设置一个标题为“项目名称”的列，然后让下一列将上一列中用户输入的项目名称附加到此 url
http://util.eveuniversity.org/xml/itemLookup.php?name=
and then parse the resulting XML file to return the type ID. This is accomplished in google docs using
然后解析生成的 XML 文件以返回类型 ID。这是在谷歌文档中使用
=importxml(concatenate("http://util.eveuniversity.org/xml/itemLookup.php?name=",A3);"//itemLookup/typeID")
A3 is the column that has the item name, which in this case would be Tritanium, and imports the data form the resulting XML file
A3 是具有项目名称的列，在本例中为 Tritanium，并从生成的 XML 文件导入数据
http://util.eveuniversity.org/xml/itemLookup.php?name=Tritanium
which returns the value 34.
返回值 34。
I have a list of about 20 item names that google docs automatically updates the item ID on every time I open the file. Is there any way for Excel 2010 to replicate this function?
我有一个大约 20 个项目名称的列表，每次打开文件时，Google 文档都会自动更新项目 ID。Excel 2010 有没有办法复制这个功能？
Thanks!
谢谢！
Will
将要
采纳答案by chris neilsen
You will need to write your own UDF.
您将需要编写自己的 UDF。
One way would be to use the MSXML2library, something like this:
一种方法是使用MSXML2库，如下所示：
Function GetData(sName As String, sItem As String, Optional sURL = "") As Variant
Dim oHttp As New MSXML2.XMLHTTP60
Dim xmlResp As MSXML2.DOMDocument60
Dim result As Variant
On Error GoTo EH
If sURL = "" Then
sURL = "http://util.eveuniversity.org/xml/itemLookup.php?name="
End If
'open the request and send it'
oHttp.Open "GET", sURL & sName, False
oHttp.Send
'get the response as xml'
Set xmlResp = oHttp.responseXML
' get Item'
GetData = xmlResp.getElementsByTagName(sItem).Item(0).Text
' Examine output of these in the Immediate window'
Debug.Print sName
Debug.Print xmlResp.XML
CleanUp:
On Error Resume Next
Set xmlResp = Nothing
Set oHttp = Nothing
Exit Function
EH:
GetData = CVErr(xlErrValue)
GoTo CleanUp
End Function
Call it like this (where A5contains the required typeName)
像这样调用它（其中A5包含所需的typeName）
=GetData(A5, "typeID")
回答by vav
Question is from 2013, some time has passed...
问题是从 2013 年开始的，已经过去了一段时间......
With Excel 2013, there is a function WEBSERVICEto load XML documents, that would do exactly what you want.
在 Excel 2013 中，有一个函数WEBSERVICE可以加载 XML 文档，这正是您想要的。
There is also FILTERXML to search loaded XML document using XPath.
还有 FILTERXML 可以使用 XPath 搜索加载的 XML 文档。
回答by Neil McGuigan
Function ImportXML(url As String, query As String)
Dim document As MSXML2.DOMDocument60
Dim http As New MSXML2.XMLHTTP60
http.Open "GET", url, False
http.send
Set document = http.responseXML
ImportXML = document.SelectSingleNode(query).nodeTypedValue
End Function
回答by sendbits
The 'From Web' function on the Data menu will pull online data directly into spreadsheet. XML data import is also available under the From Other Sources sub-menu, also listed on the data menu.
“数据”菜单上的“来自 Web”功能会将在线数据直接提取到电子表格中。XML 数据导入也可以在 From Other Sources 子菜单下使用，也列在数据菜单上。
Created connections are managed via the Connections dialogue box on the Data menu.
创建的连接通过数据菜单上的连接对话框进行管理。
Sample code using record macros while creating a 'From Web' connection:
创建“从 Web”连接时使用记录宏的示例代码：
Sub Macro1()
' Macro1 Macro
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://en.wikipedia.org/wiki/Microsoft_Excel" _
, Destination:=Range("$A"))
.Name = _
"?affID=110195&tt=270912_7a_3912_6&babsrc=HP_ss&mntrId=3e2fc48700000000000088532eb428ec"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub
