Microsoft Excel provides a great number of crunching capabilities, as well as its powerful charting feature. Learn how to incorporate an Excel chart into your .NET application.
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.
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.
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.
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.