XLW Version 3 - Support for Excel 2007

Summary

XLW is upgraded to support new features of Excel 2007 such as

• longer strings
• Unicode strings
• larger spreadsheets
• multithreaded worksheet functions
• additional function arguments

Certain new features of Excel 2007 such as larger spreadsheets and long/Unicode strings can be exposed by XLW to its client applications with no change to the XLW interface. When the Addin is loaded, the core XLW library detects the version of Excel under which the XLL is being run, and dynamically supports the new features where possible with no need for changes to legacy source code of XLW client applications. Other new Excel 2007 features such as multithreading necessitate enhancements to the XLW interface and modifications must be made to source code of client applications in order to exploit these new features.

Terminology

Excel version 4 introduced the Excel C API, which remained largely unchanged through Excel version 11 (also known as Excel 2003). Excel version 12 (Excel 2007) introduces fundamental enhancements to the API. Hereafter Excel 12 refers to Excel 2007 and Excel 4 refers collectively to all versions of Excel prior to Excel 2007.

Objectives

The screenshots below summarize the main objectives of the XLW version 3 release.

Excel 4

Excel 12

Explanation

The screenshots above display the output from a single XLW XLL which is compiled from a single source code base and is compatible both with Excel 4 and Excel 12.

The application implements three variations of a "Concat()" function. Each variation of the function is called once with ASCII input (supported by Excel 4 and 12) and once with Unicode input (supported by Excel 12 only).

Function	Description
Concat()	Functions both with Excel 4 and 12 Supports Unicode if run under Excel 12
Concat4()	Functions both with Excel 4 and 12 Retains Excel 4 behavior (no Unicode support) even when run under Excel 12
Concat12()	Supports Unicode Available only in Excel 12

Function Concat() reflects the primary objectives for the XLW upgrade. The function retains old Excel 4 compatible behavior if run under Excel 4, but when called from Excel 12 the same function picks up new Excel 12 functionality, in this case Unicode support.

The flexibility in function Concat() incurs a performance overhead at run time. This overhead is avoided in functions Concat4() and Concat12() which target specific versions of Excel.

Class XlfOper - Original Design

The crux of the XLW design is that class XlfOper, the wrapper for Excel's XLOPER datatype, shares the same memory footprint as LPXLOPER (XLOPER*). XlfOper declares a single data member of type LPXLOPER such that sizeof(XlfOper) = sizeof(LPXLOPER).

class XlfOper {
    LPXLOPER lpxloper_;
public:
    std::string AsString();
    //...
};

LPXLOPERs passed from Excel to the Addin are received by Addin functions into arguments of type XlfOper. Consider XLW example function xlConcat(), which accepts as input two strings which are concatenated and returned. This function is registered with Excel such that Excel thinks the datatype of the input parameters is LPXLOPER.

LPXLOPER xlConcat(LPXLOPER xlStr1, LPXLOPER xlStr2);

In fact the function is declared as accepting input parameters of type XlfOper.

LPXLOPER xlConcat(XlfOper xlfStr1, XlfOper xlfStr2);

Excel passes arguments of type LPXLOPER, which are received by the function into parameters of type XlfOper. The XlfOper constructor is not invoked, the LPXLOPER value passed by Excel simply populates the LPXLOPER data member of the XlfOper argument. XlfOper's member functions then serve as functionality bound to that reference, and the implementation of xlConcat() accesses the LPXLOPER via its XlfOper wrapper.

std::string str1 = xlfStr1.AsString();

The operation above exploits the fact that no type checking is performed in this call across DLL boundaries. This is undefined behavior, which happens to work with all versions of Visual C++ (so far) but there is no guarantee that any compiler should support this feature and in particular MinGW (gcc) does not. MinGW stores PODs (such as LPXLOPER) differently than user defined types (such as XlfOper), and when compiled with MinGW the above code crashes at runtime.

To ensure type safety, the function must be declared exactly as it is registered with Excel - with parameters of type LPXLOPER. These values must then be passed explicitly to the XlfOper constructor.

LPXLOPER xlConcat(LPXLOPER xlStr1, LPXLOPER xlStr2) {
    XlfOper xlfStr1(xlStr1);
    std::string str1 = xlfStr1.AsString();
    //...
}

Class XlfOper - New Design

Class XlfOper is enhanced to allow it to occupy the memory footprint of either LPXLOPER or LPXLOPER12.

class XlfOper {
    union { 
        LPXLOPER lpxloper4_;
        LPXLOPER12 lpxloper12_;
    };
public:
    double AsDouble();
    //...
};

Now the class requires run time logic to determine which pointer to dereference. Ideally the switch is implemented via polymorphism, e.g. some abstract notion of an LPXLOPER, concretely instantiated as either LPXLOPER (4) or LPXLOPER12 depending on which version of Excel is detected. But no virtual function can be added to class XlfOper because the corresponding vtable would result in XlfOper no longer being bitwise equivalent to LPXLOPER.

The logic around the LPXLOPER/LPXLOPER12 reference is passed off to friend class XlfOperImpl. XlfOper can't hold a reference to XlfOperImpl so instead the latter is implemented as a polymorphic Singleton, an abstract base class which is instantiated at runtime by one of two concrete derived classes, XlfOperImpl4 or XlfOperImpl12, depending on which version of Excel is detected at startup.

class XlfOperImpl {
    static XlfOperImpl *instance_;
public: 
    static XlfOperImpl &instance() { return *instance_; }
    XlfOperImpl() { instance_ = this; }
    virtual double AsDouble(const XlfOper &xlfOper) = 0;
};  

class XlfOperImpl4 : public XlfOperImpl {
    virtual double AsDouble(const XlfOper &xlfOper) {
        return xlfOper.lpxloper4_->val.num;
    }   
};

class XlfOperImpl12 : public XlfOperImpl {
    virtual double AsDouble(const XlfOper &xlfOper) {
        return xlfOper.lpxloper12_->val.num;
    }
};

Calls to XlfOper are forwarded to XlfOperImpl for execution by the appropriate derived class.

double XlfOper::AsDouble() {
    return XlfOperImpl::instance().AsDouble(*this);
}

Usage

Consider legacy code from XLW client applications:

LPXLOPER xlCirc(XlfOper xlfDiam) {
    double diam = xlfDiam.AsDouble();
    //...
}

Or the alternative implementation where MinGW support is required:

LPXLOPER xlCirc(LPXLOPER xlDiam) {
    XlfOper xlfDiam(xlDiam);
    double diam = xlfDiam.AsDouble();
    //...
}

The above code can be compiled without amendment under the new version of XLW into an XLL which is compatible with both Excel 4 and 12. The type XlfOper when run under Excel 12 automatically picks up support for Excel12 features. However both of the code examples above contain explicit references to the Excel 4 datatype LPXLOPER, and in those places the code does not acquire Excel 12 functionality.

Full interoperability requires a function which implements the LPXLOPER type when run under Excel 4 and the LPXLOPER12 datatype when run under Excel 12. This flexibility is achieved through use of the void* type, which accepts LPXLOPER or LPXLOPER12 depending on the version of Excel detected at runtime. The following typedef serves to clarify the intentions of the code:

typedef void* LPXLFOPER;

The function may then be implemented as

LPXLFOPER xlCirc(LPXLFOPER xlDiam) {
    XlfOper xlfDiam(xlDiam);
    double diam = xlfDiam.AsDouble();
    //...
}

When running under Excel 4, XLW registers the above function as receiving and returning LPXLOPER. When running under Excel 12, XLW registers the above function as receiving and returning LPXLOPER12. The single source code implementation serves in either case and when run under Excel 12 the code dynamically picks up support for Excel 12 features.

Where MinGW compatibility is not a concern, the code can be simplified to

LPXLFOPER xlCirc(XlfOper xlfDiam) {
    double diam = xlfDiam.AsDouble();
    //...
}

XlfOper4 and XlfOper12

The proposed new implementation of XlfOper allows for a seamless upgrade, at the cost of some runtime performance. The cost is not warranted for applications that know they always require LPXLOPER or LPXLOPER12, and so new classes XlfOper4 and XlfOper12 are provided to map directly to those types.

The table below summarizes the advantages and disadvantages of the supported interfaces.

Class	+	-
XlfOper	Automatically encapsulates either LPXLOPER or LPXLOPER12 Backward compatible with old XlfOper class	Incurs a run time overhead. Each member function forwards its call to a virtual function in the XlfOperImpl singleton.
XlfOper4	No performance overhead compared to old XlfOper class Backward compatible with old XlfOper class	Encapsulates LPXLOPER only Supported by Excel 12 but invokes none of Excel 12's new features
XlfOper12	No performance overhead compared to old XlfOper class Supports new features of Excel 12	Encapsulates LPXLOPER12 only Not supported by Excel 4

Registering Functions

A quick review of how XLW registers user defined addin functions with Excel.

The Old Way

From XLW version 1. Deprecated, but still supported.

XlfArgDesc diameter("diameter", "Diameter of the circle", "B");
XlfFuncDesc circ("xlCirc", "Circ", "Computes the circumference of a circle", "xlw Example");
circ.SetArguments(diameter);
circ.Register();

The New Way

Easier method, introduced in XLW version 2, and used in code created by the InterfaceGenerator utility.

XLRegistration::Arg CircArgs[] = {
    { "Diameter", "Diameter of the circle", "B"}
};
XLRegistration::XLFunctionRegistrationHelper registerCirc(
    "xlCirc", "Circ", "Computes the circumference of a circle",
    "xlw Example", CircArgs, 1, false);

Usage Summary

The table below summarizes various declarations required in addin code depending on which XlfOper class is in use.

Feature	XlfOper	XlfOper4	XlfOper12
Value Type	XLF_OPER	P (OPER *)	Q (OPER12 *)
Reference Type	XLF_XLOPER	R (XLOPER *)	U (XLOPER12 *)
Return Value	LPXLFOPER (void *)	LPXLOPER (XLOPER *)	LPXLOPER12 (XLOPER12 *)
Memory Management Macros	EXCEL_BEGIN / EXCEL_END	EXCEL_BEGIN / EXCEL_END_4	EXCEL_BEGIN / EXCEL_END_12

Value Type and Reference Type are codes recognized by XlfArgDesc (deprecated) and XLRegistration::Arg to register the inputs of a user defined addin function.

• Value Type: For these types, Excel automatically coerces range references, so the addin function receives the values contained in the range rather than the range reference itself. XLF_OPER is automatically interpreted by XLW as either P or Q depending on the version of Excel detected at startup.
• Reference Type: For these types, Excel preserves range references. This is seldom useful for user defined worksheet formulas. XLF_XLOPER is automatically interpreted by XLW as either R or U depending on the version of Excel detected at startup.
• Return Value: In the function implementation, use the return type which corresponds to the XlfOper type returned by the function. LPXLFOPER is automatically interpreted by XLW as either LPXLOPER or LPXLOPER12 depending on the version of Excel detected at startup.
• Memory Management Macros: Addin functions should be wrapped in these preprocessor macros. Choose the flavor of EXCEL_END which corresponds to the XlfOper type used for the function's return value.

For more information, please see the definitions of the above terms in the Reference Manual, and refer to the example applications.