如何从XLL UDF返回数组 [英] How to return an array from an XLL UDF

问题描述

目标： = array_cons（1） ，2,3）=> {1，2，3}

但是，我没有正确初始化XLOPER12。在Excel中，我的函数目前返回 #NUM 。我采取参数列表，并通过宏将其打包成一个 vargs 数组，然后尝试返回提供的数组的一部分。

  #include< windows.h> 
 #include< xlcall.h> 
 #include< framewrk.h> 
 #include< boost / preprocessor.hpp> 
 
 #define VARG_COUNT 250 
 #define VARG_FORMAT（Z，A，B）B ## A，
 #define VARG_DEF_LIST（N）BOOST_PP_REPEAT（N，VARG_FORMAT，LPXLOPER12 varg） \\ 
 LPXLOPER12 varg ## N 
 #define VARG_ARRAY（N）{BOOST_PP_REPEAT（N，VARG_FORMAT，varg）varg ## N} 
 #define GET_VARGS VARG_ARRAY（VARG_COUNT）
 
 __declspec（dllexport）LPXLOPER12 WINAPI array_cons（VARG_DEF_LIST（VARG_COUNT））
 {
 LPXLOPER12 vargs [] = GET_VARGS; 
 int args_passed = 0; 
 for（int i = 0; i< VARG_COUNT; ++ i，++ args_passed）
 {
 if（vargs [i]  - > xltype == xltypeMissing）
 {
 break; 
} 
} 
 if（args_passed == 0）
 {
 XLOPER12 err; 
 err.xltype = xltypeErr; 
 err.val.err = xlerrValue; 
 return（LPXLOPER12）& err; 
} 
 XLOPER12列表; 
 list.xltype = xltypeMulti; 
 list.val.array.lparray =（XLOPER12 *）vargs; 
 list.val.array.rows = args_passed; 
 list.val.array.columns = 1; 
 return（LPXLOPER12）& list; 
} 
  

解决方案

我想出来了需要注意的几件事情 -

您需要确保您的UDF注册正在使用正确的签名。在我的情况下，我希望Excel引用给我各自的值，所以我在注册该函数时使用了 Q 类型。如果您不明白这一点，请查看 http://msdn.microsoft .com / en-us / library / office / bb687869.aspx

为了返回一个数组，你必须动态分配新的内存到 list.val.array.lparray 成员并迭代填充。

  __ declspec （dllexport）LPXLOPER12 WINAPI array_cons（VARG_DEF_LIST（VARG_MAX））{
 LPXLOPER12 vargs [] = GET_VARGS; 
 int args_passed = 0; 
 for（int i = 0; i< VARG_MAX; ++ i，++ args_passed）{
 if（vargs [i]  - > xltype == xltypeMissing）{
 break; 
} 
} 
 XLOPER12列表; 
 list.xltype = xltypeMulti | xlbitDLLFree; 
 list.val.array.lparray = new XLOPER12 [args_passed]; 
 list.val.array.rows = args_passed; 
 list.val.array.columns = 1; （int i = 0; i< args_passed; ++ i）
 {
 list.val.array.lparray [i] = * vargs [i]; 
} 
 return& list; 
} 
  

由于我们动态分配内存，我们需要定义回调以释放它。

  __ declspec（dllexport）void WINAPI xlAutoFree12（LPXLOPER12 p）{
 if（p-> xltype == （xltypeMulti | xlbitDLLFree））{
 delete [] p-> val.array.lparray; 
} 
} 
  

I'm trying to write an array constructor for Excel as a worksheet function using the C API.

Goal: =array_cons(1, 2, 3) => {1, 2, 3}

However, I am not initializing the XLOPER12 correctly. In Excel, my function currently returns #NUM. I am taking the argument list and packing it into a vargs array via macros then trying to return the part of the array that was supplied.

#include <windows.h>
#include <xlcall.h>
#include <framewrk.h>
#include <boost/preprocessor.hpp>

#define VARG_COUNT 250
#define VARG_FORMAT(Z, A, B) B##A, 
#define VARG_DEF_LIST(N) BOOST_PP_REPEAT(N, VARG_FORMAT, LPXLOPER12 varg) \
                         LPXLOPER12 varg##N
#define VARG_ARRAY(N) { BOOST_PP_REPEAT(N, VARG_FORMAT, varg) varg##N }
#define GET_VARGS VARG_ARRAY(VARG_COUNT)

__declspec(dllexport) LPXLOPER12 WINAPI array_cons(VARG_DEF_LIST(VARG_COUNT))
{
    LPXLOPER12 vargs[] = GET_VARGS;
    int args_passed = 0;
    for(int i = 0; i < VARG_COUNT; ++i, ++args_passed)
    {
        if (vargs[i]->xltype == xltypeMissing)
        {
            break;
        }
    }
    if (args_passed == 0)
    {
        XLOPER12 err;
        err.xltype = xltypeErr;
        err.val.err = xlerrValue;
        return (LPXLOPER12)&err;
    }
    XLOPER12 list;
    list.xltype = xltypeMulti;
    list.val.array.lparray = (XLOPER12*)vargs;
    list.val.array.rows = args_passed;
    list.val.array.columns = 1;
    return (LPXLOPER12)&list;
}

解决方案

I figured it out. A couple of things to note here -

You need to make sure that your UDF registration is using the right signature. In my case, I wanted Excel references to give me their respective values, so I used the Q type when registering the function. If you don't understand this, check out http://msdn.microsoft.com/en-us/library/office/bb687869.aspx

In order to return an array, you have to dynamically allocate new memory to the list.val.array.lparray member and iteratively populate it.

__declspec(dllexport) LPXLOPER12 WINAPI array_cons(VARG_DEF_LIST(VARG_MAX)) {
    LPXLOPER12 vargs[] = GET_VARGS;
    int args_passed = 0;
    for(int i = 0; i < VARG_MAX; ++i, ++args_passed) {
        if (vargs[i]->xltype == xltypeMissing) {
            break;
        }
    }
    XLOPER12 list;
    list.xltype = xltypeMulti | xlbitDLLFree;
    list.val.array.lparray = new XLOPER12[args_passed];
    list.val.array.rows = args_passed;
    list.val.array.columns = 1;
    for(int i = 0; i < args_passed; ++i) {
        list.val.array.lparray[i] = *vargs[i];
    }
    return &list;
}

Since we are dynamically allocating memory, we need to define the callback to free it.

__declspec(dllexport) void WINAPI xlAutoFree12(LPXLOPER12 p) {
    if (p->xltype == (xltypeMulti | xlbitDLLFree)) {
        delete [] p->val.array.lparray;
    }
}

这篇关于如何从XLL UDF返回数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！