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
    COM-related exceptions.

These namespaces simplify the process of utilizing Excel
objects within a .NET application.

Weekly .NET tips in your inbox

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!

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
    Excel application.
  • Workbook: A single Excel workbook
    that may contain one or more worksheets.
  • Worksheet: An individual Excel
    worksheet.
  • Range: A range of cells within a
    worksheet.
  • Chart: A single Excel chart. Its ChartType property designates the type of chart to be
    created.
  • 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.

Excel.Chart.ChartType property

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
of choices:

  • 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
    bubble format.
  • xlColumnStacked: Chart data is displayed in
    stacked columns.
  • xlLine: A line chart is used.
  • xlPie: A standard pie chart is used.
  • xlXYScatter: A scatter chart is used.

Code sample

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:

  • Aliases
    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.
  • 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.
  • A
    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
    the worksheet.
  • A
    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.
  • The
    COM object is released in the finally block. This ensures objects are
    properly disposed.

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
Wrapper (RCW).

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
disposed:

  • Declare
    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.
  • To
    release the reference to the variable, set the variable equal to Nothing or Null.
  • Use
    the Quit method of the Office application object to tell the server to
    shut down.

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.