ASP.NET – Generate MS Excel files programmatically with EPPlus How to generate XLS and XLSX Excel files programmatically from any ASP.NET, ASP.NET MVC or ASP.NET Core application with the free EPPlus NuGet package

ASP.NET - Generate MS Excel files programmatically with EPPlus

If you’ve stumbled upon this post it most likely means that you’re looking for a way to generate MS Excel XLS / XLSX files within your ASP.NET web application. The typical scenario for such needis the following one: you have a DBMS from where you pull off some data – using a OleDBConnection, a SqlConnection, an EntityFramework model or anything database-fetching technique  – which you want to send to your users through a downloadable MS Excel file.

In purely theoretical terms, you might think about doing this using the MS Office Interop libraries, using a technique similar to what we explained in a couple other articles:

However, if you are here (or if you already read those posts) you most likely already know that the Microsoft Office primary interop assemblies (PIAs), better known as Office Interop, are not something you should rely upon: although there is an ASP.NET library package specifically designed to handle MS Excel files, the whole Office.Interop set of libraries is not meant to be used in web applications and will definitely cause you some nasty setup and configuration issues, such as those we enumerated (and tried to deal with) there.

Luckily enough, there’s a good news for you: a NuGet library package that does basically the same tasks you might want to use the Microsoft.Office.Interop.Excel namespace for, while working way better than that in a typical web application scenario: and it’s also completely free to use!

We’re talking of EPPlus, a great library developed by Jan Kallman and available either on GitHub and as a compiled, full-flegded NuGet package. The project is licensed under the GNU Library General Public License (LGPL), meaning that it can be used everywhere. In this post we’ll briefly show how we can install this great tool on a typical Visual Studio environment and then use it to generate a MS Excel file from a standard ASP.NET DataTable which we (ideally) pulled off by a DBMS of some sort.

Installing EPPlus

The most practical way to install EPPlus from Visual Studio is to open the NuGet Package Manager Console (View > Other Windows > Package Manager Console) and type the following command:

If you prefer to install it through the .NET CLI, you can use this command from an elevated command prompt or powershell prompt:

Version notes

It’s worth noting that this test-drive was performed using EPPlus version 4.5.3.1, which was the latest available version at the time of writing: such version is fully compatible with all the most recent versionf of the .NET Framework, from 3.5 to 4.7.x (we tested it with 4.7.2).

EPPlus usage example

Without further ado, here’s a full source code sample that will convert a standard DataTable to a XLSX Excel file and then send it to the user for the download:

As we can see, this is an ActionResult method that could fit on any ASP.NET MVC Controller: if you’re not using ASP.NET MVC you can still use it, just copy the method content wherever you need to (classic ASP.NET, Console Application, Windows Forms, and so on).

The code is quite self-explanatory and full of comments  that should hopefully explain the various processing steps. However, let’s briefly summarize what are we doing here:

  1. We fetch a DataTable object using a custom Data Provider method.
  2. We create a ExcelPackage object, which is the main container used by EPPlus for the XLSX file.
  3. We add a ExcelWorksheet inside the ExcelPackage, which is the worksheet we’ll put the data in.
  4. We iterate the DataTable columns, adding them to the first row of our worksheet to build our header row.
  5. We iterate the DataTable rows, adding each one of them to our worksheet (starting from row 2), so that each DataTable row will result in a corresponding worksheet row.
  6. Once the conversion between the DataTable and the ExcelPackage is done, we create a MemoryStream to store the ExcelPackage binary data, then convert it to a byte array.
  7. We prepare the HTML response and send the XLSX file to the user with Content-Disposition attachment, so that the browser will force the download of the file.

It’s worth noting that we used a one-liner function to retrieve the proper mime-type for XLSX files (which is “application/vnd.openxmlformats-officedocument.spreadsheetml.sheet“): such function is available only since .NET Framework 4.5 and it’s the preferred method to retrieve mime-types since then. If you need to retrieve the mime-type with older .NET Frameworks, you can use one of the various workaround described in this other post.

Conclusion

That’s basically it, at least for now: I will definitely write again about the topic in the near future and share additional code samples. Meanwhile, feel free to use or modify the above code to your specific needs and enjoy the DataTable-to-XSLX conversion thanks to EPPlus!

UPDATE: here are a couple more posts regarding other cool things that you can do with EPPlus.

 

About Ryan

IT Project Manager, Web Interface Architect and Lead Developer for many high-traffic web sites & services hosted in Italy and Europe. Since 2010 it's also a lead designer for many App and games for Android, iOS and Windows Phone mobile devices for a number of italian companies. Microsoft MVP for Development Technologies since 2018.

View all posts by Ryan

0 Comments on “ASP.NET – Generate MS Excel files programmatically with EPPlus How to generate XLS and XLSX Excel files programmatically from any ASP.NET, ASP.NET MVC or ASP.NET Core application with the free EPPlus NuGet package

  1. Pingback: ASP.NET How to set the proper Data Type of MS Excel cells with EPPlus

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.