Export data from a DataSet into a real Excel 2007 file
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, myExcelFilename);
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.
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.
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.
Calling from an ASP.Net application
Several months after writing this library, I realised I'd missed a trick here.
Why should this library limit itself to just exporting data from DataSets ? With a little help from a discussion on CodeGuru, I've added two extra functions, so you can now call the CreateExcelDocument function in three ways:
public static bool CreateExcelDocument
public static bool CreateExcelDocument(DataTable dt, string xlsxFilePath)
public static bool CreateExcelDocument(DataSet ds, string xlsxFilePath)
So, in my ASP.Net application, here's how I can call the CreateExcelFile library using the new CreateExcelDocument function which takes a List<> parameter. If it's successful, it'll then open the Excel file which was created.
// In this example, I have a defined a List of my Employee objects.
class Employee;
List<Employee> listOfEmployees = new List<Employee>();
...
// The following code gets run when I click on my "Export to Excel" button.
protected void btnExportToExcel_Click(object sender, EventArgs e)
{
string filename = "\\\\MikesServer\\ExcelFiles\\Employees.xlsx";
if (CreateExcelFile.CreateExcelDocument(listOfEmployees, filename))
{
// We successfully managed to export to an Excel file.
// Now, get the ASP.Net application to open this Excel file, ready for the user to view.
Response.ClearContent();
FileStream fs1 = new FileStream(filename, FileMode.Open, FileAccess.Read);
byte[] data1 = new byte[fs1.Length];
fs1.Read(data1, 0, data1.Length);
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}", filename));
Response.BinaryWrite(data1);
Response.End();
}
}
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.
ASP.Net permissions
A couple of users have questioned how to use this library in an ASP.Net app. You can't tell an ASP.Net app to write to the user's local C: drive or "My Documents" folder, so should they get their ASP.Net code to just pass it a filename, without any path ?
CreateExcelFile.CreateExcelDocument(ds, "MikesExcelFile.xlsx"); // This doesn't work!!
Well, no. This won't work.
The library would attempt to create an .xlsx file anywhere on your web server machine (eg c:\windows\system32\inetsrv) and would be refused access.
When you deploy your ASP.Net application to an IIS server, you need to have a directory somewhere where the CreateExcelFile library can write a file to. Usually, I add an ExcelFiles folder to my ASP.Net application, and when I deploy my ASP.Net application to my web server, I make sure that the "Everyone" user has read-write access to this directory.
string XlsFilename = "SomeExcelFile.xlsx";
// On my web server, my web application contains an ExcelFiles directory, which "Everyone" can read-write to.
string XlsPathAndFilename = server.MapPath("~/ExcelFiles/");
XlsPathAndFilename += XlsFilename;
if (CreateExcelFile.CreateExcelDocument(dt, XlsPathAndFilename))
{
// In Internet Explorer, the following lines will make a "Save to.." prompt appear
response.ClearContent();
response.Clear();
response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
response.AddHeader("Content-Disposition", "attachment; filename=" + XlsPathAndFilename + ";");
response.TransmitFile(XlsPathAndFilename);
response.Flush();
response.End();
}
The alternative solution is to just have a directory somewhere on your network (perhaps not on your web server) which the ASP.Net user can write to.
string XlsFilename = "SomeExcelFile.xlsx";
string XlsPathAndFilename = server.MapPath("//MikesOtherServer/ExcelFolder");
XlsPathAndFilename += XlsFilename;
Once again, you must make sure that whichever user is being used to run your ASP.Net application has read-write access to this directory. (Depending on your IIS configuration, this might be via an Application Pool user.) Again, the easiest way to do this is to give "Everyone" read-write access to this directory.
Setting the Excel column widths
By default, the CreateExcelFile class just creates a valid Excel 2007 with no formatting, colors, borders, etc.
If you wanted to change this code to set the widths of the columns in your Excel file, below are the changes you'd need to make.
First, you need to add a few lines to the CreateParts function (shown in red):
private static void CreateParts(DataSet ds, SpreadsheetDocument spreadsheet)
{
// Loop through each of the DataTables in our DataSet, and create a new Excel Worksheet for each.
uint worksheetNumber = 1;
foreach (DataTable dt in ds.Tables)
{
// For each worksheet you want to create
string workSheetID = "rId" + worksheetNumber.ToString();
string worksheetName = dt.TableName;
WorksheetPart newWorksheetPart = spreadsheet.WorkbookPart.AddNewPart<WorksheetPart>();
newWorksheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet();
// If you want to define the Column Widths for a Worksheet, you need to do this *before* appending the SheetData
// http://social.msdn.microsoft.com/Forums/en-US/oxmlsdk/thread/1d93eca8-2949-4d12-8dd9-15cc24128b10/
//
// The following example sets the column width, for all columns where we have data, to 20 pixels.
int columnWidthSize = 20;
Columns columns = new Columns();
for (int colInx = 0; colInx < dt.Columns.Count; colInx++)
{
Column column = CustomColumnWidth(colInx, columnWidthSize);
columns.Append(column);
}
newWorksheetPart.Worksheet.Append(columns);
// create sheet data
newWorksheetPart.Worksheet.AppendChild(new DocumentFormat.OpenXml.Spreadsheet.SheetData());
// save worksheet
WriteDataTableToExcelWorksheet(dt, newWorksheetPart);
newWorksheetPart.Worksheet.Save();
Then, add the following function just below the WriteDataTableToExcelWorksheet function:
private static Column CustomColumnWidth(int columnIndex, double columnWidth)
{
// This creates a Column variable for a zero-based column-index (eg 0 = Excel Column A), with a particular column width.
Column column = new Column();
column.Min = (UInt32)columnIndex + 1;
column.Max = (UInt32)columnIndex + 1;
column.Width = columnWidth;
column.CustomWidth = true;
return column;
}
If you wanted to calculate the column widths, based on the data in each column, it's not easy.
Have a read of this article:
http://polymathprogrammer.com/2010/01/11/custom-column-widths-in-excel-open-xml/
Downloads
Last updated: February 2013
Liked it ? Like it !
If you found this article useful, please leave a Comment below, or click on the buttons below to recommend it to fellow developers.
Thanks !
Release history
February 2013
- Added a VB.Net version of the ExportToExcel classes.
January 2013 - Excel 2010 update
- Updated the C# and VB.Net classes to cope with nullable column types.
- Also, it no longer writes a "0" to the Excel file when a numeric column has a null value in it.
- There was also an issue with OLEDB being unable to open the .xlsx files.
August 2012
- Fixed a couple of problems with exporting numeric values to an Excel 2007 file. Strangely, the OpenXML libraries would create "corrupted" files when directly writing lots of numeric values to the file. My solution has been to convert all values in the numeric columns into a double, and then to write the value to the Excel file.
- There is also an issue when writing lots of rows (30,000+) to the Excel file. The OpenXML libraries simply seem to hang when there's too much data to write. I haven't implemented a solution to this problem yet.
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