Excel object model

While you can use the Excel C++ API to manipulate workbooks and their contents from add-in macros, it is often very hard work, and the range of options is limited.

The Excel object model is available from Visual Basic for Applications (VBA) macros written inside spreadsheets, and provides a complete and reasonably consistent and intuitive interface into Excel objects. Using XLL+ 6.0.4 upwards, you can access this model easily from add-ins.

Office Primary Interop Assemblies

This set of assemblies is the approved route for accessing Excel's object model (which is implemented using COM) via .NET. They are usually installed into the Global Assembly Cache (GAC) by the Visual Studio installer.

If you want to use the Excel object model, you need to add a reference to your project.

1. Open the Project Settings window.
2. Select the page "Common Properties/References".
3. Click "Add New Reference..." (and prepare for a potentially long wait).
4. On the ".NET" page, select "Microsoft.Office.Interop.Excel", and press "OK".

If you cannot find the assembly on the .NET page, then you may need to install it. Search the Microsoft web-site for Office Primary Interop Assemblies to find a downloable installer.

You may find it saves you a lot of typing if you also add the following line to your main source file:

using namespace Microsoft::Office::Interop::Excel;

Getting the Application object

Everything in the Excel object model is accessible via the Application object. You can get access to the application by using CXllApp::GetApplicationObject. The code fragment below assumes that you have added the statement using namespace Microsoft::Office::Interop::Excel; to your code.

_Application^ theApp = safe_cast<_Application^>(CXllApp::Instance()->GetApplicationObject());

Note that the earliest time you can call GetApplicationObject() is during the event handler OnXllOpenEx(). You can also call it during any add-in function or macro. You cannot call it during InitInstance() or during the constructor of any global objects.

Using the object model

Once you have a handle to the Application object, you can use it to access the hierarchy of Excel objects.

The code fragment below calls various objects, properties and methods of the Excel object model to create, format and populate a new workbook.

// Create a new workbook
theApp->Workbooks->Add(System::Reflection::Missing::Value);

// Create a range that points to the 4 cells in the top-left corner
_Worksheet^ ws = safe_cast<_Worksheet^>(theApp->ActiveSheet);
Range^ range = safe_cast<Range^>(ws->Cells[1, 1])->Resize[2, 2];

// Apply values and formats to the range
range->Interior->ColorIndex = 4;
range->Interior->Pattern = Constants::xlSolid;
range->Font->ColorIndex = 3;
range->Value2 = "A value";

// Select C3:D4
range->Offset[2, 2]->Select();

A number of points are worth noting here:

• Missing values are represented by System::Reflection::Missing::Value. They cannot simply be omitted.
• A number of properties in the Excel model, including Application.ActiveSheet and Range.Cells, return untyped objects. You need to use safe_cast to convert them to the appropriate interface.
• Many methods are unexpectedly implemented as properties. Range.Resize is a commonly used example of this.
• The class Microsoft::Office::Interop::Excel::Constants contains many of the Excel constants.

See the PiaDemo sample for an example of using the Excel object model in C++/CLI.

Next: Sized result arrays >>