Chapter 12: COM InteropPInvoke


Chapter 12: COM Interop/PInvoke

The .NET Framework has a remarkable collection of several thousand classes, designed to handle almost every conceivable programming need. Yet there are times when the developer needs a little extra flexibility or performance and turns to legacy applications or low- level system calls. In fact, until all programs are rewritten to .NET standards, you’ll probably have a continuing need to interoperate with the COM world and unmanaged code.

Although you pay a performance penalty whenever you interoperate (data marshaling and additional instructions being the culprits), you sometimes either have no choice because the required functionality doesn’t yet exist in a .NET application, or you simply prefer the legacy applications or functions. This chapter describes how you can make your .NET applications use Microsoft Excel, Microsoft Word, and Microsoft Internet Explorer and how you can call unmanaged dynamic-link library (DLL) functions from managed code.

Application #93: Automate Office via COM Interop

This sample application shows you how to automate Microsoft Office from .NET.

start sidebar
Building Upon…

Application #7: Object-Oriented Features

end sidebar

New Concepts

Not every application can be all things to all people, so sometimes you’ll find that you need the help of another program to accomplish your goals. For example, you might have some raw numbers you’re processing in your Microsoft Visual Basic .NET application, and you decide that you need some sophisticated statistical computations done on the data. One way to meet the need is to use an Excel workbook to process the numbers.

However, you don’t have to click a shortcut to call up Excel because, like all the major Microsoft Office programs, Excel exposes its object model to other programs, which can then invoke and manipulate it programmatically.

This process is known as Automation, and it lets one application (the client) create an instance of another (the server) and use the commands from the server’s object model to perform a variety of tasks. This sample application shows how you can use Automation to access Microsoft Office applications from a Visual Basic .NET application.

Automation lets you use a program such as Word or Excel like a component, so you must first set a reference to the Word or Excel object library. Then you can programmatically create an instance of Word or Excel and get some work done with it.

Adding a Reference to a COM object

If you want to set a reference to Excel, for example, choose Project | Add Reference. In the Add Reference dialog box, choose the COM tab (because Office applications live in the COM world). Find the reference you need, such as Microsoft Excel 10.0 Object Library, click Select, and then click OK. The reference will be set and will show up in your References folder as Excel.

Behind the scenes, however, Microsoft Visual Studio has performed a little sleight of hand on your behalf by creating a runtime callable wrapper (RCW) around the COM component and setting the reference to the wrapper rather than to the COM component itself. The wrapper is needed because your .NET application has no idea how to interact with a COM component, which requires that you access it via interfaces—and that’s just not the .NET way.

The RCW bridges the gap between .NET and COM by serving as the broker between the two worlds, translating calls from your application into the format the COM component wants and marshalling data back and forth between the two entities.

You can create an RCW in a couple ways. One is by using a command-line utility named tlbimp (type library importer), which is located in your FrameworkSDK\bin folder. Once you create the wrapper you can set a reference to it and access the COM component through it.

But it’s much easier to simply set a reference to the COM component from within Visual Studio .NET, as described earlier, because Visual Studio .NET automatically creates the wrapper and also sets the reference to it.

If you created the reference to the Excel object library just described, here’s how you can see the wrapper that was created for you. Click on the Project menu, and choose Show All Files. Then in the Project Explorer, expand the bin folder. You’ll see a file named Interop.Excel.dll and, if you have other COM component references, possibly other files beginning with Interop.

Now that you have the wrapper and the reference, you’re ready to go to work by instantiating an Excel Application object and using its methods. That’s what we’ll describe in the code walkthrough. Figure 12-1 shows the sample lunch menu, ready to be exported to Excel, where the calorie average of the foods listed will be calculated.

click to expand
Figure 12-1: With Automation, your Visual Basic .NET application can use Microsoft Office programs such as Microsoft Excel to do work on its behalf.

Code Walkthrough

We’ll show how to use Excel to perform calculations and how to use Word to do spelling checking.

Automating Excel

This code creates and fills a DataSet from an XML document and then binds it to a DataGrid. Then it exports the contents of the DataSet to an Excel spreadsheet and runs an Excel function that calculates the average of values in a column. First, we’ll load the grid with items on a fictitious lunch menu contained in an XML document.

Private Sub btnGetMenu_Click(...
    
    dsMenu = New DataSet()
    dsMenu.ReadXml(Application.StartupPath & "\..\menu.xml")
    With grdMenu
        .CaptionText = "Today's Menu"
        .DataSource = dsMenu.Tables(0)
    End With
    
    btnExport.Enabled = True
End Sub

Now we want to export the contents of the DataGrid to Excel, and then run a simple Average function to determine the calorie average for all the foods. The Excel object model has a hierarchy from Application to Workbook to Worksheet. We first instantiate an Application object, which means we’re creating an instance of Excel. Then we create a workbook and a get a reference to the first worksheet within that workbook. Note the use of CType, which is needed because excelBook.Worksheets(1) returns an Object, which then has to be cast as a worksheet object.

We make the Excel instance visible so that the user can see the data being entered into the spreadsheet. If we don’t do that, all the operations will proceed, but Excel will operate invisibly. (We’ll see an example of that in the Word demo.)

Private Sub btnExport_Click(...
    Dim excelApp As New Excel.Application()
    Dim excelBook As Excel.Workbook = excelApp.Workbooks.Add
    Dim excelWorksheet As Excel.Worksheet = _
        CType(excelBook.Worksheets(1), Excel.Worksheet)
    excelApp.Visible = True

Now we use properties and methods of the Worksheet object to set the column headers and to format the cells and columns. We use the Excel Range object to refer to the set of cells we’re about to work on, and then we apply changes as needed.

Now we’re ready to export the data from our DataGrid. We’ll use a counter that starts at 2 so that the data will be placed beginning in row 2 of the worksheet, following the column headers. Then we’ll loop through the Rows collection of the DataSet and write the data in each row to the cells in Excel.

    Dim i As Integer = 2
    With excelWorksheet
        Dim dr As DataRow
        For Each dr In dsMenu.Tables(0).Rows
            .Range("A" & i.ToString).Value = dr("Item")
            .Range("B" & i.ToString).Value = dr("Price")
            .Range("C" & i.ToString).Value = dr("Calories")
            i += 1
        Next
    End With

We want to format the bottom row differently from the data, so we select row 8 from column A to column C and make it Red and Bold. We also set the first cell in the range to Average Calories. Note that A1 in this case doesn’t mean the first cell of the Worksheet, but the first cell of the range in question, A8 to C8.

    rng = excelWorksheet.Range("A8:C8")
 
    With rng
        .Font.Color = RGB(255, 0, 0)
        .Font.Bold = True
        .Range("A1").Value = "Average Calories"
    End With

Finally, we select the cell that will display the calorie average, and then set the Average formula using the FormulaR1C1 property, which uses numbers to label both rows and columns and makes it easy for us to refer to the current row minus 6 through the current row minus 2. Then we AutoFit all columns and go to the first cell of the sheet. The user is provided with the average calorie count of the items in his lunch menu.

    With excelWorksheet
        .Range("C8").Select()
        excelApp.ActiveCell.FormulaR1C1 = "=AVERAGE(R[-6]C:R[-2]C)"
        .UsedRange.Columns.AutoFit()
        .Range("A1").Select()
    End With
End Sub

Automating Word

If you want to add spelling checking capability to any application, one way to do it is by using the Microsoft Word spelling checker. This example shows how to do that.

The btnSpellCheck_Click event procedure lets the user run the Word spelling checker against whatever text is in the RichTextBox control (loaded earlier with the btnBrowseWord_Click procedure). First, we instantiate the Word Application object. Notice that, unlike the Excel example, we don’t make Word visible, because it doesn’t need to be seen to do a spelling check. We want to let the user either check the entire document or choose a portion of the document to be checked, so we test the length of the RichTextBox selected text. If it’s greater than zero, we check only that portion; otherwise, we check the entire document.

Private Sub btnSpellCheck_Click(...
    Dim wordApp As New Word.Application()
    Dim hasNoSpellingErrors As Boolean
    Dim portionChecked As String

    If Len(rtfDocument.SelectedText) > 0 Then
        portionChecked = "text"
        hasNoSpellingErrors = _
            wordApp.CheckSpelling(rtfDocument.SelectedText)
    Else
        portionChecked = "document"
        hasNoSpellingErrors = wordApp.CheckSpelling(rtfDocument.Text )
    End If

    Dim spellCheckResponse As String
    If hasNoSpellingErrors Then
        spellCheckResponse = "Congratulations, your " & portionChecked & _
            " has no spelling errors."
    Else
        spellCheckResponse = "Your " & portionChecked & _
            " has spelling errors."
    End If

    MessageBox.Show(spellCheckResponse, "Spelling Check Results", _
        MessageBoxButtons.OK, MessageBoxIcon.Information)
End Sub

This example simply displays a dialog box telling you whether your spelling check succeeded or failed. However, you could enhance this functionality to create a more feature-rich application that mimics the Word spelling checker, including such features as allowing the use of custom dictionaries, and more.

Conclusion

When Visual Basic .NET doesn’t do all that you need, you can enlist the services of other applications, such as those in the Microsoft Office suite. Once you set a reference to the object library you need, Visual Studio .NET automatically creates the necessary runtime callable wrapper to let your .NET application interoperate with the COM component. There’s almost no limit to what you can do with such interoperation if you’re willing to learn the object models of the applications you choose to automate.