XML 解析 VBA excel(函数之旅，& MSXML2.DOMDocument)

2022-09-25 02:00:33 other 阅读：510
本文介绍了XML 解析 VBA excel(函数之旅，& MSXML2.DOMDocument)的处理方法，对大家解决问题具有一定的参考价值

问题描述

我需要解析数百个具有相同结构的 XML 文件，如下所示:

I need to parse hundreds of XML files having all the same structure as follows:

<?xml version="1.0" encoding="UTF-8"?>
  <Concepts>
    <ConceptModel name="food">
      <Filters>
        <Filter type="CC"/>
      </Filters>
      <Queries>
        <Query lang="EN">(cheese, bread, wine)</Query>
        <Query lang="DE">(Käse, Brot, Wein)</Query>
        <Query lang="FR">(fromaige, pain, vin)</Query>
      </Queries>
    </ConceptModel>
  </Concepts>

我在互联网上阅读了几篇文章和帖子，如下所示，但我找不到解决方案:

I have read several articles and posts in internet like below but I could not come up with a solution:

目前我正在做的:

Dim oXml As MSXML2.DOMDocument
Set oXml = New MSXML2.DOMDocument
oXml.LoadXML ("C:folderfolder
ame.xml")

Dim Queries As IXMLDOMNodeList
Dim Query As IXMLDOMNode

ThisWorkbook.Sheets(3).Cells(i, 1) = "before loop"

Set Queries = oXml.SelectNodes("/concepts/Queries")

MsgBox "how many Queries " &  Queries.Length

For Each Query In Queries
    ThisWorkbook.Sheets(3).Cells(i, 1) = "Works"
    ThisWorkbook.Sheets(3).Cells(i, 2) = Query.SelectNodes("Query").iTem(0).Text
    i = i + 1
Next

这段代码似乎被 VBA 理解了，但它并没有读取内容.循环没有被读取，这意味着(我猜)查询根本没有循环.Msgbox "多少查询" 给出 0 作为结果证实了这一点.但实际上有三个查询.有人可以帮我吗?

This code seems to be understood by VBA but it does not read the contents. The loop does not get read, meaning (I guess) that Queries is not looped at all. Which is confirmed by the fact that the Msgbox "how many queries" gives 0 as result. But actually there are three queries. Could someone give me a hand?

第二个问题我想问一下

 Dim oXml As MSXML2.DOMDocument

和

 Dim oXml As MSXML2.DOMDocument60

自从我检查了工具/参考Microsof XML, v6.0"

Since I checked in tools/references "Microsof XML, v6.0"

我认为查询有一个标签可能会导致问题.我添加了以下几行:

I thought that the queries having a tag might cause a problem. and I added the follwoing lines:

Dim childs As IXMLDOMNodeList
Set childs = oXml.SelectNodes("/concepts")

MsgBox "childs " & childs.Length

结果也为 0.我期望 3，因为概念有三个孩子，即 ConceptModel、Filter 和 Queries.所以，我更疑惑了.

which also gives 0 as result. I would expect 3, since concepts has three children, namely ConceptModel, Filter and Queries. So, I am even more puzzled.

推荐答案

尽可能接近你的 OP

我想提请您注意几个错误或误解:

I 'd draw your attention to several errors or misunderstandings:

  • [1] 无效的 .LoadXML 语法

.LoadXML ("C:folderfolder ame.xml") 和 .Load ("C:folderfolder ame.xml") 之间有什么区别?

What is then the difference between .LoadXML ("C:folderfolder ame.xml") and .Load ("C:folderfolder ame.xml") ?

Load 需要一个文件路径，然后将文件内容加载到 oXML 对象中.

Load expects a file path and then loads the file content into the oXML object.

LoadXML 不需要文件参数，但它的实际XML 文本内容 必须是格式正确的字符串.

LoadXML doesn't expect a file parameter, but its actual XML text content that has to be a well formed string.

  • [2] XML 区分小写和大写，因此节点需要通过它们的确切字面名称来寻址: 节点不会被 "query" 标识，"ConceptModel" 与 " 不同概念模型".
  • [2] XML distinguishes between lower and upper case, therefore nodes need to be addressed by their exact literal names: the <Query> node wouldn't be identified by "query", "ConceptModel" isn't the same as "conceptmodel".

作为第二个问题，我想问一下Dim oXml As MSXML2.DOMDocument 将与相同Dim oXml As MSXML2.DOMDocument60,自从我签入工具/参考Microsof XML, v6.0"以来?

不，它不是.- 请注意，之前的声明默认会加载 3.0 版.但是，最好获得 6.0 版本(现在任何其他版本都已过时！)

No, it isn't. - Please note that the former declaration would load version 3.0 by default. However it's absolutely preferrable to get the version 6.0 (any other versions are obsolete nowadays!)

当您使用所谓的早期绑定(参考Microsoft XML，v6.0")时，我也会这样做，但指的是当前版本 6.0:

As you are using so called early binding (referencing "Microsoft XML, v6.0"), I'll do the same but am referring to the current version 6.0:

Dim oXml As MSXML2.DOMDocument60        ' declare the xml doc object
Set oXml = New MSXML2.DOMDocument60     ' set an instance of it to memory

  • [3] 误解一些 XPath 表达式

    • XPath 表达式 中的起始斜杠/"始终指的是 DocumentElement(此处为)，您可以将 .DocumentElement 添加到您的文档对象中.如果存在，起始双斜杠//xyz"会找到任何xyz"节点.

    A starting slash "/" in the XPath expression always refers to the DocumentElement (<Concepts> here), you can add .DocumentElement to your document object instead. A starting double slash "//xyz" would find any "xyz" node if existant.

    例如

        oXml.SelectNodes("//Query").Length

    返回与 

        oXml.DocumentElement.SelectNodes("//Query").Length   ' or 
    oXml.SelectSingleNode("//Queries").ChildNodes.Length ' or even       
    oXml.SelectNodes("/*/*/*/Query").Length`.

    参考 XML 6.0 版的代码示例

    当然，您必须遍历多个 xml 文件，该示例仅使用一个(从第 2 行开始).

    Of course you'd have to loop over several xml files, the example only uses one (starting in row 2).

    仅针对格式不正确的 xml 文件的情况，我添加了详细的错误例程，使您能够识别假定的错误位置.Load 和 LoadXML 都返回一个布尔值(如果加载正确，则返回 True，否则返回 False).

    Just for the case of not well formed xml files I added a detailled error Routine that enables you to identify the presumed error location. Load and LoadXML both return a boolean value (True if loaded correctly, False if not).

    Sub xmlTest()

Dim ws   As Worksheet: Set ws = ThisWorkbook.Sheets(3)
Dim oXml As MSXML2.DOMDocument60
Set oXml = New MSXML2.DOMDocument60
With oXml
    .validateOnParse = True
    .setProperty "SelectionLanguage", "XPath"   ' necessary in version 3.0, possibly redundant here
    .async = False

    If Not .Load(ThisWorkbook.Path & "xml" & "name.xml") Then
        Dim xPE        As Object    ' Set xPE = CreateObject("MSXML2.IXMLDOMParseError")
        Dim strErrText As String
        Set xPE = .parseError
        With xPE
           strErrText = "Load error " & .ErrorCode & " xml file " & vbCrLf & _
           Replace(.URL, "file:///", "") & vbCrLf & vbCrLf & _
          xPE.reason & _
          "Source Text: " & .srcText & vbCrLf & vbCrLf & _
          "Line No.:    " & .Line & vbCrLf & _
          "Line Pos.: " & .linepos & vbCrLf & _
          "File Pos.:  " & .filepos & vbCrLf & vbCrLf
        End With
        MsgBox strErrText, vbExclamation
        Set xPE = Nothing
        Exit Sub
    End If

    ' Debug.Print "|" & oXml.XML & "|"

    Dim Queries  As IXMLDOMNodeList, Query As IXMLDOMNode
    Dim Searched As String
    Dim i&, ii&
    i = 2       ' start row
  ' start XPath  
    Searched = "ConceptModel/Queries/Query"                     ' search string
    Set Queries = oXml.DocumentElement.SelectNodes(Searched)    ' XPath
  ' 
    ws.Cells(i, 1) = IIf(Queries.Length = 0, "No items", Queries.Length & " items")
    ii = 1
    For Each Query In Queries
        ii = ii + 1
        ws.Cells(i, ii) = Query.Text
    Next

End With

End Sub

    其他提示

    您可能还对如何通过 XMLDOM 列出所有子节点和使用 VBA 从 XML 获取属性名称.

    You also might be interested in an example how to list all child nodes via XMLDOM and to obtain attribute names from XML using VBA.

    由于后来的评论，我包含了进一步的提示(感谢@barrowc)

    I include a further hint due to later comment (thanks to @barrowc )

    使用 MSXML v3.0 的另一个问题是默认选择语言是 XSLPatterns 而不是 XPath.MSXML 版本之间的一些差异的详细信息是 这里两种选择语言之间的差异在此处讨论."

    "A further issue with using MSXML, v3.0 is that the default selection language is XSLPatterns instead of XPath. Details on some of the differences between MSXML versions are here and the differences between the two selection languages are discussed here."

    在当前的 MSXML2 6.0 版中，完全支持 XPath 1.0.所以看起来XSL Patterns早已经被微软实现了，基本上可以看成是XPath W3C标准化之前的XPath表达式的一个简化子集.

    In the current MSXML2 version 6.0 XPath 1.0 is fully supported. So it seems XSL Patterns have been implemented by Microsoft in earlier days, basically it can be regarded as a simplified subset of XPath expressions before W3C standardisation of XPath.

    MSXML2 3.0 版允许至少通过显式选择语言设置来集成 XPath 1.0:

    MSXML2 Version 3.0 allows the integration of XPath 1.0 at least by explicit selection language setting:

    oXML.setProperty "SelectionLanguage", "XPath"   ' oXML being the DOMDocument object as used in original post

    这篇关于XML 解析 VBA excel(函数之旅，&amp; MSXML2.DOMDocument)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，WP2

admin_action_{$_REQUEST[‘action’]}

do_action( "admin_action_{$_REQUEST[‘action’]}" )动作钩子：：在发送“Action”请求变量时激发。Action Hook: Fires when an ‘action’ request variable is sent.目录锚点：#说明#源码说明（Description）钩子名称的动态部分$_REQUEST['action']引用从GET或POST请求派生的操作。源码（Source）更新版本源码位置使用被使用2.6.0 wp-admin/admin.php:...

日期：2020-09-02 17:44:16 浏览：1170

admin_footer-{$GLOBALS[‘hook_suffix’]}

do_action( "admin_footer-{$GLOBALS[‘hook_suffix’]}", string $hook_suffix )操作挂钩：在默认页脚脚本之后打印脚本或数据。Action Hook: Print scripts or data after the default footer scripts.目录锚点：#说明#参数#源码说明（Description）钩子名的动态部分，$GLOBALS['hook_suffix']引用当前页的全局钩子后缀。参数（Parameters）参数类...

日期：2020-09-02 17:44:20 浏览：1071

customize_save_{$this->id_data[‘base’]}

do_action( "customize_save_{$this-&gt;id_data[‘base’]}", WP_Customize_Setting $this )动作钩子：：在调用WP_Customize_Setting：：save（）方法时激发。Action Hook: Fires when the WP_Customize_Setting::save() method is called.目录锚点：#说明#参数#源码说明（Description）钩子名称的动态部分，$this->id_data...

日期：2020-08-15 15:47:24 浏览：807

customize_value_{$this->id_data[‘base’]}

apply_filters( "customize_value_{$this-&gt;id_data[‘base’]}", mixed $default )过滤器：：过滤未作为主题模式或选项处理的自定义设置值。Filter Hook: Filter a Customize setting value not handled as a theme_mod or option.目录锚点：#说明#参数#源码说明（Description）钩子名称的动态部分，$this->id_date['base']，指的是设置...

日期：2020-08-15 15:47:24 浏览：900

get_comment_author_url

过滤钩子：过滤评论作者的URL。Filter Hook: Filters the comment author’s URL.目录锚点：#源码源码（Source）更新版本源码位置使用被使用 wp-includes/comment-template.php:32610...

日期：2020-08-10 23:06:14 浏览：930

network_admin_edit_{$_GET[‘action’]}

do_action( "network_admin_edit_{$_GET[‘action’]}" )操作挂钩：启动请求的处理程序操作。Action Hook: Fires the requested handler action.目录锚点：#说明#源码说明（Description）钩子名称的动态部分$u GET['action']引用请求的操作的名称。源码（Source）更新版本源码位置使用被使用3.1.0 wp-admin/network/edit.php:3600...

日期：2020-08-02 09:56:09 浏览：877

network_sites_updated_message_{$_GET[‘updated’]}

apply_filters( "network_sites_updated_message_{$_GET[‘updated’]}", string $msg )筛选器挂钩：在网络管理中筛选特定的非默认站点更新消息。Filter Hook: Filters a specific, non-default site-updated message in the Network admin.目录锚点：#说明#参数#源码说明（Description）钩子名称的动态部分$_GET['updated']引用了非默认的...

日期：2020-08-02 09:56:03 浏览：864

pre_wp_is_site_initialized

过滤器：：过滤在访问数据库之前是否初始化站点的检查。Filter Hook: Filters the check for whether a site is initialized before the database is accessed.目录锚点：#源码源码（Source）更新版本源码位置使用被使用 wp-includes/ms-site.php:93910...

日期：2020-07-29 10:15:38 浏览：834

WordPress 的SEO 教学：如何在网站中加入关键字（Meta Keywords）与Meta 描述（Meta Description）？

你想在WordPress 中添加关键字和meta 描述吗？关键字和meta 描述使你能够提高网站的SEO。在本文中，我们将向你展示如何在WordPress 中正确添加关键字和meta 描述。为什么要在WordPress 中添加关键字和Meta 描述？关键字和说明让搜寻引擎更了解您的帖子和页面的内容。关键词是人们寻找您发布的内容时，可能会搜索的重要词语或片语。而Meta Description则是对你的页面和文章的简要描述。如果你想要了解更多关于中继标签的资讯，可以参考Google的说明。Meta 关键字和描...

日期：2020-10-03 21:18:25 浏览：1727

谷歌的SEO是什么

SEO (Search Engine Optimization)中文是搜寻引擎最佳化，意思近于「关键字自然排序」、「网站排名优化」。简言之，SEO是以搜索引擎（如Google、Bing）为曝光媒体的行销手法。例如搜寻「wordpress教学」，会看到本站的「WordPress教学：12个课程…」排行Google第一：关键字：wordpress教学、wordpress课程…若搜寻「网站架设」，则会看到另一个网页排名第1：关键字：网站架设、架站…以上两个网页，每月从搜寻引擎导入自然流量，达2万4千：每月「有机搜...

日期：2020-10-30 17:23:57 浏览：1308