Excel Charts in C# Applications

Download source - 443.26 KB

Introduction

If you want to create advanced graphics and charts in C# applications, you can create them using C# and GDI+, or by using third-party graphics and chart packages. However, you can achieve this goal by taking advantage of Microsoft Excel's built-in chart features directly in C# applications.

Microsoft Visual Studio .NET makes it possible for you to create rich Microsoft Excel-based applications based on the C# Framework. You can take advantage of all of the functionality provided by Excel's large object models in C# applications. In this article, I will show you how to use the surface charts in your C# applications.

Background

In order for Excel to be used in C# applications, we need to resolve the issue of interoperation between C# and Excel. Excel application can be regarded as a COM server, so the basis of interoperation between C# and Excel applications is COM Interop. The .NET Framework provides good support for interaction with COM components. To use Excel COM components in a C# project, you simply need to add this COM component to reference. This can be done by right clicking the project in the Solution Explorer and selecting Add Reference. Click the COM tab and select the appropriate type of Object library depending on the version of Microsoft Office you are using:

Office 97: Microsoft Excel 8.0 Object Library
Office 2000: Microsoft Excel 9.0 Object Library
Office XP: Microsoft Excel 10.0 Object Library
Office 2003: Microsoft Excel 11.0 Object Library

After this step, you should find that references have been added for the Office Core and Excel as well. In my case, I built my project using Visual Studio .NET 2005 and Microsoft Office XP. So my reference to Excel is Microsoft Excel 10.0 Object Library.

Next, I will use the surface chart as an example to demonstrate how to involve Excel in a C# application. In this example, I will create a Windows Form application with two buttons on Form1. One button is used to start Plot, and the other to quit Excel and close Form1. Clicking the Plot button will launch Excel, add a new workbook to the collection of workbooks, get Active Sheet, and put data into Excel cells, and draw the surface chart.

Excel surface charts display multiple-series data on a surface using the category axis, value axis, and a third axis that displays the series name. Surface charts are used to show the optimum combination of category and series data.

Using the Code

The project is implemented in a single Form1 class. In this class, we first create data using a 1D and 2D array and assign them to the Excel worksheet. This data must be structured in a certain format. The categories (used as the X axis) are in the first column. The second row represents the series names (used as the Y axis). Finally, the data values are formed by a 2D data array.

As long as the data is assigned to the Excel worksheet, you can create a surface chart by selecting the surface chart type:

xlChart.ChartType = XlChartType.xlSurface;

The rest of the code in the Form1 class is used to customize the axes, add axis labels and a title, and remove the legend from the chart.

This project produces the output of Figure 1. It can be seen that the chart is created by ranges of data values, which is evident by the fact that the colors vary with the values along the vertical (the Z) axis. The number of colors on the surface chart is based on the major unit of the Z axis.

Figure 1: Excel surface chart created in a C# application.

The Excel surface chart has four subtypes:

xlSurface: Displays a standard surface chart
xlSurfaceWireFrame: Displays a surface chart without colors
xlSurfaceTopView: Displays the a surface chart viewed from above
xlSurfaceTopViewWireFrame: Displays a surface chart without colors, viewed from above

You can select a different subtype of surface chart from the above four options according to your application requirements.

I should point out here that Excel surface charts have some limits: they are not true X-Y-Z charts. X and Y are not treated as numeric data, and must consist of regularly defined, evenly spaced categories. Exactly one Z value is needed for each X-Y pair. Excel surface charts are drawn by connecting Z values at X-Y nodes with straight lines and planar sections. A saddle point is not accurately drawn if it occurs between the X-Y nodes. Color-filled surface charts in Excel do not allow transparency, meaning that you cannot visualize the hidden parts of the surface.

This project is from the examples of Chapter 9 of my new book "Practical C# Charts and Graphics", where you can find more advanced chart and graphics programming for real-world .NET applications. For more information, please visit my Web site.

About the Author

Dr. Jack Xu has a Ph.D in theoretical physics. He has over 15 years programming experience in Basic, Fortran, C, C++, Matlab, and C#, specializing in numerical computation methods, algorithms, physical modeling, computer-aided design (CAD) development, graphics user interface, and 3D graphics. Currently, he is responsible for developing commercial CAD tools based on Microsoft .NET Framework.

Please also read my other articles: