Extend your .NET application with Excel

by Tony Patton | Aug 02, 2005 5:53:00 PM

Tags: Microsoft Office, Middleware, .NET, Tony Patton, VBA, Microsoft Excel, COM, Microsoft .NET, .NET Newsletter

Takeaway: A .NET application may be greatly enhanced by providing additional functional via Excel integration. This includes the number-crunching capabilities inherent in Excel, as well as charting and much more. Learn more about Excel and .NET integration.

In a recent column, we explored the process of integrating Microsoft Word with the .NET Framework. There are numerous integration possibilities as the full power of the Microsoft Office Suite is available. In this article, we examine another scenario involving Microsoft Excel.

VBA persists

We must point out that the Microsoft Office product suite utilizes the Visual Basic for Applications (VBA), so a little knowledge of the Component Object Model (COM) object is helpful. However, the .NET COM interop feature makes it easy to utilize COM objects within a .NET application. Let's begin with an overview of the Excel object model.

Excel object model

Microsoft Excel provides literally hundreds of objects for programmatically working within its environment. The whole set is beyond the scope of this article, so let's examine a few objects to get up and running. Here are four common objects:

Application: Represents the entire Excel application. It exposes a great deal of information about the running application, the options applied to that instance, and the current user objects open within the instance.
Workbook: A single Excel workbook that may contain one or more worksheets.
Worksheet: An individual Excel worksheet. Most of the properties, methods, and events of the Worksheet object are identical or similar to members provided by the Application and/or Workbook classes.
Range: A range of cells within a worksheet. A Range object represents a cell, a row, a column, a selection of cells containing one or more blocks of cells, or even a group of cells on multiple sheets.

The object model begins with the Application class at the top, since it is the starting point for accessing Excel. Before you can begin working with the Excel object model via .NET COM interop, you must make it available to your project.

Using Microsoft Excel

The Microsoft Excel Object Library must be made available to your .NET project. If using Visual Studio .NET, a reference may be added to a project via the Project | Add Reference menu selection. The COM tab within the Add Reference window provides access to COM libraries installed on the system. Excel is listed as Microsoft.Excel, and the specific name will depend on the Excel version installed. I have Excel 2003 on my system, so the COM library is listed as Microsoft.Excel 11.0 Object Library. In addition, two namespaces are necessary:

Microsoft.Office.Interop.Excel: Allows you to work with Excel objects via .NET interop.
System.Runtime.InteropServices: Includes the COMException class, allowing you to properly handle COM-related exceptions.

The code snippet in Listing A loads and opens an Excel file from the local file system. (Listing B contains the equivalent VB.NET code.) Here are a few notes on the code:

The Open method of the Workbooks object (accessed via the Application object) allows you to access an existing Excel file. Note: As a C# developer, the Type.Missing value is necessary since the Excel VBA object model accepts numerous optional parameters. The Type.Missing value allows you to pass nothing to the parameter, but still recognize it. VB.NET supports optional parameters to this approach.
The Application object is set to visible and the Workbook object is activated to make it show on the screen.
The catch blocks handle specific COM-related exceptions as well as general exceptions.

The previous code does not encompass a complete application, but demonstrates how Excel may be used in both C# and VB.NET. Let's take it a step further by manipulating the data within a worksheet.

The VB.NET code in Listing C creates a new Excel sheet, inserts numbers, and performs a calculation. Only the code for a button is included. (Listing D contains the equivalent C# code.) Here are a few notes on the code:

A new Excel Workbook is created with the Add method of the Workbook's property of the Application object. This creates a workbook with one blank worksheet.
The current sheet is accessed via the ActiveSheet property of the Worksheet object.
A Range object is used to work with individual cells. The cell is accessed via its location on the sheet. For example, a title for the sheet (Techrepublic.com) is inserted at the first cell (A1 = column A and row 1). The Value property of the Range object is used to populate the cell.
The Formula property of the Range object allows you to assign a formula to a cell or group of cells. In this example, the total of the second column of values is displayed.
The Calculate method of the Range object processes the formula.

You may notice some differences between the C# and VB.NET versions. The get_Range method of the Worksheet class is used to instantiate the Range object in C#. In addition, the get_Range method features a second optional parameter so the Type.Missing value is used. Finally, the ActiveSheet object must be cast to the Worksheet class to use it. You should be prepared for such differences when using C# to utilize VBA COM objects.

The environment

Some readers have questioned the vulnerability of a user's system when working with Excel, but the examples in this article are built as Windows Form-based applications. The environment envisioned is an internal application, so security should not be as big an issue as if it is opened to the world. Using Excel via ASP.NET provides its own set of issues, and it is beyond the scope of this article.

Extending functionality

Integrating Excel with a .NET application allows you to easily and quickly provide powerful functionality within an application. The calculation and presentation features of Excel offer a wealth of options.

TechRepublic's free .NET newsletter, delivered each Wednesday, contains useful tips and coding examples on topics such as Web services, ASP.NET, ADO.NET, and Visual Studio .NET. Automatically sign up today!

12 comment(s)