Click here to Skip to main content
Click here to Skip to main content

Generate Excel Chart from Data in .NET Applications

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

Introduction

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
workbook.SaveCopyAs("F:\\Downloads\\Output.xlsx");

Figure 1: Excel Output of Microsoft Office Automation

Conclusion

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!

License

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

About the Author

LazyCoder0
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

 
QuestionNot an article... PinmvpDave Kreskowiak3-Dec-13 5:50 
AnswerRe: Not an article... PinmemberLazyCoder03-Dec-13 6:11 
AnswerRe: Not an article... PinmemberLazyCoder03-Dec-13 7:16 

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
Web01 | 2.8.140721.1 | Last Updated 3 Dec 2013
Article Copyright 2013 by LazyCoder0
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid