从C＃web服务返回多维数组数据VBA [英] Returning multi dimensional array data from C# webservice to vba

问题描述

我有一个C＃web服务，返回一个二维数组数据。既然我们不能有web服务返回的多维数据，我做到了返回交错数组。

I have a C# webservice that returns a 2D array data. Since we cannot have webservices return multi dimensional data, i made it to return a jagged array.

[OperationContract]
object[][] WSGetData();

我有一个COM可见C＃类库。这是一薄层消耗该服务，并提供它，因为它是到Excel VBA客户端。 （因为某种原因，我们选择了不通过VSTO去，或Web服务工具包的参考路线。）

I have a COM Visible C# class library. This is a thin layer which consumes this service and provides it as it is to Excel VBA clients. (For certain reasons, we chose not go via VSTO, or Web Services References Toolkit routes.)

class Wrapper
{
    public object[][] GetData()
    {
        return WSproxy.WSGetData(); //Calling the webservice method
    }
}

我叫下面的VBA的方法。

I call the method in VBA as below.

Dim data as Variant
data = wrapperObj.GetData();

我得到一个类型不匹配的错误。

I get a Type Mismatch error.

当我改变了包装类的web服务的锯齿形阵'输出转换为多维输出（即对象[，]）返回到之前的VBA，它工作正常。但我不希望这样做，因为这会影响性能，因为我们将绕过庞大的数据。

When I changed the Wrapper class to convert the webservice's 'jagged array' output to a multi dimensional output (ie. object[,]) before returning to VBA, it works fine. But I do not want to do this, because it would affect performance as we will be passing around huge data.

什么是实现这一目标的最佳途径，请。
感谢您的任何指示。

What is the best way to achieve this, please. Thanks for any directions..

推荐答案

铁血阵列是可能的。我不认为VBA喜欢你声明的变量的方式。你可能需要将其申报为Variant数组。参见下面的例子：

Jagged Arrays are possible. I don't think VBA likes the way you've declared your variant. You probably need to declare it as a Variant array. See the below example:

Sub Test()
    Dim oneToTen(9) As String 'Array 1
    Dim tenTo21(10) As String  'Array 2
    Dim twentyTwoTo23(1) As String  'Array 3
    Dim vArray() As Variant   'Jagged Array (array of arrays)

    'Fill test data in the three arrays
    Dim iCount As Integer
    For iCount = 0 To 9
        oneToTen(iCount) = iCount + 1
    Next iCount
    For iCount = 0 To 10
        tenTo21(iCount) = iCount + 11
    Next iCount
    For iCount = 0 To 1
        twentyTwoTo23(iCount) = iCount + 22
    Next iCount

    'If you uncomment the code below, you will get a type mismatch (probably for the same reason you get it in your webservice)
    'vArray1(0) = oneToTen

    'However, if you REDIM the variant array, you can then set each array into the variant
    Const JAGGED_ARRAY_SIZE = 2 'This will probably require another property on your webservice to see how big your Jagged Array is (e.g.  wrapperObj.GetJaggedArraySize())
    ReDim vArray(JAGGED_ARRAY_SIZE)

    vArray(0) = oneToTen
    vArray(1) = tenTo21
    vArray(2) = twentyTwoTo23

    'Now loop through the jagged array:
    Dim outerLoop As Integer
    Dim innerLoop As Integer
    Dim vCurrentArray As Variant

    'Loop through the arrays in the array and print out the data
    For outerLoop = 0 To JAGGED_ARRAY_SIZE
        For innerLoop = 0 To UBound(vArray(outerLoop))
            Debug.Print "Outer Loop:  " & outerLoop & "  Inner Loop:  " & innerLoop & "  Array Value:  " & vArray(outerLoop)(innerLoop)
        Next innerLoop
    Next outerLoop

End Sub

这篇关于从C＃web服务返回多维数组数据VBA的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！