Click here to Skip to main content
11,409,138 members (36,462 online)
Click here to Skip to main content

A free "Export to Excel" C# class, using OpenXML

, 17 Dec 2013 CPOL
Rate this:
Please Sign up or sign in to vote.
How to easily add an "export to Excel" feature to your app

Introduction

It's amazing that even now, in 2013, 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 a .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.

Depending on whether your data is stored in a DataSet, DataTable, or List<>, you simply need to call one of these three functions, and tell them what (Excel) file name you want to write to.

public static bool CreateExcelDocument<T>(List<T> list, string ExcelFilename
public static bool CreateExcelDocument(DataTable dt, string ExcelFilename
public static bool CreateExcelDocument(DataSet ds, string ExcelFilename)

Here's a simple example.

We create a DataSet, fill it with some data (just add your own CreateSampleData function), then call the CreateExcelFile.CreateExcelDocument function, passing it the DataSet and a filename:

// Step 1: Create a DataSet, and put some sample data in it
DataSet ds = CreateSampleData();

// Step 2: Create the Excel .xlsx file
try
{
    CreateExcelFile.CreateExcelDocument(ds, "C:\\Sample.xlsx");
}
catch (Exception ex)
{ 
    MessageBox.Show("Couldn't create Excel file.\r\nException: " + ex.Message);
    return;
}   

And that's all you have to do. The CreateExcelDocument function will create a "real" Excel file for you.

For example, if you had a created a DataSet containing three DataTables called:

  • Drivers
  • Vehicles,
  • Vehicle Owners,

..then here's what your Excel file would look like. The class would create one worksheet per DataTable, and each worksheet would contain the data from that DataTable.

Sample spreadsheet

The full source code for this C# class is freely downloadable here (click on the "Browse code" link in the left-hand panel), or you can download this full demo, plus a VB.NET version of the class, from 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 (via the "Browse code" link in CodeProject's left-hand panel), 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.cs code, so that it now reads:

#define INCLUDE_WEB_FUNCTIONS   

Now, you need to add a Reference to the System.Web .NET 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

Mike Gledhill
Software Developer
Switzerland Switzerland
Programming since the age of 15, with experience in C# and iPad software development.
Working as a software developer for a financial company in Zurich, Switzerland.

More developer tips'n'trip at my website:
http://www.MikesKnowledgeBase.com

Comments and Discussions

 
QuestionLanguage dependent column names PinprofessionalMårten Sörliden8-Apr-15 3:40 
QuestionAddHeader needs filename='" + filename + "'" to work in Google Chrome PinmemberMember 111198127-Apr-15 6:13 
QuestionWhat about this DLL? PinprofessionalDietmar Schoder28-Feb-15 4:43 
AnswerRe: What about this DLL? PinmemberMichael Gledhill18-Mar-15 2:30 
GeneralRe: What about this DLL? PinmemberPaul C. Rhodes18-Mar-15 2:43 
QuestionIn Webmatrix I can't get it to work PinprofessionalMarcelloCarrabba10-Feb-15 6:24 
Questionxls extension error PinmemberMember 1106205626-Jan-15 5:51 
AnswerRe: xls extension error PinmemberMike Gledhill29-Jan-15 23:23 
QuestionError related to Hexadecimal characters Pinmembermagupta776-Jan-15 0:49 
AnswerRe: Error related to Hexadecimal characters PinmemberMike Gledhill29-Jan-15 23:24 
QuestionDoesn't work with List<string> PinmemberMember 1010915510-Dec-14 6:26 
QuestionExport to excel with image PinmemberMember 112844648-Dec-14 20:27 
AnswerRe: Export to excel with image PinmemberMike Gledhill29-Jan-15 23:28 
QuestionAnother ataboy - and a quick question on DateTime values Pinmembertlmrogers8-Dec-14 11:59 
SuggestionThanks for code [modified] PinmemberMember 1076949218-Nov-14 7:38 
QuestionMVC Extensions... Pinmemberal_todd4-Nov-14 23:38 
AnswerGreate! Pinmemberplamboss15-Oct-14 0:14 
QuestionAwesome PinmemberMember 1018986822-Sep-14 2:33 
QuestionNot working in IE 10 PinmemberNaweed Ahmed15-Sep-14 11:17 
QuestionColumns Width PinmemberJonas Dev1-Sep-14 12:38 
AnswerRe: Columns Width Pinmemberleiyangge19-Sep-14 6:21 
AnswerRe: Columns Width PinmemberMike Gledhill26-Sep-14 3:10 
QuestionDealing with Int Numbers that are larger than the IEEE757 Standard and gets rounded to 15 digits in Excel [modified] PinmemberEdwin Jones13-Aug-14 3:03 
AnswerExport to Excel C# class PinmemberRindra RAZAFINJATOVO26-Jul-14 23:15 
GeneralRe: Export to Excel C# class PinmemberMikeGledhill26-Jul-14 23:44 
BugException occuring PinmemberGoneBump21-Jul-14 21:23 
GeneralRe: Exception occuring PinmemberMike Gledhill23-Jul-14 5:29 
GeneralRe: Exception occuring PinmemberGoneBump17-Oct-14 22:10 
QuestionGreat solution PinmemberMember 100353712-Jul-14 10:21 
QuestionSend an email of this copy PinmemberVivekh RF27-Jun-14 6:05 
AnswerRe: Send an email of this copy PinmemberMike Gledhill29-Jun-14 22:12 
QuestionMessage Removed PinmemberMember 1090441625-Jun-14 17:56 
QuestionMy vote of 5 PinprofessionalYogesh Kumar Tyagi23-Jun-14 3:27 
QuestionFile not downloading PinmemberMember 1089709420-Jun-14 9:28 
AnswerRe: File not downloading PinmemberMikeGledhill22-Jun-14 8:39 
GeneralRe: File not downloading PinmemberMember 1089709423-Jun-14 3:48 
QuestionMy vote of only 5... PinmemberHannes_Malan19-Jun-14 20:29 
AnswerRe: My vote of only 5... PinmemberMikeGledhill19-Jun-14 21:08 
QuestionError with .xlsx PinmemberMember 1089286818-Jun-14 9:31 
AnswerRe: Error with .xlsx PinmemberMikeGledhill19-Jun-14 5:54 
GeneralRe: Error with .xlsx Pinmemberswbo1029-Oct-14 4:50 
AnswerRe: Error with .xlsx Pinmemberswbo10210-Oct-14 5:29 
QuestionWorks fine, but one small problem PinprofessionalJohnny J.5-Jun-14 0:15 
AnswerRe: Works fine, but one small problem PinmemberMikeGledhill5-Jun-14 0:23 
GeneralRe: Works fine, but one small problem PinprofessionalJohnny J.5-Jun-14 0:29 
GeneralThank you very much! PinmemberMember 108622603-Jun-14 11:16 
GeneralRe: Thank you very much! PinmemberMikeGledhill3-Jun-14 21:42 
GeneralRe: Thank you very much! PinmemberMrsMoyaLangley4-Jun-14 5:27 
QuestionFormat of Document PinmemberMember 1050496630-May-14 8:37 
AnswerRe: Format of Document PinmemberMike Gledhill30-May-14 10: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 | Terms of Use | Mobile
Web03 | 2.8.150414.5 | Last Updated 17 Dec 2013
Article Copyright 2013 by Mike Gledhill
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid