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!