xlSet

When you buy an airline ticket, you generally have to choose between a full-fare ticket with no restrictions and a much cheaper ticket with restrictions. The cheaper ticket might be nonrefundable or require a Saturday night stay.

Similarly, in Microsoft Excel there are two ways to enter information into cells. The usual way, available in the macro language, is to use the FORMULA function, called xlcFormula from C. This is the expensive, unrestricted way. It takes a long time, but you can put almost anything anywhere and, if you don't like the result, you can undo it by calling xlcUndo.

However, there is a much faster function called xlSet. It is available only from the C API, and comes with four restrictions:

• You can enter only constants, not formulas, into cells. This allows Microsoft Excel to skip recomputing the internal formula dependency tree.
• You cannot undo with the xlSet function. This saves time, since Microsoft Excel does not have to record the information required to undo the action.
• You are limited by the maximum array size in Microsoft Excel. Ranges larger than approximately 5,460 cells may fail.
• A function that is called from a worksheet cannot call xlSet. It is a command equivalent function. In other words if the function in the DLL was called as part of recalculation, xlSet will fail.

In spite of these restrictions, xlSet is very useful. In most database access scenarios, you need to write a large table of constant values into a rectangular range of cells. This can all be done with one call to Microsoft Excel.

Note

Because xlSet is a command-equivalent function, it does not work in user-defined functions.

The following code creates a large array and places it into the active sheet, in one step:

int i,j;
static XLOPER rgx[10][10], xArray, xRef;
XLMREF xlmref;

if (xlretSuccess != Excel4(xlSheetId, &xRef, 0))
    return;

xRef.xltype = xltypeRef;
xRef.val.mref.lpmref = (LPXLMREF) &xlmref;
xlmref.count = 1;
xlmref.reftbl[0].rwFirst  = 0;
xlmref.reftbl[0].rwLast   = 9;
xlmref.reftbl[0].colFirst = 0;
xlmref.reftbl[0].colLast  = 9;

for (i=0; i<10; i++)
{
    for (j=0; j<10; j++)
    {
        rgx[i][j].xltype = xltypeNum;
        rgx[i][j].val.num = i * 10 + j;
    }
}

xArray.xltype = xltypeMulti;
xArray.val.array.lparray = (LPXLOPER) &rgx;
xArray.val.array.rows = xArray.val.array.columns = 10;

Excel4(xlSet, 0, 2, (LPXLOPER) &xRef, (LPXLOPER) &xArray);

By using xlCoerce and xlSet, you can speed up data transfer dramatically.