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
List<> data is stored, and what (Excel) filename you want to write to.
DataSet ds = CreateSampleData();
string excelFilename = "C:\\Sample.xlsx";
catch (Exception ex)
MessageBox.Show("Couldn't create Excel file.\r\nException: " + ex.Message);
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();
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".
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.
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:
Now, you need to add a Reference to the
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
List<Employee> listOfEmployees = new List<Employee>();
protected void btnExportToExcel_Click(object sender, EventArgs e)
CreateExcelFile.CreateExcelDocument(listOfEmployees, "Employees.xlsx", Response);
And that's it. A real Excel file, in one line of code.
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.