VBA(Excel)中的XPath评估 [英] XPath evaluation in VBA (Excel)

问题描述

我有一个要在Excel 2010中评估的xml文件.xml文件如下所示:

I have an xml file to be evaluated in Excel 2010. The xml file looks as follows:

<Account>
    <Entry>
        <Value>5</Value>
    </Entry>
    <Entry>
        <Value>4</Value>
    </Entry>
    <Entry>
        <Value>-3.6</Value>
    </Entry>
</Account>

我想对每个符合某些指定条件的条目"的所有值求和.我需要的评估如下:

I want to sum over all values of every 'Entry' that fits some specified conditions. The evaluation which I need looks as follows:

sum(/*/Entry[Date[starts-with(., '04') and contains(., '2014')]][Value < 0.0][not(ContraEntryID)]/Value)

我不知道如何在Excel中对此进行评估.到目前为止，我得到的是一个选择，在此之后我会对每个选择进行总结，但是必须有一种更好的方法来直接正确地进行评估?这是我已经写的:

I don't know how to get this evaluated in Excel. What I get so far is a selection where I sum up over every item of it afterwards but there must be a better way to directly get the evaluation right? This is what I have already written:

Private Sub getSumOfValues()

Dim xmlFile As String
xmlFile = "..."

Dim xmlDoc As New MSXML2.DOMDocument60
Dim xmlSelection As IXMLDOMSelection

xmlDoc.async = False
xmlDoc.validateOnParse = True

xmlDoc.Load (xmlFile)

xmlDoc.setProperty "SelectionLanguage", "XPath"

Set xmlSelection = xmlDoc.SelectNodes("/*/Entry[Date[starts-with(., '04') and contains(., '2014')]][Value < 0.0][not(ContraEntryID)]/Value")

Dim i As Integer
Dim sum As Double
sum = 0
Dim val As String

For i = 0 To xmlSelection.Length - 1
    val = xmlSelection.Item(i).Text
    val = Replace(val, ".", ",")
    sum = sum + CDbl(val)
Next i
Debug.print(sum)   
End Sub

推荐答案

据我所知，Msxml仅支持评估返回节点集的XPath表达式，没有API评估返回诸如以下值的原始值的XPath表达式一个号码.使用MSXML，您所能做的就是创建并执行一个样式表，该样式表执行< xsl:value-of select ="sum(...)"/> .

As far as I know, Msxml only supports evaluating XPath expressions that return a node set, there is no API to evaluate an XPath expression that returns a primitive value like a number. With MSXML, all you could is create and execute a stylesheet, that does an <xsl:value-of select="sum(...)"/>.

还有其他类似Microsoft .NET Framework中的XPath实现，它们提供了 Evaluate 方法.我不确定是否可以在Office中使用它.

There are other XPath implementations like in the Microsoft .NET framework, that offer an Evaluate method. Whether you can use that from Office I am not sure.

这篇关于VBA(Excel)中的XPath评估的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！