Click here to Skip to main content
14,362,554 members

Generate Excel Chart from Data in .NET Applications

Rate this:
4.33 (3 votes)
Please Sign up or sign in to vote.
4.33 (3 votes)
3 Dec 2013CPOL
This tip describes how to generate Excel charts from data in .NET applications using Office Automation.


Charts are used to present the data in graphical representation. When the data is represented graphically, it becomes easy to understand and manipulate. Users can create various types of charts in an Excel file using Office Automation. Some people find it difficult to work with Office Automation, so I’ll share an alternative method to create a pie chart with sample data as well.

Inserting Sample Data in a Worksheet

The following code inserts headings and sample data in Excel worksheet using the Office Automation. It also sets the font size and makes the text bold, and adds some countries along with number of users sample data for a pie chart.

//Add headings in A1 and B1
sheet.Cells[1, 1] = "Country";
sheet.Cells[1, 2] = "Users";

//Set the text bold and font size
sheet.Cells[1, 1].Font.Bold = true;
sheet.Cells[1, 2].Font.Bold = true;
sheet.Cells[1, 1].Font.Size = 13;
sheet.Cells[1, 2].Font.Size = 13;

//Add data from A2 till B6
sheet.Cells[2, 1] = "England";
sheet.Cells[2, 2] = 10000;
sheet.Cells[3, 1] = "USA";
sheet.Cells[3, 2] = 8000;
sheet.Cells[4, 1] = "China";
sheet.Cells[4, 2] = 12000;
sheet.Cells[5, 1] = "Russia";
sheet.Cells[5, 2] = 9000;
sheet.Cells[6, 1] = "India";
sheet.Cells[6, 2] = 7000;  

Creating a Pie Chart using Office Automation

In the following lines of code, I create an Excel chart. I set its chart type as Pie Chart. I also set its title properties and source data range. Finally, I save the workbook.

//Chart reference
Microsoft.Office.Tools.Excel.Chart usersChart;

//Add a Pie Chart
usersChart = sheet.Controls.AddChart(0, 105, 330, 200, "CountryUsers");
usersChart.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlPie;

//Set chart title
usersChart.HasTitle = true;
usersChart.ChartTitle.Text = "Users";

//Gets the cells that define the data to be charted.
Microsoft.Office.Interop.Excel.Range chartRange = sheet.get_Range("A2", "B6");
usersChart.SetSourceData(chartRange, missing);

//Access the Active workbook from Vsto sheet
Microsoft.Office.Interop.Excel.Workbook workbook = sheet.Application.ActiveWorkbook;

//Save the copy of workbook as Output.xlsx

Image 1

Figure 1: Excel Output of Microsoft Office Automation


We have finally created an Excel file with data and a pie chart as shown in above figure. Office Automation (VSTO) has its own stability and performance issues so if you don’t want to use Office Automation on server side to generate Excel charts, then you may also use Open XML SDK from Microsoft which doesn’t require Microsoft Office to be installed on server. And if you find using Open XML SDK complicated, then you may also try some commercial Excel library that addresses all concerns of Office Automation and Open XML SDK.

Good luck and happy coding!


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


About the Author

Instructor / Trainer
United States United States
I'm a developer with experience in document management solutions using Office Automation (VSTO), Open XML SDK and also commercial libraries like Aspose.

Comments and Discussions

GeneralMy vote of 2 Pin
RAVI TEJA NANI21-Oct-14 20:29
memberRAVI TEJA NANI21-Oct-14 20:29 
QuestionNot an article... Pin
Dave Kreskowiak3-Dec-13 6:50
mveDave Kreskowiak3-Dec-13 6:50 
Your topic simply isn't broad enough to justify an article. This is a tip/trick at best, unless you want to put a lot more work into this an expand the topic.

AnswerRe: Not an article... Pin
LazyCoder03-Dec-13 7:11
memberLazyCoder03-Dec-13 7:11 
AnswerRe: Not an article... Pin
LazyCoder03-Dec-13 8:16
memberLazyCoder03-Dec-13 8:16 

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.

Posted 3 Dec 2013


14 bookmarked