Excel extensions with VSTO power tools

Apparently, VSTO Power Tools have been around for a while (Feb 02), but if I had not read this article, I would have missed them - which would be too bad, because they are awesome. The Power Tools consist of a few dlls, which, while not officially supported, have been released by Microsoft developers. I started playing with the Excel Extensions, and I love it; it is a “very thin wrapper to the Office primary interop assemblies”, which essentially gives you cleaner methods to access the Excel object, with type safety, and without the clumsy “Missing.Value” arguments.

I always ended up adding a utility class to my projects, with a few simplified static methods to do things like find a sheet by name in a workbook; this does all of that, but way better. For instance, instead of the awkward:

Microsoft.Office.Interop.Excel.Application excel = AddIn.Application;
Excel.Workbook workbook = excel.ActiveWorkbook;
Excel.Worksheet worksheet = null;
foreach (Excel.Worksheet aSheet in workbook.Worksheets)
{
    if (aSheet.Name == "Sheet1")
    {
        worksheet = aSheet;
        break;
    }
}
Excel.Range startCell = worksheet.get_Range("A1", Missing.Value);
Excel.Range endCell = worksheet.get_Range("B2", Missing.Value);
Excel.Range range = worksheet.get_Range(startCell, endCell);
range.Select();

you can type something like:

Microsoft.Office.Interop.Excel.Application excel = AddIn.Application;
Excel.Workbook book = excel.ActiveWorkbook;            
Excel.Worksheet worksheet = book.Sheets.Item<Excel.Worksheet>("Sheet1");
Excel.Range range = worksheet.Range("A1:B2");
range.Select();

Much nicer, no?

If you like working in C# and develop for Office, go get it there, and check this post!

Comments

Have a comment or a question? Ping me on Twitter, or use the comments section!