XLL Containers

Introduction
Common Requirements
Best Practices
Examples

Introduction

Implementation of Excel add-in functional interface often requires dealing with XLOPER structure created by Excel and a pointer to which passed to a user defined function. In addition, sometimes a user defined function should return a pointer to XLOPER back to Excel as a return value. XLOPER structure is actually a variant type, which can contain data of several different types, but the vast majority of all applicable treatments of XLOPER structure falls in the following three categories:

• a single cell,
• a one-dimensional range, or a vector of elements
• a two-dimensional range, or a matrix of elements

To extract useful information from XLOPER structure application programmers usually implement various converters to transform XLOPER structure to and from base types:

int, double

std::string, std::vector

The solution proposed here takes a different approach. It is based on classes-adaptors that wraps around XLOPER and allow to treat it as a single value of a specific base type, or as a container complying with requirements of STL.

Thus, each class in the proposed solution can be viewed as a dual entity: for Excel such a class is seen as an XLOPER structure, but for application functions it can be treated as a C++ basis or container type. Such a dualism contributes a great deal to efficient memory management, code brevity and exception safety.

Check out the examples of use of XLL containers below.

The XLL containers template library provides six container class templates:

In addition, for each of xll::Cell, xll::Matrix, xll::Vector container classes the library provides implementation of five different pointer class concepts, which implement concepts of boost::scoped_pointer, boost::shared_pointer, and boost::intrusive_pointer:

When implementing an Excel add-in functional interface the following five essentially different situation usually should be addressed:

1. Excel passes a pointer to XLOPER to a user defined function that should be treated as an input, read-only data. A const-pointer type is suitable in this case.
2. A user defined function must construct an XLOPER structure and pass it as a result to Excel. In this case a weak-pointer is a perfect choice.
3. A local XLOPER object should be created to pass it to Excel4v function as a parameter-result (e.g. xlfGetName). A scoped-pointer is a convenient choice in this case.
4. An Excel object should be constructed and stored in some external container (e.g. ObjectHandler) for future use. A shared-pointer is the only solution in this case.
5. A temporal XLOPER should be created to pass it as an input parameter to Excel4v function. In this case a temp-pointer can be used.

The table below summarizes the references to all XLL Containers library classes and binds them to the use cases listed above (see also examples below):

Common Requirements

All XLL Containers library class templates have a template parameter, T, which specifies the type of the object stored in a container. The following types can be specified:

• integer types: int, long, short, bool;
• floating point types: float, double;
• std::string;
• boost::any (which makes it an abstract container);
• an error type cell: xll::ErrorCode;
• a special case int type: xll::Int (to handle xltypeInt XLL type).

Best Practices

Although it is possible to create an object of type CellAlloc, MatrixAlloc, or VectorAlloc, it is recommended to use pointer classes instead.
The reason for this is that unlike object-containers, pointer classes explicitly specify the intention and reason for creating an object: const-pointer means read only data, weak-pointer indicates that the object created will be passed to Excel, scoped-pointer indicates local "in-scope" use of object, and shared-pointer means creation of a global, reusable object.
Construction of object-containers is not safe in this respect since it is easy to make a mistake choosing an inappropriate allocation method.

It is also not advisable using static objects since use of pointer classes discussed here makes it senseless.

An access to the features of underlying class-containers can be done via usual pointer dereferencing technique as demonstrated in examples below.

Examples

The following example demonstrates the use of a const-pointer.

#include <XLLArrays.hpp>

// In the function below it is assumed that parameter x represents a vector of doubles
double Average (const XLOPER *x)
{
    try {
        xll::VectorPtrConst <double> px (x);
        double res = 0;
        for (size_t i = 0; i < px->size(); ++i)
            res += px->at(i); // or res += (*px)[i];
        return res / px->size();
    }
    catch (const std::bad_cast &)
    {
        cerr << "Parameter x either not an array or its elements cannot be converted to double" << endl;
    }
    return 0;
}

Notes.

1. VectorPtrConst class checks the type of input parameter x. If it cannot be converted to array of doubles the constructor throws a bad_cast exception.
2. The code above works fine even if a user passes a single numeric cell to this function. VectorPtrConst will treat it as an array of size 1 automatically in this case.

The following example demonstrates the use of a weak-pointer.

#include <XLLArrays.hpp>
#include <algorithm>

// This function generates an array of random numbers and returns it to Excel
XLOPER * GenRandomVector (int how_many, int in_row)
{
    xll::VectorPtrWeakExcel <int> px (how_many, in_row != 0);
    std::generate (px->begin(), px->end(), rand);
    return px;
}

Note. Memory allocated in the function above will be released by Excel. If instead of VectorPtrWeakExcel one used VectorPtrWeak class, the xlAutoFree function would have to be provided to release memory.

The next example demonstrates the use of a scoped-pointer and a temp-pointer.

#include <XLLArrays.hpp>

// A nifty implementation of xlAutoOpen interface function
int xlAutoOpen()
{
    using namespace xll;
    try
    {
        CellPtrScopedExcel <std::string> xDll;
        Excel(xlGetName, xDll, 0);

        Excel(xlfRegister, 0, 10, xDll,
            TempStr ("GenRandomVector"), //Function code name.
            TempStr ("PJJ"),		  //Parameter codes.
            TempStr ("GenRandomVector"), //Function display name.
            TempStr (""),
            TempStr ("1"), //Function type.
            TempStr ("My functions"), //Function category.
            TempStr (""), //shortcut text (command macros only).
            TempStr (""), //path to help file.
            TempStr ("Returns a vector of random numbers ")
        );
        return 1;
    }
    catch (const std::exception &ex)
    {
        Excel(xlcAlert, 0, 1, TempStr(ex.what()));
        return 0;
    }
    catch (...)
    {
        Excel(xlcAlert, 0, 1, TempStr("Unknown exception"));
        return 0;
    }
}

Note use of CellPtrScopedExcel class in the function above. We do not have to worry about the call of xlFree function to release memory allocated by Excel in xlGetName since implementation of CellPtrScopedExcel class takes care about it. If, however, we use CellPtrScoped class instead (and it is admittedly very easy to confuse) the code above most likely end up in a crash when CellPtrScoped destructor will try to release memory allocated by Excel in xlGetName call.

The following example demonstrates the use of a shared-pointer.

#include <XLLArrays.hpp>
#include <boost/shared_ptr.hpp>
#include <algorithm>

// Calculates the sum of two arrays and returns the result to the outside world.
boost::shared_ptr<XLOPER> SumArrays(const XLOPER *x, const XLOPER *y)
{
    using namespace xll;
    VectorPtrConst <double> px(x);
    VectorPtrConst <double> py(y);
    VectorPtrShared <double> pz(px->size());

    std::transform (px->begin(), px->end(), py->begin(), pz->begin(), std::plus);

    return boost::static_pointer_cast <XLOPER> (pz);
}

Note that a shared pointer returned by the function above can be stored in ObjectHandler. Memory allocated by VectorPtrShared class is released in its destructor, which is called when reference counter for the instances of underlying object becomes zero.

History and Acknowledgements

April 2007. Original version.

References

[XLOPER] MSDN, The XLOPER Data Type

Copyright 2007 ---