XLL+ Class Library (6.3)

Returning matrices to Excel

Returning a matrix to Excel is very similar to returning a vector. Add the following stand-alone matrix function to Tutorial1.cpp.

Note: This is a very silly add-in function. Transformations of this kind are far easier to do in Excel itself, especially if you use a mixture of relative and absolute addresses. But it makes a reasonably good example function.
CopyC++
// Multiplication of two vectors to produce a matrix 
// aadZ should be an array of cX pointers to columns 
void MatrixMultiply(int cX, double* adX, int cY, double* adY, double** aadZ)
{
    int i, j;
    for (i = 0; i < cX; i++) {
        for (j = 0; j < cY; j++) {
            aadZ[i][j] = adX[i] * adY[j];
        }
    }
}

Note that argument aadZ is an array of pointers to columns, and can be addressed as aadZ[column][row].

Use the Function Wizard to create a new function MATRIXMULT:

Name: MATRIXMULT
Return type: CXlOper
Category: Math & Trig
Description: Matrix multiply two vectors

Add two vector arguments, as follows:

Type: Double[]
Name: X
Description: First vector of numbers
Type: Double[]
Name: Y
Description: Second vector of numbers

Modify the generated code so that it looks like this:

CopyC++
CXlOper* MATRIXMULT_Impl(CXlOper& xloResult, const CXlOper* X_op, const CXlOper*
    Y_op)
{
    // Input buffers 
    std::vector<double> X;
    std::vector<double> Y;
    // Validate and translate inputs
    XlReadVector(*X_op, X, L"X", XLA_TRUNC_ONEMPTY|XLA_TRUNC_ONBLANK);
    XlReadVector(*Y_op, Y, L"Y", XLA_TRUNC_ONEMPTY|XLA_TRUNC_ONBLANK);
    // End of generated code 
//}}XLP_SRC 
 
    // Allocate a matrix with initial size: rows = Y.xsize() and cols = X.size()         
    ple::mtx_ptrs<double> matResult(Y.size(), X.size());                                 

    // Pass the input and output arrays to the stand-alone function                      
    MatrixMultiply(Y.size(), &Y[0], X.size(), &X[0], &matResult[0]);                     
    // If you prefer STL notation, use the following                                      
    //MatrixMultiply(Y.size(), Y.begin(), X.size(), X.begin(), matResult.begin());        
 
    // Populate xloResult with the resulting matrix                                      
    xloResult = matResult;                                                               
 
    return xloResult.Ret();
}

Here we've made use of the mtx_ptrs class to manage the output array. The matrix constructor allocates an appropriate number of rows and columns, which are initially filled with zeroes. Then we use either a pointer to the first array in the matrix (&matResult[0]) or the STL-style matResult.begin() function to get an array of column pointers, as required by MatrixMultiply().

Populating xloResult with the result of MatrixMultiply() is very simple: we use another CXlOper overloaded assignment operator to allocate the output data and to copy in the contents of matResult.

CopyC++
xloResult = matResult;

Returning other matrix types

If you need to return a matrix of another type to Excel, just use an adapter class, so that CXlOper::operator= treats it as a ple::imtx container.

For example, to return a boost matrix to Excel, you might use code such as:

CopyC++
boost::multi_array<double, 2> m;
// ... Fill contents of m ...
xloResult = mtx_adapter(m);
return xloResult.Ret();

If you only have const access to the matrix you wish to return, use a read-only adapter, e.g.:

CopyC++
const boost::multi_array<double, 2>& ref_to_matrix = call_some_function();
xloResult = mtx_const_adapter(m);
return xloResult.Ret();

See mtx_adapter and mtx_const_adapter for more about matrix adapter classes.

Next: Arrays of mixed type >>