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.
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:
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.
Application object is set to visible and the Workbook object is activated
to make it show on the screen.
catch blocks handle specific COM-related exceptions as well as general
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:
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
current sheet is accessed via the ActiveSheet
property of the Worksheet object.
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.
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.
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.
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.
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!