Revisiting Excel Interop

I ran into problem today relating to Excel interop. A coworker made a change to a C# application I wrote and was trying to build it. The program relied on a project which had a reference to the Microsoft Excel 9.0 Object Library which ships with Office 2000. However, the coworker had Office 2003 installed which includes the Excel 11.0 Object Library and not the Excel 9.0 Object Library. Because of this, he could not build the application.

I first considered copying the Excel 2000 executable to his machine to try to make the Excel 9.0 Object Library available, but I decided this was nasty and possibly illegal. I next considered using the Microsoft Office XP Primary Interop Assemblies but ruled them out because I must support Office 2000. I then spent a good amount of time trying to use the Excel 5.0 Object Library (distributed by both Office 2000 and Office 2003) but I ran into some fairly challenging issues relating to the lack of documentation and difficulties with the autogenerated wrapper classes.

My eventual solution was to use the lowest common denominator: my own custom Excel wrapper classes which map directly to objects in the Excel object model but internally use OLE automation (a.k.a. run-time binding a.k.a. IDispatch). The wrapper classes inherit from AutoReleaseComObject to get nice IDisposable-based COM Release() semantics (which is important for Excel!). A sample wrapper class looks something like:

// Maps directly to the Workbooks object in the Excel
// object model.
public sealed class ExcelWorkbooks : AutoReleaseComObject
{
    // Needed for .InvokeMember()
    private Type m_excelWorkbooksType;

    public ExcelWorkbooks(object comObject) : base(comObject)
    {
        m_excelWorkbooksType = comObject.GetType();
    }

    // Maps directly to the Workbooks.Add() method in the
    // Excel object model.
    public ExcelWorkbook Add()
    {
        object o = m_excelWorkbooksType.InvokeMember
            (
            "Add",
            BindingFlags.InvokeMethod,
            null,
            this.ComObject,
            new object[] {}
            );
        return new ExcelWorkbook(o);
    }
}

These IDispatch-based wrapper classes give me maximum flexibility with minimum dependencies (and zero compile-time dependencies) but they are extraordinarily tedious to write. In the future I may experiment with using custom attributes and run-time code generation through System.CodeDom to simplify writing these wrappers. Alternatively, I may look into generating the code at compile-time but that would probably mean moving to a more sophisticated build tool such as NAnt.

Minimize your dependencies is an important rule to keep in mind when programming.

Advertisements