Click here to Skip to main content
15,867,288 members
Articles / Programming Languages / C#
Article

Excel Charts in C# Applications

Rate me:
Please Sign up or sign in to vote.
4.30/5 (16 votes)
1 Aug 2007CPOL4 min read 265.6K   11K   114   28
This article shows you how to use Excel charts in C# applications

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:

C#
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.

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

The Excel surface chart has four subtypes:

  1. xlSurface: Displays a standard surface chart
  2. xlSurfaceWireFrame: Displays a surface chart without colors
  3. xlSurfaceTopView: Displays the a surface chart viewed from above
  4. 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:

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
United States United States
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
QuestionCan you use this with C# 2003? Pin
Member 105154839-Jan-14 22:22
Member 105154839-Jan-14 22:22 
GeneralMy vote of 5 Pin
freegoods8-Dec-12 22:11
freegoods8-Dec-12 22:11 
QuestionSystem.BadImageFormatException was unhandled Pin
ayyangar1-Aug-12 13:26
ayyangar1-Aug-12 13:26 
AnswerRe: System.BadImageFormatException was unhandled Pin
freegoods8-Dec-12 22:06
freegoods8-Dec-12 22:06 
QuestionUpdate for Office Excel 2010, VS 2010 Pin
asalzber9-May-12 17:38
asalzber9-May-12 17:38 
GeneralMy vote of 5 Pin
Manoj Kumar Choubey20-Feb-12 20:36
professionalManoj Kumar Choubey20-Feb-12 20:36 
QuestionHow to Make Bold in Part of an Excel Cell - Need Eargent help!!!!!!! Pin
supuna2u12-Sep-10 17:13
supuna2u12-Sep-10 17:13 
Generalscrollbar Pin
eng.jeblak4-Aug-10 2:03
eng.jeblak4-Aug-10 2:03 
Generalsee Pin
axinkumar18-Feb-10 22:06
axinkumar18-Feb-10 22:06 
GeneralInterOp Excel Charts in ASP.NET Application Pin
Karthi.Mtech18-Dec-09 2:38
Karthi.Mtech18-Dec-09 2:38 
GeneralGood Stuff! Pin
ebrewste14-Sep-09 13:58
ebrewste14-Sep-09 13:58 
GeneralWith Open XML Format SDK 2.0 you can generate charts in open format, this is really great tool Pin
Maciej Gren21-Aug-09 8:18
Maciej Gren21-Aug-09 8:18 
QuestionReference COM: Excell, Office, VBIDE Pin
ColdSun1-Jul-09 18:45
ColdSun1-Jul-09 18:45 
GeneralRe: Reference COM: Excell, Office, VBIDE Pin
zoonny14-Mar-12 16:33
zoonny14-Mar-12 16:33 
GeneralThanks a lot! Pin
tenyears11-May-09 17:59
tenyears11-May-09 17:59 
QuestionHow not to displau any values on Category axis (Horizontal axis) Pin
vblearn26-Aug-08 10:31
vblearn26-Aug-08 10:31 
GeneralUsing VS2003, .Net Framework 2 Pin
MeDev18-Feb-08 3:01
MeDev18-Feb-08 3:01 
GeneralRe: Using VS2003, .Net Framework 2 Pin
tenyears11-May-09 18:10
tenyears11-May-09 18:10 
GeneralExcel Reference Pin
Drati18-Dec-07 1:36
Drati18-Dec-07 1:36 
GeneralRe: Excel Reference Pin
MeDev18-Feb-08 2:57
MeDev18-Feb-08 2:57 
GeneralRe: Excel Reference Pin
gs_virdi18-Apr-10 22:54
gs_virdi18-Apr-10 22:54 
insted...... try using "Microsoft Excel 5.0 Object Library".
GeneralNice Pin
Pooya Musavi19-Oct-07 8:04
Pooya Musavi19-Oct-07 8:04 
QuestionI have an exception running your Application Pin
Scent of a Knight19-Aug-07 22:23
Scent of a Knight19-Aug-07 22:23 
AnswerRe: I have an exception running your Application Pin
talosDk4-Oct-07 7:03
talosDk4-Oct-07 7:03 
GeneralYour other article Pin
Axel Rietschin20-Jul-07 17:32
professionalAxel Rietschin20-Jul-07 17:32 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.