Click here to Skip to main content
12,076,053 members (33,727 online)
Click here to Skip to main content
Add your own
alternative version

Tagged as

Stats

72K views
3.2K downloads
79 bookmarked
Posted

C# Export data to Excel, using OpenXML libraries

, 17 Jun 2014 CPOL
Rate this:
Please Sign up or sign in to vote.
Export your data to a real Excel file, using one line of code!

Introduction

It's amazing that even now, in 2014, there are so many developers still asking for help on how to write C# and VB.Net code, to export their data to Excel.

Even worse, a lot of them will stumble on articles suggesting that they should write their data to a comma-separated file, but to give the file an .xls extension.

So, today, I'm going to walkthrough how to use my C# "Export to Excel" class, which you can add to your C# WinForms / WPF / ASP.Net application, using one line of code. Simply tell the class where your DataSet, DataTable, or List<> data is stored, and what (Excel) filename you want to write to.

// Step 1: Create a DataSet, and put some sample data in it
DataSet ds = CreateSampleData();
// Step 2: Create the Excel .xlsx file
try
{
    string excelFilename = "C:\\Sample.xlsx";
    CreateExcelFile.CreateExcelDocument(ds, excelFilename);
}
catch (Exception ex)
{ 
    MessageBox.Show("Couldn't create Excel file.\r\nException: " + ex.Message);
    return;
}

This article is based on some code I have documented on my blog, MikesKnowledgeBase.

Adding the library to your application

The C# code above shows how easy it is to call the CreateExcelFile class.

DataSet ds = CreateSampleData();
CreateExcelFile.CreateExcelDocument(ds, "C:\\Sample.xlsx");

However, to use this library, you'll need to add two files from the free Microsoft OpenXML SDK:

  • DocumentFormat.OpenXml.dll From the free Microsoft Open XML SDK library
  • WindowsBase.dll From the Microsoft .NET Framework library

Add these two .dlls to your project's References section, and remember to set them to "Copy Local".

Copy Local

Then, just download the CreateExcelFile.cs file, and add it to your application.

And that's it.

Regardless of if your data is stored in a List<>, DataTable or DataSet, you can export it to a "real" Office 2007 Excel .xlsx file using that one line of code.

And because it is created using the OpenXML library, you can run this code on machines which don't have Excel installed.

ASP.NET

For ASP.NET developers, I've added three extra functions.

public static bool CreateExcelDocument<T>(List<T> list, string filename, System.Web.HttpResponse Response)
public static bool CreateExcelDocument(DataTable dt, string filename, System.Web.HttpResponse Response)
public static bool CreateExcelDocument(DataSet ds, string filename, System.Web.HttpResponse Response) 

Rather than creating the Excel file in a temporary directory, then having to load in the file and output it to the webpage's HttpResponse, you can get the library to write directly to the HttpResponse.

However, by default, this functionality is disabled (to prevent build issues for the non ASP.NET developers). To enable these three functions, you need to make two changes.

First, uncomment the top line of the CreateExcelFile code, so that it now reads:

#define INCLUDE_WEB_FUNCTIONS  

Now, you need to add a Reference to the System.Web library:

Adding a reference

Once you've done these two steps, the CreateExcelFile library is ready to go.

For example, in this example, my ASP.Net C# code has a list of Employee records, stored in a List<Employeee>. I add an "Export to Excel" button to my webpage, and when the user clicks on it, I just need one simple call to the CreateExcelFile class.

// In this example, I have a defined a List of my Employee objects.<br />class Employee;
List<Employee> listOfEmployees = new List<Employee>();
...

// The following ASP.NET code gets run when I click on my "Export to Excel" button.
protected void btnExportToExcel_Click(object sender, EventArgs e)
{
    // It doesn't get much easier than this...
    CreateExcelFile.CreateExcelDocument(listOfEmployees, "Employees.xlsx", Response);
}  

And that's it. A real Excel file, in one line of code.

Going forward

You'll notice that this library is excellent for one job - writing plain, boring data to an Excel file. I haven't attempted to add any classes to add formatting, colors, pivot tables or anything else.

However, this class is an excellent way to get started (without paying for third-party software to create the Excel file for you), and if you want to take this further, you'll soon find that Googling will easily find you extra source code to add on top of this.

For example, if you wanted to add a background color to some of the cells in the Excel file, simply Google "open xml background color" and you'll have many articles showing you how to do this.

The reason I wrote this article is that I found that it was very hard to find a free, easy to use C# library which actually created the OpenXML Excel file in the first place.

License

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

Share

About the Author

Michael Gledhill
Software Developer Partner Reinsurance
Switzerland Switzerland
I'm a software developer, working with Visual Studio and XCode, working for a financial company.

Author of the PartnerReSearch iPad app, winner of a "Business Insurance Innovation Award" in 2013, and a TechAward2014 "Innovation of the year" award in 2014.

Living near Zurich, Switzerland, with my Swiss-English family.

You may also be interested in...

Comments and Discussions

 
PraiseVery good Pin
Member 1183618910-Dec-15 1:18
memberMember 1183618910-Dec-15 1:18 
GeneralRe: Very good Pin
Michael Gledhill10-Dec-15 2:00
memberMichael Gledhill10-Dec-15 2:00 
QuestionIt's work great Pin
VitorTrindade27-Oct-15 22:08
memberVitorTrindade27-Oct-15 22:08 
QuestionVery useful. How to fill Excel with EF entities? Pin
a.casnati13-Oct-15 1:23
membera.casnati13-Oct-15 1:23 
AnswerRe: Very useful. How to fill Excel with EF entities? Pin
Michael Gledhill14-Oct-15 3:15
memberMichael Gledhill14-Oct-15 3:15 
GeneralMy vote of 5 Pin
DVL Patel23-Aug-15 20:53
professionalDVL Patel23-Aug-15 20:53 
GeneralThanks Pin
pjhunter12-May-15 12:23
memberpjhunter12-May-15 12:23 
GeneralRe: Thanks Pin
Michael Gledhill12-May-15 21:55
memberMichael Gledhill12-May-15 21:55 
QuestionGreat solution Pin
BluEiS25-Feb-15 23:57
memberBluEiS25-Feb-15 23:57 
AnswerRe: Great solution Pin
Michael Gledhill25-Feb-15 23:59
memberMichael Gledhill25-Feb-15 23:59 
QuestionGreaaaat Thaaanks Pin
Dev_Fady...24-Feb-15 21:56
memberDev_Fady...24-Feb-15 21:56 
QuestionThank you Pin
Member 1141299530-Jan-15 6:16
memberMember 1141299530-Jan-15 6:16 
AnswerRe: Thank you Pin
Mike Gledhill30-Jan-15 6:41
memberMike Gledhill30-Jan-15 6:41 
QuestionVery Helpful but what a bout 3 sheets in one Excel file Pin
Dev_Fady...13-Dec-14 23:47
memberDev_Fady...13-Dec-14 23:47 
AnswerRe: Very Helpful but what a bout 3 sheets in one Excel file Pin
Michael Gledhill14-Dec-14 23:49
memberMichael Gledhill14-Dec-14 23:49 
QuestionRe: Very Helpful but what a bout 3 sheets in one Excel file Pin
Dev_Fady...15-Dec-14 1:50
memberDev_Fady...15-Dec-14 1:50 
GeneralMy vote of 5 Pin
canrut4-Dec-14 15:40
membercanrut4-Dec-14 15:40 
SuggestionAdded wrapper for ASP.net while using VB.net Pin
Frank van Bokhoven4-Nov-14 22:55
memberFrank van Bokhoven4-Nov-14 22:55 
Questioni want to put background color on Excel Sheet Header Column Pin
Member 101945591-Sep-14 4:29
memberMember 101945591-Sep-14 4:29 
AnswerRe: i want to put background color on Excel Sheet Header Column Pin
Mike Gledhill2-Sep-14 4:03
memberMike Gledhill2-Sep-14 4:03 
GeneralRe: i want to put background color on Excel Sheet Header Column Pin
Member 101945598-Sep-14 20:18
memberMember 101945598-Sep-14 20:18 
QuestionThis may be interesting here: Pin
Dietmar Schoder29-Jul-14 11:31
professionalDietmar Schoder29-Jul-14 11:31 
QuestionOpen XML SDK Productivity Tool Pin
fatman4518-Jun-14 14:18
memberfatman4518-Jun-14 14:18 
QuestionHow it differs from EPPlus.dll Pin
manishchourasia200017-Jun-14 20:06
membermanishchourasia200017-Jun-14 20:06 
AnswerRe: How it differs from EPPlus.dll Pin
MikeGledhill17-Jun-14 23:51
memberMikeGledhill17-Jun-14 23:51 
GeneralRe: How it differs from EPPlus.dll Pin
manishchourasia200018-Jun-14 18:52
membermanishchourasia200018-Jun-14 18:52 
QuestionDownload CreateExcelFile.cs Pin
jpostma12-Jun-14 7:27
memberjpostma12-Jun-14 7:27 
AnswerRe: Download CreateExcelFile.cs Pin
MikeGledhill2-Jun-14 7:50
memberMikeGledhill2-Jun-14 7:50 
SuggestionAnother way to export data to Excel Pin
Member 860411026-Feb-14 20:52
memberMember 860411026-Feb-14 20:52 
SuggestionDetecting Numeric Cells Pin
Steve Hemmings10-Feb-14 0:28
memberSteve Hemmings10-Feb-14 0:28 
GeneralGreat starter Pin
Oramo Blue27-Dec-13 0:15
memberOramo Blue27-Dec-13 0:15 
GeneralRe: Great starter Pin
Mike Gledhill29-Dec-13 22:20
memberMike Gledhill29-Dec-13 22:20 
QuestionMultiple Sheets Pin
Patrick Cooper26-Dec-13 11:43
memberPatrick Cooper26-Dec-13 11:43 
AnswerRe: Multiple Sheets Pin
Mike Gledhill29-Dec-13 22:09
memberMike Gledhill29-Dec-13 22:09 
GeneralRe: Multiple Sheets Pin
Patrick Cooper30-Dec-13 6:17
memberPatrick Cooper30-Dec-13 6:17 
GeneralNice one Pin
Shan-Bala10-Dec-13 17:31
memberShan-Bala10-Dec-13 17:31 
QuestionThis looks great BUT... Pin
kjmcsd9-Dec-13 7:07
memberkjmcsd9-Dec-13 7:07 
QuestionSize of the dll Pin
Dietmar Schoder9-Dec-13 7:02
professionalDietmar Schoder9-Dec-13 7:02 
AnswerRe: Size of the dll Pin
Mike Gledhill9-Dec-13 21:59
memberMike Gledhill9-Dec-13 21:59 
GeneralRe: Size of the dll Pin
Dietmar Schoder9-Dec-13 22:10
professionalDietmar Schoder9-Dec-13 22:10 
QuestionWindowsBase.dll Pin
Dietmar Schoder9-Dec-13 2:14
professionalDietmar Schoder9-Dec-13 2:14 
AnswerRe: WindowsBase.dll Pin
Mike Gledhill9-Dec-13 6:06
memberMike Gledhill9-Dec-13 6:06 

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.

| Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.160208.1 | Last Updated 17 Jun 2014
Article Copyright 2013 by Michael Gledhill
Everything else Copyright © CodeProject, 1999-2016
Layout: fixed | fluid