Click here to Skip to main content
Click here to Skip to main content
Go to top

Excel Charts in C# Applications

, 1 Aug 2007
Rate this:
Please Sign up or sign in to vote.
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:

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:

  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)

Share

About the Author

Jack J. H. Xu

United States United States
No Biography provided

Comments and Discussions

 
QuestionCan you use this with C# 2003? PinmemberMember 105154839-Jan-14 22:22 
GeneralMy vote of 5 Pinmemberfreegoods8-Dec-12 22:11 
QuestionSystem.BadImageFormatException was unhandled Pinmemberayyangar1-Aug-12 13:26 
AnswerRe: System.BadImageFormatException was unhandled Pinmemberfreegoods8-Dec-12 22:06 
QuestionUpdate for Office Excel 2010, VS 2010 Pinmemberasalzber9-May-12 17:38 
GeneralMy vote of 5 Pinmembermanoj kumar choubey20-Feb-12 20:36 
QuestionHow to Make Bold in Part of an Excel Cell - Need Eargent help!!!!!!! Pinmembersupuna2u12-Sep-10 17:13 
Generalscrollbar Pinmembereng.jeblak4-Aug-10 2:03 
Generalsee Pinmemberaxinkumar18-Feb-10 22:06 
GeneralInterOp Excel Charts in ASP.NET Application PinmemberKarthi.CSC18-Dec-09 2:38 
GeneralGood Stuff! Pinmemberebrewste14-Sep-09 13:58 
GeneralWith Open XML Format SDK 2.0 you can generate charts in open format, this is really great tool PinmemberMaciej Gren21-Aug-09 8:18 
QuestionReference COM: Excell, Office, VBIDE PinmemberColdSun1-Jul-09 18:45 
GeneralRe: Reference COM: Excell, Office, VBIDE [modified] Pinmemberzoonny14-Mar-12 16:33 
GeneralThanks a lot! Pinmembertenyears11-May-09 17:59 
QuestionHow not to displau any values on Category axis (Horizontal axis) Pinmembervblearn26-Aug-08 10:31 
GeneralUsing VS2003, .Net Framework 2 PinmemberMeDev18-Feb-08 3:01 
GeneralRe: Using VS2003, .Net Framework 2 Pinmembertenyears11-May-09 18:10 
GeneralExcel Reference PinmemberDrati18-Dec-07 1:36 
GeneralRe: Excel Reference PinmemberMeDev18-Feb-08 2:57 
GeneralRe: Excel Reference Pinmembervirdigs18-Apr-10 22:54 
GeneralNice PinmemberPooya Musavi19-Oct-07 8:04 
QuestionI have an exception running your Application PinmemberKnight's Scent19-Aug-07 22:23 
AnswerRe: I have an exception running your Application PinmembertalosDk4-Oct-07 7:03 
GeneralYour other article Pinmemberaxelriet20-Jul-07 17:32 

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

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

| Advertise | Privacy | Mobile
Web03 | 2.8.140916.1 | Last Updated 1 Aug 2007
Article Copyright 2007 by Jack J. H. Xu
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid