Introduction

Examples of inserting text and formulas into an Excel spreadsheet are in abundant supply (see References.) So when I was faced with the task of inserting images into a spreadsheet, I figured an appropriate example wouldn't be hard to find. Boy, Was I Wrong. Now, it could just be that I'm a real lightweight when it comes to search engines. Or, perhaps it's obvious to everyone else how this is done. But one comment posted last year by a fellow CPian gave me some confidence that this article might have some utility after all. It took me a painful couple of days to figure it all out, and I hope to spare others the same frustration.

This article demonstrates how to insert a set of images into an Excel spreadsheet using the Excel automation API. The demo application allows the user to choose an Excel workbook, a set of image files, and one of the worksheets where the images will be inserted. One click later and the images, each given a one-pixel finishing border, are stacked vertically in the worksheet. The user is then given the option to save the workbook or close it.

Note: The project was written in Visual Studio .NET 2002 and tested on WinXP. It should work with MS Office versions 2000 and XP. Using different versions of Visual Studio and MS Office is an exercise left to the reader.

Using the Excel automation API

Before we begin, allow me to set one thing straight: I am a novice when it comes to automation, COM, and other such dirty topics. What I do know is that automation is a fancy word for a rather simple concept. To paraphrase one simple explanation offered by another CP fellow, automation is a programming interface--more specifically, a COM interface--exposed by an application that allows another application to interact with it. If after reading this section you are interested in a deeper exploration of automation or COM, start here.

Excel conveniently provides an automation interface that allows you to control its many functions. In the context of this example, Excel is the automation server and my program is the automation client. Excel's automation interface is encapsulated in a type library--also called an object library--distributed with Excel. A type library contains type definitions, such as class names, that describe the automation/COM interface. (Think of it as a fancy header file.) There are different versions of the type library depending on the version of Excel you use. I own and operate Excel 2000, which uses version 9.0 of the type library (as does Excel XP.) Earlier versions of Excel use version 8.0 of the type library. Honestly, I don't know whether there is any backward compatibility between these versions. But fear not: the type libraries are not substantively different for many of the basic functions. There are many examples, some of which are pointed to in the References section, which can help you adapt my code. I would do it myself if I owned an earlier version of Excel.

Importing the type library

Armed with your type library, there are two ways you can put it to use, converting the library's content into a set of C++ classes. The first method is to use the #import directive, which looks something like...

#import "C:\Program Files\Microsoft Office\Office\EXCEL9.OLB" \
  rename("DialogBox","_DialogBox") \
  rename("RGB","_RGB") \
  exclude("IFont","IPicture")

This is a perfectly fine method. What bothers me about it, however, is that at compile time you have to know where the type library is located. If I build this code on machines where Excel is installed elsewhere, then I have to go searching for the type library. Although this would require probably nothing more than a different drive letter, I'm lazy and I get upset when my code breaks.

The second method is to use the Add Class wizard in Visual Studio. I won't go into detail here because the steps are outlined in one MSDN article for users of Visual Studio. NET and another MSDN article for users of Visual Studio 6.0. Briefly, the Add Class wizard allows you to select a type library (from a precompiled list in .NET; in 6.0 you must still locate the library manually, but only once, when the project is first created.) Visual Studio will happily parse the library and generate a set of header files containing class definitions that can be used to create your Excel automation client. Once the files have been generated, they need never be generated again. (Unless, of course, you begin using a different version of the type library.) These files appear in my example project under the "Spreadsheet" subdirectory. Take a look at the class files. They won't reveal much, as they are really nothing more than a huge set of function lookup tables. But they're worth a gander nonetheless.

As an aside, I want to be a little more clear about my stated bias. Although I have my favorite, I don't mean to disparage the first method for extracting class definitions from the type library. In fact, I first learned about automating Excel from a CP article that uses this method. I mention both methods because I want people to realize they have a choice. My own choice is based mostly on my personal aesthetic, which you may not share.

The class definitions can be used by simply including the generated header files in your project. There may be as many classes in the Excel API as there are features in the application. (Well, not really, because a single class can encapsulate a common set of features. But there are a lot of classes.) Here's a brief summary of the classes I use in this example.

`CApplication`	Provides a handle to the active Excel process. Allows you to control whether Excel is visible, whether the user can directly control it, etc.
`CWorkbooks`	This class represents the functions that can act on a set of workbooks. More specifically, it's used to open individual workbooks.
`CWorkbook`	Handle to an open workbook.
`CWorksheets`	Top-level worksheet manager, like `CWorkbook`s but for worksheets.
`CWorksheet`	Handle to a specific worksheet object.
`CRange`	Handle to a range of cells in a worksheet.
`CPictures`	Top-level picture manager.
`CPicture`	Handle to a specific picture object.
`CBorder`	Handle to a border object.

About the demo program

I'll briefly summarize the workflow of the demo program. Rather than cut-and-paste large sections of code into the article, I thought it best to simply point out where the work is being done. The interested reader can look at the commented source code for the behind-the-scenes magic.

Initialize the COM library

The first step in building our automation client is to initialize the COM library. The COM library must be explicitly loaded by calling CoInitialize(), and later unloaded by calling CoUnitialize(). A sensible place to do this is in the application's InitInstance() function.

BOOL CExcelImagesApp::InitInstance()
{
  ...
  
  CoInitializeEx (NULL, COINIT_APARTMENTTHREADED);

  // main dialog goes modal

  CoUnitialize ();
  
  ...
}

Start an Excel (automation server) process

I decided to start an Excel process in the main dialog's OnInitDialog() function. You could just as easily fire it up immediately after initializing the COM library. But since we don't need it during the application's entire lifetime, there's no need to have the process hanging around idly, looking for mischief.

BOOL CExcelImagesDlg::OnInitDialog();

Open a workbook

From the main dialog, the user may choose a spreadsheet where the images will be placed. (I have included a blank spreadsheet and set of images in the demo project for those who desire immediate gratification.) Once selected, the workbook is opened by a call to OpenWorkbook() (from the OnPostBrowse() function.)

void CExcelImagesDlg::OnPostBrowse(NMHDR *pNMHDR, LRESULT *pResult);
void CExcelImagesDlg::OpenWorkbook (CString szWorkbook);

If the workbook is opened successfully, the names of the individual worksheets are retrieved and used to populate the dialog's combo box. The user can then choose one of these sheets for the placement of the images.

Place the images

The user browses for a collection of images (any type should do), and then presses the "Place Images" button. This causes InsertPictures() to be invoked (via OnBnClickedPlaceImages()). InsertPictures stacks the images vertically in the worksheet by calculating height of each picture in row units. Each picture is given a one-pixel border to finish it off.

void CExcelImagesDlg::OnBnClickedPlaceimages();
void CExcelImagesDlg::InsertPictures ();

Close the workbook

An open workbook is closed when either a new workbook is chosen or the user closes the dialog. This is handled by the CloseWorkbook() function.

void CExcelImagesDlg::CloseWorkbook();

Close Excel

The Excel process is finally closed when the user closes the dialog. This happens in the dialog's OnDestroy() function.

void CExcelImagesDlg::OnDestroy();

References

Credits

Thanks to PJ Arends for his File Edit Control. You’ll see it in action when you browse for the workbook and the image files.

History

Version 1.0 (September 22, 2004): it's alive... it's alive!

Placing images in Excel using automation