Mike
Tips, tricks and solutions for software developers.
 
Latest News (July 2017)
  • The C# class now writes the data to the Excel file using the OpenXmlWriter library, making it much more memory-friendly.
  • My CodeProject article has been read over 232,000 times.

Export data from a DataSet into a real Excel 2007 file

By Mike Gledhill

Ever wanted to add an "Export to Excel" function to your ASP.Net, WinForms or WPF application ?

This free C# and VB.Net library lets you export a DataTable or DataSet of data into a "real" Excel 2007 .xlsx file, using one line of code.

CreateExcelFile.CreateExcelDocument(myDataSet, myExcelPathFilename);

...or if you're using ASP.Net...

CreateExcelFile.CreateExcelDocument(myDataSet, myExcelFilename, Response);

You can download the full source code using the links below, so you can extend it to add Excel formatting, etc.

It uses the OpenXML libraries, rather than Microsoft's Visual Studio Tools for Office (VSTO) libraries, so you don't need to have Excel installed on your server.

The CreateExcelFile library

All of the code you'll need is contained in one class, CreateExcelFile, which I've saved in the file CreateExcelFile.cs (or CreateExcelFile.vb for the VB.Net version).

To use this class, you simply call its CreateExcelDocument function, passing it a DataSet variable (which contains the values you want writing into Excel cells), and the path+filename of the Excel file you wish to create.

Each DataTable within your DataSet will be saved into it's own Excel worksheet.

// 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;
}

Demo program

The attached Visual Studio 2010 C# and VB.Net WinForms demo shows how easy it is to use the CreateExcelFile library.

It consists of a simple dialog with one button on it (shown below).

When you click on the button, it'll create a DataSet and fill it with some sample data, and prompt you for a location to save our example Excel file to. It will then call the CreateExcelDocument function to create an Excel 2007 .xlsx file containing our DataSet's data, then open the file in Excel for you to admire.

Screenshot from the C# demo Screenshot from the C# demo

Notice how our demo application created a DataSet containing three DataTables in it, called Drivers, Vehicles & Vehicle Owners. The CreateExcelFile library then created an Excel Worksheet for each of these DataTable names, and wrote each DataTable's data into it.

Using the "Export to Excel" library in your own application

The CreateExcelFile library has two dependences:

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

I have included a copy of these two files in the source code's lib folder, but ideally, you should download the most recent version from the Microsoft website.

To use the library in your own code, simply add these two files, and the CreateExcelFile.cs file to your Visual Studio project, then add the two .dll files to the References section of your project.

Also, you need to make sure that these two files have the "Copy Local" field set to true. Without this, when you deploy your app, it won't copy the .dlls to your bin directory, and IIS will complain that it can't find the OpenXML libraries.

Then just call the static CreateExcelDocument function, as shown above.

CreateExcelFile.CreateExcelDocument(ds, excelFilename);

I have deliberately left this library's source code available for you to view and alter, as desired.

Exporting from a List<> or DataTable

With a little help from a discussion on CodeGuru, I added two extra functions, so you can now call the CreateExcelDocument function in three ways:

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

..so you should find it really easy to export your data to Excel from any of these formats.

Using the library in an ASP.Net application

November 2013: With help from my fellow developers, I have made the C# library even easier to use in an ASP.Net application. The ExportToExcel class now lets you create an Excel file without writing to a temporary file first. You just need to pass the "Response" as a parameter.

// In this example, I have a defined a List of my Employee objects.
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);
}

By default, this functionality is disabled in the C# file, so that non-ASP.Net developers don't get build errors when attempting to use the library. To enable the functionality, you need to manually make two small changes:

First, uncomment the first line of code in the CreateExcelFile.cs file, so it looks like this:

#define INCLUDE_WEB_FUNCTIONS

Then add a new Reference, and select the System.Web library.

Add Reference..

And that's it. You can now use the three new web-friendly functions for exporting to Excel.

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)

Cool, hey ?

You're welcome to use and adapt this code as you like, but - please - if you like it, leave me a comment below.

Setting the Excel column widths

If you wanted to calculate the column widths, based on the data in each column, it's not particularly easy.

Here's an article which contains C# code to do it (I haven't tested this code though):
http://social.msdn.microsoft.com/Forums/office/en-US/28aae308-55cb-479f-9b58-d1797ed46a73/solution-how-to-autofit-excel-content?forum=oxmlsdk

Downloads

C# VB.Net Filename Description
ExportToExcel.zip The full Excel demo, showing how to use the CreateExcelFile library code, and the Open XML libary files.
 

CreateExcelFile.cs / .vb Just the C#/VB.Net source code file for the CreateExcelFile class.

ExportToExcelExe.zip The executable (.exe) file of the Demo, if you just want to see what the application looks like.
Note: This zip file contains a copy of the .exe file created by the Visual Studio project which you can download using the link above. This .zip file is safe, but your anti-virus software might complain about it or block it, as it contains an executable file.

Last updated: July 2017

Support my charity

I have also written a "Pro" version of the C# class, which adds the following features:

  • add styling to the results, so you can see examples of how to add alignment, colors, number formatting, borders, etc
  • writes date values as numbers, and uses formatting to display them as dates (in the user's own language)

Export to Excel, Pro

I am happy to send a copy of the C# version of this library to anyone who donates $10 or more to the following charities, and sends me an email copy of the receipt.

(I don't want your money.. but I do want your support !)

Safe water transforms lives.

(Clicking on these links will open a new browser window, redirected to the charity's own web site.)

License

Copyright (C) 2015 MikesKnowledgeBase

Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.

Release history

January 2016

  • Released the 'Pro' version of the library, adding formatting, colors, etc.

February 2015

  • When exporting to an HTTPResponse, it no longer uses "Response.End();".
    New function to prevent hexadecimal characters from crashing the export.

March 2014

  • Now writes the Excel data using the OpenXmlWriter classes.
    This is a much more memory efficient method, and will prevent issues when writing large Excel files.

November 2013

  • Added new ASP.Net functions to (just) the C# code, to let you write Excel directly to the page's HttpResponse, rather than writing to a temporary file first.

February 2013

  • Added a VB.Net version of the ExportToExcel classes.

References

Importing data from Excel into SQL Server
http://mikesknowledgebase.com/pages/SQLServer/ImportExcelIntoSQLServer.htm

"Introduction to Open XML SDK 2.0"
http://msdn.microsoft.com/en-us/library/bb448854.aspx

"How to create an Excel 2007 file, from scratch:"
http://blogs.msdn.com/b/chrisquon/archive/2009/07/22/creating-an-excel-spreadsheet-from-scratch-using-openxml.aspx

"Writing data into excel document using openxml"
http://www.prowareness.com/blog/?p=476

"Convert a List<T> into a DataTable"
http://www.codeguru.com/forum/showthread.php?t=450171

Comments

blog comments powered by Disqus