Most development projects leave little room for change;
thus, finding development shortcuts can rescue valuable time. One way to
efficiently use development time is to take advantage of existing products as
opposed to developing everything from scratch.
Microsoft Office is one product that you should leverage. In
recent columns, we’ve covered .NET integration with Outlook, Word, and Excel. Today
we revisit Excel by utilizing its charting features to present data in a .NET
Windows Forms application.
Using Microsoft Excel
To utilize Excel in your application, the Microsoft Excel
Object Library must be made available. 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
These namespaces simplify the process of utilizing Excel
objects within a .NET application.
Excel chart objects
You must work with standard Excel worksheets to utilize one
or more charts. One or more cells on worksheets are used as the chart’s data
source. For this reason, we must be familiar with the Excel objects as well as
the related chart objects, which include the following:
- Application: Represents the entire
- Workbook: A single Excel workbook
that may contain one or more worksheets.
- Worksheet: An individual Excel
- Range: A range of cells within a
- Chart: A single Excel chart. Its ChartType property designates the type of chart to be
- Charts: A collection of Excel
Sheet objects (the parent of both Chart and Worksheet objects) containing references
to each of the charts contained in the active workbook. In addition, it
contains an Add method for adding a new chart to the workbook.
The type of chart displayed is an important detail. For this
reason, let’s take a closer look at the ChartType
property of the Excel.Chart object. The Excel.Chart.ChartType property has numerous options for
chart presentation. The following list contains a subset of the complete list
- xlArea: An area chart is used.
- xlAreaStacked: A stacked area chart is used.
- xlBarClustered: A clustered bar chart is used.
- xlBubble: Chart data is displayed in
- xlColumnStacked: Chart data is displayed in
- xlLine: A line chart is used.
- xlPie: A standard pie chart is used.
- xlXYScatter: A scatter chart is used.
With the basic object model covered, let’s turn our
attention to coding. Our sample creates an Excel chart using data inserted into
the worksheet via code. It is a Windows Forms application built with Visual
Studio .NET with all code included. The crux of the Excel automation is
contained in the button’s click event.
Listing A contains the C# code, while Listing B
contains the equivalent VB.NET code.
A few details of the code are worth noting:
are assigned to the two namespaces used to work with Excel. This allows us
to type the shorter alias when using the namespace within the code rather
than typing (and retyping) the long namespace path.
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.
new Chart object is created via the ChartObjects
object which is assigned to the current worksheet. The ChartObjects’
Add method accepts parameters for specifying where the chart is placed on
new Range object is created to be used as chart’s data source. The range
is the column of cells beginning at B3 and ending with B8. This covers the
numbers. The Range object is assigned to the Chart via the SetSourceData method of the Chart object.
COM object is released in the finally block. This ensures objects are
Don’t forget to clean up
A common issue encountered when working with Microsoft
Office COM objects via .NET is the Office application not properly closing once
automation is complete. When Visual Studio .NET uses a COM object via .NET COM interop, it automatically creates a Runtime Callable
The RCW marshals calls between the .NET application and the
COM object. The RCW keeps a reference count on the COM object. The COM object
will not quit if all references have not been released. The Microsoft Support
Web site defines the following criteria to ensure COM objects are properly
each object as a new variable.
- Use System.Runtime.InteropServices.Marshal.ReleaseComObject
when you have finished using an object. This
decrements the RCW’s reference count.
release the reference to the variable, set the variable equal to Nothing or Null.
the Quit method of the Office application object to tell the server to
Our sample code follows all of these guidelines except for
the call to the Quit method. We don’t call the Quit method in the code because
this shuts down the Office application. Instead, the user initiates this call
by closing the application opened (Excel).
A picture is worth a thousand words
It is well-documented that a picture can communicate a
concept better than words. The Excel object model and .NET COM interop simplifies the integration no matter what
programming language you choose.
This column only touches on the power of using
the Excel Chart object. You can enhance the chart’s presentation with colors,
text, and so forth. In addition, an outside data source like a SQL Server table
could be used to fuel the chart.