在不知道架构 XPaths 的情况下自动将 Excel XmlMap 映射到 VBA 中的工作表 [英] Automatically map an Excel XmlMap to a worksheet in VBA without knowing the schema XPaths
问题描述
我正在构建一个从 API 下载的 Excel 文件.
它可以从 URL 模式元数据自动生成 XmlMap.但是，我需要将 XmlMap 元素映射到 ListObjects 以提取数据并放在工作表上.
执行此操作的代码是每个项目的 range.Xpath.SetValue 映射 xPath(来自
现在，如果您的 XML 具有复杂的嵌套子元素，请考虑构建和运行
I am building an Excel file that downloads from an API.
It can automatically generate the XmlMap from the URL schema metadata. However I then need to map the XmlMap elements to ListObjects in order to pull the data and put on a worksheet.
The code to do this is range.Xpath.SetValue map xPath for each item (from MSDN):
Sub CreateXMLList()
Dim mapContact As XmlMap
Dim strXPath As String
Dim lstContacts As ListObject
Dim objNewCol As ListColumn
' Specify the schema map to use.
Set mapContact = ActiveWorkbook.XmlMaps("Contacts")
' Create a new list.
Set lstContacts = ActiveSheet.ListObjects.Add
' Specify the first element to map.
strXPath = "/Root/Person/FirstName"
' Map the element.
lstContacts.ListColumns(1).XPath.SetValue mapContact, strXPath
' Specify the second element to map.
strXPath = "/Root/Person/LastName"
' Add a column to the list.
Set objNewCol = lstContacts.ListColumns.Add
' Map the element.
objNewCol.XPath.SetValue mapContact, strXPath
strXPath = "/Root/Person/Address/Zip"
Set objNewCol = lstContacts.ListColumns.Add
objNewCol.XPath.SetValue mapContact, strXPath
End Sub
Here's the schema output:
<?xml version="1.0" encoding="utf-8"?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" >
<xsd:element name="root" nillable="true" >
<xsd:complexType>
<xsd:sequence minOccurs="0">
<xsd:element minOccurs="0" maxOccurs="unbounded" nillable="true" name="list-item" form="unqualified">
<xsd:complexType>
<xsd:sequence minOccurs="0">
<xsd:element name="data_source_organization"
minOccurs="0"
nillable="true"
type="xsd:string"
form="unqualified"
/>
<xsd:element name="survey_name"
minOccurs="0"
nillable="true"
type="xsd:string"
form="unqualified"
/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
Here's the data (from which Excel automatically gets the schema and creates the XmlMap, if using the GUI):
<root xsi:noNamespaceSchemaLocation="/api/domain/schema/?format=xml">
<list-item>
<data_source_organization>An org</data_source_organization>
<survey_name>A Survey</survey_name>
</list-item>
<list-item>
<data_source_organization>An org</data_source_organization>
<survey_name>Another Survey</survey_name>
</list-item>
</root>
However I don't want to specify the XPath strings - I want Excel to get everything from the schema metadata, just like it does if you use the GUI functionality (Data, Get External Data, From Other Sources, XML, paste a URL) - this automatically creates an XML map, creates a ListObject on the worksheet, maps every column in the source data, and grabs and displays the data. (If you record a macro doing this, it skips the mapping step.)
- Can I point an XmlMap to a cell, range or ListObject?
- Can I iterate the XmlMap and retrieve every list-item XPath?
- Some other way?
To experiment/reproduce, save the above XML as files, then create a sub as follows:
Set currentMap = ActiveWorkbook.XmlMaps.Add("C:\path\to\schema.xml", "root")
currentMap.DataBinding.LoadSettings "path\to\data.xml"
' Do something to map the XmlMap elements to cells in the spreadsheet
' eg, objNewCol.XPath.SetValue currentMap, "root/data_source_organization"
' But some method that does not involve naming the Xml paths but iterates the schema
currentMap.DataBinding.Refresh
If the XmlMap is mapped to cells, those cells will populate with data.
Consider using Workbooks.OpenXML method as your XML file is flat and simple with one-child level for easy tabular import:
Sub ImportXML()
Workbooks.OpenXML "C:\Path\To\File.xml", , xlXmlLoadImportToList
End Sub
Now, if your XML is complex with nested child elements, consider building and running XSLT, the special-purpose language designed to transform XML files. Such transformations can be automated with the MSXML library, available as a VBA reference. Note: XSLT is not an XSD schema file but part of the extensible stylesheet family of which includes XPath.
Below XSLT removes the namespace from original XML. But script can be used to flatten nested, complex structures to flat, simple ones like your posted example.
XSLT (save as .xsl file; removes any namespace and attributes from document)
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="xml" indent="yes"/>
<xsl:strip-space elements="*"/>
<xsl:template match="*">
<xsl:element name="{name()}">
<xsl:apply-templates select="node()" />
</xsl:element>
</xsl:template>
</xsl:stylesheet>
VBA
Sub XSLTransformAndImport()
On Error GoTo ErrHandle
' SELECT Microsoft XML, v6 AS VBA REFERENCE
Dim xmldoc As New MSXML2.DOMDocument60, xslDoc As New MSXML2.DOMDocument60, newDoc As New MSXML2.DOMDocument60
' LOAD XML AND XSL FILES
xslDoc.async = False
xmldoc.Load "C:\Path\To\Input.xml"
xslDoc.async = False
xslDoc.Load "C:\Path\To\XSLTScript.xsl"
' TRANSFORM XML
xmldoc.transformNodeToObject xslDoc, newDoc
newDoc.Save "C:\Path\To\Output.xml"
' IMPORT INTO WORKBOOK AS TABLE
Workbooks.OpenXML "C:\Path\To\Output.xml", , xlXmlLoadImportToList
ExitHandle
' RELEASE RESOURCES
Set xmldoc = Nothing: Set xslDoc = Nothing: Set newDoc = Nothing
Exit Sub
ErrHandle:
MsgBox Err.Number & " - " & Err.Description, vbCritical
Err.Raise xslDoc.parseError.ErrorCode, , xslDoc.parseError.reason
Resume ExitHandle
End Sub
这篇关于在不知道架构 XPaths 的情况下自动将 Excel XmlMap 映射到 VBA 中的工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
