Uses the Excel object model, via the Primary Interop Assemblies

Overview

This project shows how to gain access to the Excel object model in C++/CLI, and how to use Excel object model classes, properties and methods to create, format and populate a workbook.

For more information on using the Excel object model via .NET, see Using the Excel object model in the User Guide.

Run-time requirements

Please note that if you are using Visual Studio 2005, you must have Service Pack 1 installed in order to build a valid XLL.

If the sample fails to load into Excel at run-time, please see the technical note .NET requirements.

This sample contains its own copy of the .NET Primary Interop Assemblies for Excel. If you already have these assemblies installed into the GAC, you can remove these assemblies, and replace the reference to the private assembly with a reference to the installed version as described below.

Project setup

In order to make this project build and run, the following steps were required:

• Check the /clr option in the XLL+ Application Wizard.
• Add a reference to Microsoft.Office.Interop.Excel.dll, which can be found in the ".NET" page of the "Add New Reference..." dialog. (You can add an assembly reference by opening the "Common Properties/References" page of the Project Properties dialog and clicking "Add New Reference...".)
• For programming convenience, add the line using namespace Microsoft::Office::Interop::Excel;

Application object

The add-in function UseObjectModel first gets a handle to the Excel Application object from the XLL+ framework, and casts it to an _Application interface:

_Application^ theApp = safe_cast<_Application^>(
    CXllApp::Instance()->GetApplicationObject());
if (theApp == nullptr)
    return CXlOper::RetError(xlerrNA);

Note the use of safe_cast to safely cast the application object.

If the interface cannot be found, the function aborts.

Using the object model

The add-in function then 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.

Classes and functions used

CXllApp::GetApplicationObject

Sample project

Each sample project is located in a sub-directory of the Samples directory of the XLL+ installation. To use the sample project, open the solution file PiaDemo.sln or the project file PiaDemo.vcproj.

You can enable debugging under Excel by using the Setup Debugging command in the XLL+ ToolWindow.

When delivered, the help files are excluded from the build. You can enable the help build by selecting the files PiaDemo.help.xml and PiaDemo.chm in the Solution Explorer, and using the right-click menu to view Properties. Select the page "Configuration Properties/General" and set the "Excluded from build" property to "No". See Generating help in the User Guide for more information.

See Also

List of Sample Projects