If you’re a C# developer who has tried to do Microsoft Office programming, you know how much of a pain it can be. Most of the methods require tons of parameters, and you end up needing to pass tons of Missing.Value argument around.
If you haven’t done this before, it’s easy to get started writing Office programs. First, make sure you have Office installed. Second, add a reference in your project to the Office application you want to automate.
Now, add the appropriate using clause (using Microsoft.Office.Interop.Excel;) to your C# code. If you use multiple Office applications, you may wish to alias the namespace to prevent naming conflicts. Most of the Office interops use the same class: Application. Now, create your Excel application object for automation.
var excel = new Microsoft.Office.Interop.Excel.Application();
That was easy enough. Here’s the part where it gets nasty… nearly every method you call requires optional parameters and casting.
Worksheet sheet = (Worksheet)book.Sheets.Add(Missing.Value,
Missing.Value, Missing.Value, Missing.Value);
Sheets.Add isn’t so bad, but some methods have dozens of parameters. These only exist to junk up your code. Wouldn’t you much prefer to write the following instead?
Worksheet sheet = book.Sheets.Add();
So would I. In C# 4.0, we get optional parameters and the interop code returns dynamic types, so this is exactly how your code will look next year. But that doesn’t really help us today. Luckily, there is a C# 3.0 feature that will allow you to write readable interop code: extension methods.
public static Worksheet Add(this Sheets sheets)
{
return (Worksheet)sheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value);
}
Just place that method in a static class and you’re good to go. Then, add methods as you need them. Since you can overload extension methods, you still have the flexibility of optional parameters. Here’s the full class I needed for the piece of Office automation I was working on.
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);
}
}
Subscribe