I’m still in the middle of converting a few tools, and the latest is a Visual Studio package I created. This package allows our developers to edit decision tables in a grid rather than XML, and one of the features requested was the ability to export to Excel. Since the editor was implemented with a DataGridView, I decided it would be nice if the DataGridView could export its own information to excel. Our project used a simple grid layout, so you may need to make modifications if you’re planning on using any of the code.

public static void ExportToExcel(this DataGridView dataGridView,
    string fileName, string worksheetName)
{
    if (dataGridView == null)
    {
        throw new ArgumentNullException("dataGridView");
    }

    var excel = new Microsoft.Office.Interop.Excel.Application();
    Workbook book = null;
    Worksheet sheet = null;
    try
    {
        if (File.Exists(fileName))
        {
            book = excel.Workbooks.Open(fileName);
        }
        else
        {
            book = excel.Workbooks.Add();
        }

        sheet = book.Sheets.Add();

        sheet.Name = worksheetName;

        for (int i = 0; i < dataGridView.Columns.Count; i++)
        {
            sheet.Cells[1, i + 1] = dataGridView.Columns[i].Name;
        }
        sheet.get_Range(sheet.Cells[1, 1],
            sheet.Cells[1, dataGridView.Columns.Count]).Font.Bold = true;

        for (int row = 0; row < dataGridView.Rows.Count; row++)
        {
            for (int column = 0; column < dataGridView.Columns.Count; column++)
            {
                sheet.Cells[row + 2, column + 1] =
                    dataGridView.Rows[row].Cells[column].Value;
            }
        }

        book.SaveAs(fileName);
        book.Close();
    }
    finally
    {
        excel.Workbooks.Close();
        excel.Quit();
        Marshal.ReleaseComObject(sheet);
        Marshal.ReleaseComObject(book);
        Marshal.ReleaseComObject(excel);
    }
}

Anyone working with excel COM object knows that the methods usually consist tons of Missing.Value arguments. This was made easier in .NET 4 with the dynamic keyword, but this was a .NET 3.5 project. I made my code easier to read by using extension methods to define the calls the way I wanted them to be.

/// <summary>
/// Hide the ugliness of Excel interop.
/// </summary>
internal static class ExcelExtensions
{
    public static void Close(this Workbook workbook)
    {
        workbook.Close(null, null, null);
    }

    public static void SaveAs(this Workbook workbook, string fileName)
    {
        workbook.SaveAs(fileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, 
            Missing.Value, XlSaveAsAccessMode.xlShared, Missing.Value, Missing.Value, 
            Missing.Value, Missing.Value, Missing.Value);
    }

    public static Workbook Add(this Workbooks workbooks)
    {
        return workbooks.Add(Missing.Value);
    }

    public static Workbook Open(this Workbooks workbooks, string fileName)
    {
        return workbooks.Open(fileName, Missing.Value, Missing.Value, Missing.Value, 
            Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, 
            Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, 
            Missing.Value);
    }

    public static Worksheet Add(this Sheets sheets)
    {
        return (Worksheet)sheets.Add(Missing.Value, Missing.Value, Missing.Value, 
            Missing.Value);
    }
}

With the dynamic keyword, and named/optional parameters in .NET 4, this class is unnecessary. In fact, when I converted the solution I discovered the extensions no longer work. Since the parameters are now optional, the classes' methods are preferred over the extension methods. It also becomes necessary to use the the dynamic keyword with the sheet variable so the implicit conversions will take place… otherwise you must cast it.

public static void ExportToExcel(this DataGridView dataGridView,
    string fileName, string worksheetName)
{
    if (dataGridView == null)
    {
        throw new ArgumentNullException("dataGridView");
    }

    var excel = new Microsoft.Office.Interop.Excel.Application();

    try
    {
        Workbook book;

        if (File.Exists(fileName))
        {
            book = excel.Workbooks.Open(fileName);
        }
        else
        {
            book = excel.Workbooks.Add();
        }

        dynamic sheet = book.Sheets.Add();

        sheet.Name = worksheetName;

        for (int i = 0; i < dataGridView.Columns.Count; i++)
        {
            sheet.Cells[1, i + 1] = dataGridView.Columns[i].Name;
        }
        sheet.get_Range(sheet.Cells[1, 1],
            sheet.Cells[1, dataGridView.Columns.Count]).Font.Bold = true;

        for (int row = 0; row < dataGridView.Rows.Count; row++)
        {
            for (int column = 0; column < dataGridView.Columns.Count; column++)
            {
                sheet.Cells[row + 2, column + 1] =
                    dataGridView.Rows[row].Cells[column].Value;
            }
        }

        book.SaveAs(fileName, AccessMode: XlSaveAsAccessMode.xlShared);
        book.Close();
    }
    finally
    {
        excel.Workbooks.Close();
        excel.Quit();
    }
}

This isn’t much different than with the extension methods, but it completely eliminates the need. Also, since these objects are handled through the DLR, I cut out the Marshal.ReleaseComObject. Without those calls, the file would become locked and unable to be opened with another instance of Excel. I will update the post if I find through testing that it is still necessary. However, I suspect this entire class is going away because the move to 2010 may necessitate a WPF designer, and I would hate to leave a WinForms artifact lying around.