Using the Excel Object Model and Performance

July 20, 2004

Recently I’ve had to write a bit of code which communicates with Microsoft Excel using its object model. Here are a few things I have learned from this experience.

Excel::_Application app;
// Create and work with app...
  • Even if Excel is not visible to the user, Application::Quit() may pop up a hidden dialog asking if the user wants to save the changes that were made through the dialog box. Since the dialog is not visible, Excel will never shut down. To prevent this dialog, either set Application.DisplayAlerts to false or set Workbook.Saved to true for all modified workbooks. The former is preferred.
  • Each call using the Excel object model is very, very slow, probably as a result of the use of IPC. This means that the typical way one would think of interacting with cell values in Excel — iterating cell-by-cell within a set of nested for loops — is often too slow to be practical. Instead, I work in selections of nRows rows by nCols columns and use a two-dimensional SAFEARRAY. For example:
COleSafeArray rawData;
DWORD rawDataDimensions[2];
rawDataDimensions[0] = nRows;
rawDataDimensions[1] = nCols;
rawData.Create(VT_VARIANT, 2, rawDataDimensions);
// Populate the values of rawData...
// Select a range of size nRows x nCols
Excel::Range range = wksheet.GetRange(varUpperLeftCell, varLowerRightCell);
// Set the cells' values in one call to .SetValue()
// instead of setting individual cell values