Receive a matrix from excel in c++ xll, modifying

问题:

I coded up an xll function receiving a matrix from excel, modifying it a returning it :

__declspec(dllexport) LPXLOPER12 WINAPI ZZZZUpdateArray1(LPXLOPER12 arrayin)
{
    if (arrayin->xltype == xltypeMulti | xlbitDLLFree)
    {
        double ScaleFactor = 2.0;
        int rows = arrayin->val.array.rows;
        int cols = arrayin->val.array.columns;
        static XLOPER12 xlArray;
        xlArray.val.array.lparray = reinterpret_cast<LPXLOPER12>(::new XLOPER12[rows * cols] /*::malloc(rows * cols * sizeof(XLOPER12))*/);
        for (int r = 0; r<rows; r++)
        {
            for (int c = 0; c<cols; c++)
            {
                if ((arrayin->val.array.lparray + ((r* cols) + c))->xltype == xltypeNum)
                {
                    XLOPER12* var = xlArray.val.array.lparray + ((r* cols) + c);
                    var->xltype = xltypeNum;
                    var->val.num = ScaleFactor*(arrayin->val.array.lparray + ((r* cols) + c))->val.num ;
                }
            }
        }
        return static_cast<LPXLOPER12>(&xlArray);
    }
    return arrayin;
}

but it crashes on

if ((arrayin->val.array.lparray + ((r* cols) + c))->xltype == xltypeNum)

If I am for instance taking a 5*5 matrix from excel, at debug I see it having 19 rows and 20 colums, what happened !?

Is it maybe because when I receive the LPXLOPER12 arrayin, it represents an excel range, way more complicated than a 5*5 matrix. How to access the 5*5 matrix it contains, then ?

回答1:

• Use the Excel code Q to register the input argument.

• You need to declare properly the return variable :

  // initialize the return variable
  xlArray.val.array.rows = rows;
  xlArray.val.array.columns = cols;
  xlArray.xltype = xltypeMulti | xlbitDLLFree;
  

回答2:

I took the ujsgeyrr1f0d0d0r0h1h0j0j_juj code´s to make one to test my application. I think it can be useful. I´ve used codes from another references also.

    __declspec(dllexport) LPXLOPER12 WINAPI Array_In_Out(LPXLOPER12 ExMatriz)

{

     static XLOPER12 xlMatriz;  // variável de retorno


try
{

    int rows = ExMatriz->val.array.rows;
    int cols = ExMatriz->val.array.columns;
    xlMatriz.xltype = xltypeMulti | xlbitDLLFree;
    xlMatriz.val.array.rows = rows;
    xlMatriz.val.array.columns = cols;


    // ======= VERIFICACAO DOS DADOS DE ENTRADA ==============


    if (ExMatriz->xltype != xltypeRef       &&
        ExMatriz->xltype != xltypeSRef  &&
        ExMatriz->xltype != xltypeMulti)
    {
        xlMatriz.xltype = xltypeErr;
        xlMatriz.val.err = xlerrValue;

        return (LPXLOPER12)&xlMatriz;
    }



    static XLOPER12 MatrizMulti, tempTypeMulti;
    LPXLOPER12 Matrizptr;

    tempTypeMulti.xltype = xltypeInt;
    tempTypeMulti.val.w = xltypeMulti;

    if (xlretUncalced ==
        Excel12(xlCoerce, (LPXLOPER12)&MatrizMulti, 2, (LPXLOPER12)ExMatriz,
        (LPXLOPER12)&tempTypeMulti))
    {
        return 0;
    }


    Matrizptr = MatrizMulti.val.array.lparray;

    // verify that the entire ExMatriz are nums

    for (int r = 0; r < rows; r++)
    {
        for (int c = 0; c < cols; c++)
        {


            if ((Matrizptr + ((r* cols) + c))->xltype != xltypeNum)  // verifica se os valores são numericos
            {


                if (Matrizptr->xltype == xltypeErr)
                {

                    xlMatriz.xltype = xltypeErr;
                    xlMatriz.val.err = Matrizptr->val.err;

                    return (LPXLOPER12)&xlMatriz;
                }
                else
                {

                    xlMatriz.xltype = xltypeErr;
                    xlMatriz.val.err = xlerrValue;

                    return (LPXLOPER12)&xlMatriz;
                }

            }


        }
    }



    xlMatriz.val.array.lparray = reinterpret_cast<LPXLOPER12>(::new XLOPER12[rows * cols]); //new XLOPER12[rows*cols];


    for (int r = 0; r < rows; r++)
    {

        for (int c = 0; c < cols; c++)
        {

            xlMatriz.val.array.lparray + ((r* cols) + c);

            ExMatriz->val.array.lparray + ((r* cols) + c);

            xlMatriz.val.num = ExMatriz->val.num;         // coloca os valores de entrada na variável de saída !!!

        }
    }



    return static_cast<LPXLOPER12>(&xlMatriz);


    //return (LPXLOPER12)&xlMatriz;

}
catch (std::exception& e)
{
    xlMatriz.xltype = xltypeErr;
    xlMatriz.val.err = xlerrNA;
    return  (LPXLOPER12)&xlMatriz;
}

}