Sunday, July 18, 2010

Dynamic .NET 4.0 to the rescue

With the new .NET 4.0 features in mind I was reviewing some code from a previous project written in C# 3.0. The application was interacting with Excel quite a lot. One of the requirements of the project was that Excel should be able to represent a remote database structure (with one-many and many-many relationships)  accessed through a web service with drop downs for related values.

The old code for doing the dynamic adding of VBA combo boxes in the cells of the relevant columns looked like the following

internal void AddComboBox(Worksheet sheet, Range relateCell, FieldMetadata fieldMetadata, string selectedValue)
{
    Microsoft.Office.Interop.Excel.Shape comboBox = sheet.Shapes.AddOLEObject(
        "Forms.ComboBox.1",
        Missing.Value, 
        Missing.Value,
        Missing.Value, 
        Missing.Value,
        Missing.Value, 
        Missing.Value,
        relateCell.Left,
        relateCell.Top,
        relateCell.Width,
        relateCell.Height
    );

    OLEObject oleComboBox = (OLEObject)comboBox.DrawingObject;
    MSForms.ComboBox cb = (MSForms.ComboBox)(oleComboBox.Object);

    ...

    VbaHandler.SetComboBoxEventHandler(ExcelApplication.ActiveWorkbook, sheet, oleComboBox);
    RegisterCallbackHandlerForMacroModule(sheet);

    // save ComboBox to disable garbage collection on it
    SupportComboBoxes.Add(new KeyValuePair<int, string>(relateCell.Row, GetDataColumnNameByIndex(sheet, relateCell.Column)), oleComboBox);
}


Using optional parameters, named parameters and the dynamic keyword made the code nicer and remove a lot of the plumbing syntax from previous versions of the .NET framework. The code now looks like


internal void AddComboBox(Worksheet sheet, Range relateCell, FieldMetadata fieldMetadata, string selectedValue)
{
    Microsoft.Office.Interop.Excel.Shape comboBox = sheet.Shapes.AddOLEObject(
        ClassType : "Forms.ComboBox.1",
        Left      : relateCell.Left,
        Top       : relateCell.Top,
        Width     : relateCell.Width,
        Height    : relateCell.Height
    );

    dynamic oleComboBox = comboBox.DrawingObject;
    dynamic cb = oleComboBox.Object;

    ...

    VbaHandler.SetComboBoxEventHandler(ExcelApplication.ActiveWorkbook, sheet, oleComboBox);
    RegisterCallbackHandlerForMacroModule(sheet);

    // save ComboBox to disable garbage collection on it
    SupportComboBoxes.Add(new KeyValuePair<int, string>(relateCell.Row, GetDataColumnNameByIndex(sheet, relateCell.Column)), oleComboBox);
}
Post a Comment