Doing the dynamic thing (Part III)

In the first part of this series I gave an introduction to what the dynamic keyword can do for you. The second part of this series showed you how this dynamic keyword can be pretty handy when interoperating with javaScript code. in this third part we will look into interoperating with Excel. This part will be brief, though, mainly because there are already quite a lot of examples to be found concerning this subject.
In this post I will extend the example I started in the previous post. There, we've seen I get a list of Person data from a WCF service. This Person list is shown in a DataGrid control of a Silverlight application. The latest version of Silverlight, supports running out of browser and once you run out of browser you get some extra possibilities for your application. One of these possibilities is interacting with automation objects, like Excel. So, let's extend our example so it can run out of browser and also, so it can generate an Excel document.
Running out of browser is actually not that hard to do. The user of your application can right click on the Silverlight application and choose 'Install NameOfYourApplication onto this computer'.

To offer this right click functionality to your users you need to check the 'Enable running application out of the browser' checkbox in the project settings of your Silverlight application.

The button control under this checkbox will show a popup window with some extra settings. One of these extra settings, 'require elevated trust', is important if you want to offer automation functionalities in your out of browser Silverlight application, so you should check this as well. 

Once you have done this, your users can install your application. 

In code it is possible to test whether or not you are running out of browser. I, for instance, added some code to disable a button that generates HTML if we are running out of browser, since generating HTML is pretty useless if you're not running in a browser window (and if you do execute the generation of HTML out of browser you will get a runtime error, so better disable it).

btnHTML.IsEnabled = !Application.Current.IsRunningOutOfBrowser;

We can also test whether or not we can use the automation capabilities.

if (AutomationFactory.IsAvailable)

Once we know we have automation available, we can create a new Excel object. The return type of the GetObject and CreateObject calls is dynamic, so this is a good choice for the type of your variable.

dynamic ExcelApp;
try
{
    ExcelApp = AutomationFactory.GetObject("Excel.Application");
}
catch (Exception exc)
{
    try
    {
        ExcelApp = AutomationFactory.CreateObject("Excel.Application");
    }
    catch (Exception exc2)
    {
        txtStatus.Text = exc2.Message;
        return;
    }
}

The reason I use both GetObject and CreateObject is that if Excel is already running, we can get a reference to the application with GetObject. If Excel is not already running, we need to start, or create the application with CreateObject.

Once we have the Excel application at our disposal, we can create workbooks, we can add data to cells, etc. The good news is that, since version 4.0 of the .NET framework all objects in Excel (and in the other Office automation frameworks) are of type dynamic. This simply means you can omit a lot of casting operators. Where previously you needed to write code like this:

var ExcelApp = new Excel.Application();
ExcelApp.Visible = true;
Workbook workbook = ExcelApp.Workbooks.Add();
int rowCounter = 1;
foreach (DataService.Person p in dataGrid1.ItemsSource)
{
    ((Range)((Worksheet)workbook.Sheets[1]).Cells[rowCounter, 1]).Value = p.FirstName;
    ((Range)((Worksheet)workbook.Sheets[1]).Cells[rowCounter, 2]).Value = p.LastName;
    rowCounter++;
}
((Range)((Worksheet)workbook.Sheets[1]).Columns[1]).AutoFit();
((Range)((Worksheet)workbook.Sheets[1]).Columns[2]).AutoFit();

You can now write this much more briefly like this:

ExcelApp.Visible = true;
dynamic workbook = ExcelApp.Workbooks.Add();
int rowCounter = 1;
foreach (DataService.Person p in dataGrid1.ItemsSource)
{
    workbook.Sheets[1].Cells[rowCounter, 1].Value = p.FirstName;
    workbook.Sheets[1].Cells[rowCounter, 2].Value = p.LastName;
    rowCounter++;
}
workbook.Sheets[1].Columns[1].AutoFit();
workbook.Sheets[1].Columns[2].AutoFit();

As you can see, there is a lot of casting going on in the first example, primarily to the Worksheet type and to the Range type. This is something we can completely omit when using dynamic. Which, in my opinion is a good thing!

The bad news, however, is, that with these dynamic types, your intellisense is gone. You need to know which operation calls are valid on which objects. But than again, who cares, because previously you needed to know which type you needed to cast to. If you got that one wrong, you'd get a runtime exception as well.

If you want to know even more on automation, this article in the MSDN library can give you some extra info.

That's it for the Office automation part of this series. Be ready to do some serious stuff with ExpandoObjects in the next post.