Section 5.7.Use Excel as a Component in .NET

5.7. Use Excel as a Component in .NET

Another way for Excel to interact with the .NET world is to program with Excel objects directly in VB.NET. In this case, Excel becomes a component for use in a .NET applicationthe reverse of the case just shown.

Using Excel as a component in a .NET application is handy when you want to present application output using the Excel interfaceas a spreadsheet or chart, for instance.

---

Note: Excel works as a component, too. It's a big one, though, and you need to be carefulwhen using it from .NET applications.

---

5.7.1. How to do it

To create a .NET application that uses Excel as a component:

1. Create a new Windows application project in Visual Studio .NET.

2. Choose Project Add Reference. Visual Studio .NET displays the Add Reference dialog box, click the COM tab. Visual Studio .NET displays the COM objects registered on your system as shown in Figure 5-10.

   Figure 5-10. Adding a reference to the Microsoft Excel object library

   
   
   

3. Select the Microsoft Excel 11.0 Object Library and click Select, then OK to add the reference to your project. Visual Studio .NET automatically references the PIA for the Excel object library if it is installed on your system.

4. If the PIA is not installed, Visual Studio .NET creates a new interop assembly and adds it to your project (this is not what you wantthe PIA is much more reliable). To make sure you are using the PIA, check the Name and Path properties of the Excel reference. They should appear as shown in Figure 5-11.

   : Look here

   Use the COM page of the References dialog to add a reference to the Excel PIAs. Even though those are actually .NET assemblies, you won't find them on the .NET page.

   
   

   Figure 5-11. Check the reference properties to make sure you are using the Excel PIA

   
   
   

5. In code, create an instance of the Excel Application object and use that object's member to perform tasks in Excel.

For example, the following code starts Excel and creates a new workbook:

        ' .NET Windows form code        Dim WithEvents As Microsoft.Office.Interop.Excel.Application            Private Sub cmdStartExcel_Click(ByVal sender As System.Object, _            ByVal e As System.EventArgs) Handles cmdStartExcel.Click            ' If not initialized, create a new instance of the object.            If IsNothing(m_xl) Then _                m_xl = New Microsoft.Office.Interop.Excel.Application            ' Make Excel visible.            m_xl.Visible = True            ' Create a new workbook.            m_xl.Workbooks.Add(  )        End Sub

The m_xl variable is declared WithEvents so VB.NET can respond to events that occur in the application. The cmdStartExcel_Click procedure initializes the Excel Application object if it was not already initialized, and then it calls the Workbook collection's Add method to create a new workbook. It is important to note that if Visible is not set to True, all this happens invisibly in the background and, while that is kind of interesting, it is not usually what you want.

Use the following code to close the Excel application when you are done:

        Private Sub cmdQuitExcel_Click(ByVal sender As System.Object, _            ByVal e As System.EventArgs) Handles cmdQuitExcel.Click            ' Close the Excel application.            m_xl.Quit(  )            ' Set object reference to Nothing.            m_xl = Nothing            ' Force .NET to perform garbage collection.            System.GC.Collect(  )        End Sub

5.7.2. How it works

The preceding code illustrates a couple precautions you should take when working with Excel from .NET.

First, you should set the object variable to Nothing after you call Quit . Calling Quit doesn't set m_xl to Nothing, which can keep the application alive, running in the background.

Second, force .NET to get rid of unused resources by calling System.GC.Collect . .NET manages memory using a process called garbage collection and you need to force it to take out the garbage after you've thrown away Excel. Otherwise, .NET will leave Excel in memory until resources run low and automatic garbage collection takes place (I think of this as waiting for my son to take out the garbage, rather than doing it myself). You don't want to call GC.Collect frequently because it is an expensive operation, but it is great when you want to free very large objects like Excel.