In the previous tutorial, we saw :
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 :
1
|
using Excel = Microsoft.Office.Interop.Excel;
|
Step 3 : Instantiate an Excel object
1
|
Excel.Application oXL= new Excel.Application();
|
Step 4 : Sometimes, Excel display some warning messages if there any compatibility issues. You can disable all the warning messages using the following code.
1
|
oXL.DisplayAlerts = false;
|
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 :
1
2
3
4
5
|
#if DEBUG
oXL.Visible = true;
#else
oXL.Visible = false;
#endif
|
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:
1
2
3
4
5
|
//Open the Example Excel File
String inputFile = @"D:\Experiment\InsertChart.xlsx";
Excel.Workbook oWB = oXL.Workbooks.Open(inputFile);
|
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:
1
2
3
|
String SheetName = "ExperimentSheet";
Excel._Worksheet oSheet = oWB.Sheets[SheetName];
|
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.
1
2
3
|
oSheet = oWB.Sheets[1]; // Gives the reference to the first sheet
oSheet = oWB.ActiveSheet; // Gives the reference to the current opened sheet
|
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
1
|
oSheet.Activate();
|
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.
1
2
3
|
//Insert an Empty Chart
Excel.Shape chart1 = oSheet.Shapes.AddChart(Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
|
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
1
2
3
|
//Insert a basic 2-D bar graph
chart1.Chart.ChartType = Excel.XlChartType.xlColumnClustered;
|
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:
1
2
3
4
5
6
7
|
//Set the Data Source for the chart
Excel.Range Rng;
Rng = oSheet.get_Range("A2", "C10");
chart1.Chart.SetSourceData(Rng, Type.Missing);
|
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.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
|
//Set the Chart Title
chart1.Chart.HasTitle = true;
chart1.Chart.ChartTitle.Text = "Population Growth";
//Set the y-axis
var yaxis = (Excel.Axis)chart1.Chart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary);
yaxis.HasTitle = true;
yaxis.AxisTitle.Text = "Growth";
//Vertical Allignment of y-axis title
yaxis.AxisTitle.Orientation = Excel.XlOrientation.xlVertical;
//Set the X-axis
var xaxis = (Excel.Axis)chart1.Chart.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary);
xaxis.HasTitle = true;
xaxis.AxisTitle.Text = "Population";
yaxis.AxisTitle.Orientation = Excel.XlOrientation.xlHorizontal;
//Set the Legend
chart1.Chart.SeriesCollection(1).Name = (String)oSheet.get_Range("B1").Value2;
chart1.Chart.SeriesCollection(2).Name = (String)oSheet.get_Range("C1").Value2;
|
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.
1
2
3
4
5
6
7
8
9
|
//Set the Size of the Chart
chart1.Width = 350;
chart1.Height = 350;
chart1.Left = (float)oSheet.get_Range("D1").Left;
chart1.Top = (float)oSheet.get_Range("D3").Top;
|
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.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
|
String ReportFile = @"D:\Excel\Output.xls";
oWB.SaveAs(ReportFile, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault,
Type.Missing, Type.Missing,
false,
false,
Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
Type.Missing,
Type.Missing,
Type.Missing,
Type.Missing,
Type.Missing);
oXL.Quit();
Marshal.ReleaseComObject(oSheet);
Marshal.ReleaseComObject(oWB);
Marshal.ReleaseComObject(oXL);
oSheet = null;
oWB = null;
oXL = null;
GC.GetTotalMemory(false);
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.GetTotalMemory(true);
|
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.