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:
- Programmatically convert MS Word DOC and DOCX files to PDF using Office Interop
- Programmatically Access, Read and Edit MS Word DOC and DOCX files using Office Interop
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:
|
1 |
PM> Install-Package EPPlus |
If you prefer to install it through the .NET CLI, you can use this command from an elevated command prompt or powershell prompt:
|
1 |
> dotnet add package EPPlus |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 |
public ActionResult ConvertToXLSX() { byte[] fileData = null; // replace the GetDataTable() method with your DBMS-fetching code. using (DataTable dt = GetDataTable()) { // create an empty spreadsheet using (var p = new ExcelPackage()) { // add a worksheet to the spreadsheet ExcelWorksheet ws = p.Workbook.Worksheets.Add(dt.TableName); // Initialize rows and cols counter: note that they are 1-based! var row = 1; var col = 1; // Create the column names on the first line. // In this sample we'll just use the DataTable colum names row = 1; col = 0; foreach (DataColumn dc in dt.Columns) { col++; ws.SetValue(row, col, dc.ColumnName); } // Insert the DataTable rows to the XLS file foreach (DataRow r in dt.Rows) { row++; col = 0; foreach (DataColumn dc in dt.Columns) { col++; ws.SetValue(row, col, r[dc].ToString()); } // alternate light-gray color for uneven rows (3, 5, 7, 9)... if (row % 2 != 0) { ws.Row(row).Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid; ws.Row(row).Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.LightGray); } } // output the XLSX file using (var ms = new MemoryStream()) { p.SaveAs(ms); ms.Seek(0, SeekOrigin.Begin); fileData = ms.ToArray(); } } } string fileName = "ConvertedFile.xlsx"; string contentType = System.Web.MimeMapping.GetMimeMapping(fileName); Response.AppendHeader("Content-Disposition", String.Format("attachment;filename={0}", fileName)); return File(fileData, contentType); } |
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:
- We fetch a DataTable object using a custom Data Provider method.
- We create a ExcelPackage object, which is the main container used by EPPlus for the XLSX file.
- We add a ExcelWorksheet inside the ExcelPackage, which is the worksheet we'll put the data in.
- We iterate the DataTable columns, adding them to the first row of our worksheet to build our header row.
- 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.
- 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.
- 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.
- How to Format and Style a Excel XLSX Worksheet with EPPlus
- How to setup custom Data Type formats to cells and/or columns with EPPlus
Hi,
If EPPlus can also export images(image files on server) into Excel?
Thanks
Yes, it should be possible using the
sheet.Drawingscollection.Here’s a code sample:
https://stackoverflow.com/a/44252624/1233379