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.
// 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:
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.
xloResult = matResult;
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:
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.:
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.