HOWTO: Handle an input with mixed types

How to handle an input with mixed types

Issue

I have an area on a spreadsheet with mixtures of types - strings, booleans and numbers. How do I read them in my add-in function.

Summary

1. Set the argument's type to be COper.
2. Use COper methods to inspect the input, including the following: COper::Cell(), COper::GetDims(), COper::IsBool(), COper::IsDouble(), COper::IsString().

Argument type: COper

Use the XLL+ Function Wizard to set the argument's type to COper.

Inspecting the COper

Use the method COper::GetDims() to get the dimensions of the input array. Use COper::Cell() to inspect any cell within the array.

Use any of the following methods to inspect the type and contents of each cell:

The example function below takes an array of mixed type, reads each cell and sorts the values into numbers, booleans and strings.

// Function:    MixedInputFn
// Purpose:     Reads an input array of mixed types

//{{XLP_SRC(MixedInputFn)
    // NOTE - the FunctionWizard will add and remove mapping code here.
    //    DO NOT EDIT what you see in these blocks of generated code!
IMPLEMENT_XLLFN2(MixedInputFn, "RP", "MixedInputFn", "Mixed", 
    "User Defined", "Reads an input array of mixed types", 
    "An array containing mixed types\000", "\0appscope=1\0", 1)

extern "C" __declspec( dllexport )
LPXLOPER MixedInputFn(const COper* Mixed)
{
    XLL_FIX_STATE;
    CXlOper xloResult;
//}}XLP_SRC

    // Buffers to hold values of each type
    std::vector<std::string> strings;
    std::vector<bool> booleans;
    std::vector<double> numbers;

    // Inspect size
    USHORT rows, cols;
    Mixed->GetDims(rows, cols);

    // Inspect each cell
    for (USHORT r = 0; r < rows; r++)
    {
        for (USHORT c = 0; c < cols; c++)
        {
            const COper& cell = Mixed->Cell(r, c);
            if (cell.IsDouble())
            {
                numbers.push_back(cell.ToDouble());
            }
            else if (cell.IsBool())
            {
                booleans.push_back(!!cell.ToBool());
            }
            else if (cell.IsString())
            {
                strings.push_back(cell.ToString());
            }
        }
    }

    // Get the size of the largest result vector
    size_t maxRows = numbers.size();
    if (booleans.size() > maxRows)
        maxRows = booleans.size();
    if (strings.size() > maxRows)
        maxRows = strings.size();

    // Arrange the results in 3 columns
    xloResult.AllocArray(maxRows, 3);
    size_t i;
    for (i = 0; i < numbers.size(); i++)
        xloResult.Cell(i, 0) = numbers[i];
    for (i = 0; i < booleans.size(); i++)
        xloResult.Cell(i, 1) = booleans[i];
    for (i = 0; i < strings.size(); i++)
        xloResult.Cell(i, 2) = strings[i];

    return xloResult.Ret();
}