How to insert a Chart in Excel through C#

In the previous tutorial, we saw :

• How to read from an Microsoft Excel file in C#

• How to write to an Microsoft Excel file in C#
  
  

This is part of a series of tutorials on Programming tips using C# and Microsoft Excel. This tutorial will be very useful for developers working on Desktop applications in .NET where we need to do lot of report generation in Microsoft Excel. In this tutorial, we will how to insert a chart in Excel through C#.

At the end of this tutorial, we will see how to plot a graph from the data in Excel sheet, and insert a chart in Excel sheet through C#. Finally, we will discuss little about formatting the chart.

Steps

There are a number of free 3rd party libraries available for handling Micosoft Excel file in C#. However, here we use COM library which is easier compared to other libraries. Also, it is already available in your system if you have Microsoft Office installed.

Add a reference to the Excel object Library

Step 1: First, you need to add a reference to the Excel object Library

Create a new C# Windows Forms Project. Open the Solution Explorer pane, Right click on References and click on Add Reference. This will open a new window “Add Reference”. Click on the COM tab, and select “Microsoft Excel Object Library”.

Step 2 : You need to add the reference in the code, in order to use all the methods of Excel object library. This can be done using the following :

Step 3 : Instantiate an Excel object

Step 4 : Sometimes, Excel display some warning messages if there any compatibility issues. You can disable all the warning messages using the following code.

Set the Visibility

Step 5 : By default, the visibility of the Excel application is set to FALSE. At least during the development stages, lot of developers would prefer to debug the code and while seeing the Excel file. You can set the visibility of the Excel file using the code :

When you are debugging, the visibility is set to true, so you can see the Excel file when the application is running. When in release mode, the visibility is set to false.

Open the Excel File

Step 6 : In order to open the Excel file, you can use the following code:

Now this is important, lot of developers make this mistake. When you open the Excel file, you might have noticed, there are more than 1 Excel sheet. So, the application would open some Excel sheet. So you need to first get the reference to the concerned Excel sheet.

For our experiment, let us say the Excel sheet name is “ExperimentSheet”. You can get the reference to the sheet using the code:

However, you can also you use the following code. However, the best practice is to obtain the reference using the sheet name as mentioned in the above step.

Step 7 : In Step 6, we just obtained the reference to the sheet. However, this sheet might not be open(as we can have multiple sheets in Excel). In order to open the Excel sheet, using the following code

Example

These steps remain same for all the C# programs dealing with Microsoft Excel. Let us assume that we have collected some data related to population for some major cities around the world in 2001 and 2011. The objective of the tutorial is to draw a bar a 2-D bar graph from the population data of different cities in the world in 2001 and 2011.

The figure shows the Excel chart we would like to generate programmatically.

Add an new Chart

Step 8 : So far, so good ? Let us draw an empty chart with default settings related to the size and position of the chart. We can change all these parameters later.

You can also specify the chart type, size as an argument of AddChart() method. However, I personally prefer starting with an empty chart.

Set the chart type

Step 9 : We need a 2-D bar graph to display the population growth . Microsoft Excel supports different chart types like pie chart, Histogram, 3-D bar graph etc. All the chart type supported by Microsoft Excel is listed here. Please have a look.

The chart type can be selected using the following code

Set the Data Source

Step 10 : Now, we need to select the data from the 2-D bar chart. In our example, the data is present in Columns A, B and C, we can select the address range and use this range of address as the data source.

Here is the code for setting the data source:

Set the Axes

Step 11 : Now we have the desired chart. The only thing pending is setting the axes and placement of the chart in the Excel sheet. We can set the different parameters like title, x-axis and y-axis using the following code.

Chart Placement

Step 12 : You can infact skip this step, if you think the chart is not overlapping with the data cells. So, the idea is to look for the empty cells and place the chart starting from that cell.

The excel chart placement needs 4 parameters:

• Width
• Height
• Left placement
• Top placement

As we have only 3 columns of data, we can place the chart starting from Column D.

Save Excel

Step 12 : The last step is to close the Excel file and the Excel application. The following code closes the Excel file and releases all the memory associated with the Excel application.

Download

You can download the complete source code from here and and the Excel file from here.

I hope, you find this tutorial on reading data from Microsoft excel in C# useful. Please leave a comment if you have any doubts or contact me with your comments and feedback. I like reading your feedback.