C# Export data to Excel, using OpenXML libraries

• Download CreateExcelFile.zip - 10Kb

Introduction

I first published this CodeProject article, describing my "Export to Excel" C# library, back in 2014, and it's still hugely popular today.   

So, in this article, I'll bring it right up to date, and show you how to use the library in a regular C# app, an ASP.NET app, and an ASP.NET Core 2+ app.

In a nutshell, my library lets you add an "export to Excel" function to your C# app, just by using some free Microsoft libraries, my class, and one line of code. Simply tell my library where your DataSet, DataTable, or List<> data is stored, and what (Excel) filename you want to write to.

Here's how you would use it in a C# WinForms app:

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

Limitations

Don't get me wrong - this C# library is absolutely bare-bones.  If you're looking for a library where you can export things like images, diagrams, and pivot tables to an Excel file, then this isn't the right place for you.   You can either go and buy a third-party library, or take the code which I've provided, and use it as a basis to create your own customized library.

As you'll see, this C# library simply takes a chunk of data stored in a DataSet, DataTable, or List<>, and outputs it to an Excel .xlsx file.  Oh, and it will create one worksheet per DataTable in your DataSet. 

A few nice things about this library though:

1. It will look at your types of data, and attempt to set the correct Excel cell styles.  For example, if you have a DateTime column, it will write your data value to the Excel file as a number, and apply a style to show it as a date.  This is how Excel stores such data itself.
2. Because the Excel file is created using Microsoft's free OpenXML library, you can run this code on machines which don't have Excel installed.
3. My library creates the file using OpenXmlWriter.  This has a huge advantage that, when you're creating massive Excel files, it won't wait until it has created all of the elements for your Excel file before writing it all out to the file.  Without this functionality, you would be receiving out-of-memory errors when trying to create large Excel files.
4. On each worksheet, I set the header text to a different style.  You can look at the GenerateStyleSheet() function in my library to see how I've done this, and feel free to customize its apparence, fonts and colors, as desired.

Having said all of that, this C# library is golden.  I've spent the last 12 years working in financial companies in Zurich, and, without exception, no matter how beautiful and responsive you make your application, the users always want the possibility to play around with the data using Excel.   And now, you can slip in this functionality without breaking a sweat.

1. Adding the library to your ASP.NET Core 2+ application

Let's start with ASP.NET Core, as this is where most of you should/will be using.  There are three very easy steps to adding my library to your application.

First, download the CreateExcelFile.cs file from the top of this article, and add it to your project.  At the top of this file, you'll see three commented-out #define statements.   You need to just uncomment the second one, to say you're using ASP.NET Core:

// #define USING_ASP_NET                //  Uncomment this line, if you're using the old ASP.NET
#define USING_ASP_NET_CORE              //  Uncomment this line, if you're using ASP.NET Core 2 or later
// #define DATA_CONTAINS_FORMULAE

Next, you need to open "NuGet Package Manager" in Visual Studio and include the "DocumentFormat.OpenXml" nuget package.  This is Microsoft's library for creating Excel files.

And finally, you need to get your code to call the StreamExcelDocument function.  I actually demonstrate how to do this from an ASP.Net Core Web API in this CodeProject article.

[HttpGet("ExportToExcel")]
public async Task<IActionResult> ExportEmployeesToExcel()
{
    try
    {
        List<Employee> employees = await _context.Employee.ToListAsync();
        FileStreamResult fr = ExportToExcel.CreateExcelFile.StreamExcelDocument
                             (employees, "Employees.xlsx");
        return fr;
    }
    catch (Exception ex)
    {
        return new BadRequestObjectResult(ex);
    }
}

How simple that that !  You basically pass it a List, give it a filename, then my library creates the Excel file for you, and passes it back in a FileStreamResult object.

2. Adding the library to your ASP.NET application

If you're still using the original ASP.NET (and if so, why?!), then here are the steps to use the library in your application.  

First, you need to include the System.Web library in your project...

... and then uncomment the first using statement at the top of my CreateExcelFile.cs file:

#define USING_ASP_NET                //  Uncomment this line, if you're using the old ASP.NET
// #define USING_ASP_NET_CORE        //  Uncomment this line, if you're using ASP.NET Core 2 or later
// #define DATA_CONTAINS_FORMULAE

Next, you'll need to download and install the free Microsoft OpenXML SDK from here:  Microsoft OpenXML SDK download.   (Note: if this link changes, simply Google for "Microsoft OpenXML download".

You actually only need two files from this SDK:

• DocumentFormat.OpenXml.dll 
• WindowsBase.dll 

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

Using ASP.NET (not ASP.NET Core), you have access to three functions, for creating your Excel files.

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) 

For example, if my ASP.NET C# application had a list of employee records, stored in a List<Employee>, then I would 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);
}  

3. Adding the library to your C# WinForms application

This is almost the same steps as for ASP.NET (above).  Once again, you need to download and install the free Microsoft OpenXML SDK using the link above, and include the two .dlls in your project.

Then just include my CreateExcelFile.cs file in your project, and you're good to go.   Just get your data into a DataSet, DataTable, or List<> variable and call the CreateExcelDocument function, with a filename to save to.

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

And that's it.

Going forward

As I said, this C# library is amazing, but won't fill everyone's criteria.  However, it is an excellent starting point for adding your own functionality.   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.

Good luck, and don't forget to rate this article if you like it, and leave a comment with any questions.