In this article, we will see different ways to export data to Excel from a web application. It is very common task for web developers. Here are different approaches to do it in ASP.NET – C#:
Approach 1:
Using the Excel PIA (primary interop assemblies) to generate a spreadsheet server-side.
It needs Microsoft Office suite installed on your server which is not good. So it is NOT recommended.
Approach 2:
Using CSV/TSV content and save it to a file with a .xls extension.
If you don’t need control over formatting, styles, or workbook structure, only need data in excel, this approach is good for you.
To convert Generic data to TSV (Tab Separated Values) Format, use following method:
public void WriteTsv<T>(IEnumerable<T> data, TextWriter output) { PropertyDescriptorCollection props = TypeDescriptor.GetProperties(typeof(T)); foreach (PropertyDescriptor prop in props) { output.Write(prop.DisplayName); // header output.Write("\t"); } output.WriteLine(); foreach (T item in data) { foreach (PropertyDescriptor prop in props) { output.Write(prop.Converter.ConvertToString( prop.GetValue(item))); output.Write("\t"); } output.WriteLine(); } }
and use following ASP.NET MVC action to generate excel:
public void ExportListFromTsv() { var data = new[]{ new{ Name="Ram", Email="ram@techbrij.com", Phone="111-222-3333" }, new{ Name="Shyam", Email="shyam@techbrij.com", Phone="159-222-1596" }, new{ Name="Mohan", Email="mohan@techbrij.com", Phone="456-222-4569" }, new{ Name="Sohan", Email="sohan@techbrij.com", Phone="789-456-3333" }, new{ Name="Karan", Email="karan@techbrij.com", Phone="111-222-1234" }, new{ Name="Brij", Email="brij@techbrij.com", Phone="111-222-3333" } }; Response.ClearContent(); Response.AddHeader("content-disposition", "attachment;filename=Contact.xls"); Response.AddHeader("Content-Type", "application/vnd.ms-excel"); WriteTsv(data, Response.Output); Response.End(); }
For simplicity, I am using hard-coded sample data.
Read Also: Upload and Read Excel File (.xls, .xlsx) in ASP.NET MVC
Approach 3:
Generating HTML content and save it to a file with a .xls extension.
To convert Generic data to HTML Format with Table control, use following method:
public void WriteHtmlTable<T>(IEnumerable<T> data, TextWriter output) { //Writes markup characters and text to an ASP.NET server control output stream. This class provides formatting capabilities that ASP.NET server controls use when rendering markup to clients. using (StringWriter sw = new StringWriter()) { using (HtmlTextWriter htw = new HtmlTextWriter(sw)) { // Create a form to contain the List Table table = new Table(); TableRow row = new TableRow(); PropertyDescriptorCollection props = TypeDescriptor.GetProperties(typeof(T)); foreach (PropertyDescriptor prop in props) { TableHeaderCell hcell = new TableHeaderCell(); hcell.Text = prop.Name; hcell.BackColor = System.Drawing.Color.Yellow; row.Cells.Add(hcell); } table.Rows.Add(row); // add each of the data item to the table foreach (T item in data) { row = new TableRow(); foreach (PropertyDescriptor prop in props) { TableCell cell = new TableCell(); cell.Text = prop.Converter.ConvertToString(prop.GetValue(item)); row.Cells.Add(cell); } table.Rows.Add(row); } // render the table into the htmlwriter table.RenderControl(htw); // render the htmlwriter into the response output.Write(sw.ToString()); } } }
In above method, we defined yellow header background color. use the above method to export data like below
public void ExportListFromTable() { var data = new[]{ new{ Name="Ram", Email="ram@techbrij.com", Phone="111-222-3333" }, new{ Name="Shyam", Email="shyam@techbrij.com", Phone="159-222-1596" }, new{ Name="Mohan", Email="mohan@techbrij.com", Phone="456-222-4569" }, new{ Name="Sohan", Email="sohan@techbrij.com", Phone="789-456-3333" }, new{ Name="Karan", Email="karan@techbrij.com", Phone="111-222-1234" }, new{ Name="Brij", Email="brij@techbrij.com", Phone="111-222-3333" } }; Response.ClearContent(); Response.AddHeader("content-disposition", "attachment;filename=Contact.xls"); Response.AddHeader("Content-Type", "application/vnd.ms-excel"); WriteHtmlTable(data, Response.Output); Response.End(); }
You can use ASP.NET GridView directly(instead of table).
public void ExportListFromGridView() { var data = new[]{ new{ Name="Ram", Email="ram@techbrij.com", Phone="111-222-3333" }, new{ Name="Shyam", Email="shyam@techbrij.com", Phone="159-222-1596" }, new{ Name="Mohan", Email="mohan@techbrij.com", Phone="456-222-4569" }, new{ Name="Sohan", Email="sohan@techbrij.com", Phone="789-456-3333" }, new{ Name="Karan", Email="karan@techbrij.com", Phone="111-222-1234" }, new{ Name="Brij", Email="brij@techbrij.com", Phone="111-222-3333" } }; Response.ClearContent(); Response.AddHeader("content-disposition", "attachment;filename=Contact.xls"); Response.AddHeader("Content-Type", "application/vnd.ms-excel"); using (System.IO.StringWriter sw = new System.IO.StringWriter()) { using (System.Web.UI.HtmlTextWriter htw = new System.Web.UI.HtmlTextWriter(sw)) { GridView grid = new GridView(); grid.DataSource = data; grid.DataBind(); grid.RenderControl(htw); Response.Write(sw.ToString()); } } Response.End(); }
If you want to export to excel from ASP.NET Razor View then use following action:
public ActionResult ExportView() { Response.AddHeader("content-disposition", "attachment;filename=Report1.xls"); Response.AddHeader("Content-Type", "application/vnd.ms-excel"); return View(data); }
add a razor view of above action and use following code:
@model IEnumerable<ImportExcel.Models.Contact> @{ Layout = null; } <!DOCTYPE html> <html> <head> <meta name="viewport" content="width=device-width" /> <title>ExportView</title> </head> <body> <div> <table> <tr> <th> @Html.DisplayNameFor(model => model.Name) </th> <th> @Html.DisplayNameFor(model => model.Email) </th> <th> @Html.DisplayNameFor(model => model.Phone) </th> </tr> @foreach (var item in Model) { <tr> <td> @Html.DisplayFor(modelItem => item.Name) </td> <td> @Html.DisplayFor(modelItem => item.Email) </td> <td> @Html.DisplayFor(modelItem => item.Phone) </td> </tr> } </table> </div> </body> </html>
In this approach, the logic is same to generate HTML, but the ways are different.
Approach 4:
When you open .xls file generated with Approach 2 & 3, you will get warning because of format difference. It is a bit annoyance.
EPPlus for .XLS
In this Approach, we will use free open source library EPPlus to export the content. It writes Excel 2007/2010 files using the Open Office Xml format (xlsx) only.
Run following command in the Package Manager Console to install EPPlus:
install-package epplus
Use following method for exporting data:
public void ExportListUsingEPPlus() { var data = new[]{ new{ Name="Ram", Email="ram@techbrij.com", Phone="111-222-3333" }, new{ Name="Shyam", Email="shyam@techbrij.com", Phone="159-222-1596" }, new{ Name="Mohan", Email="mohan@techbrij.com", Phone="456-222-4569" }, new{ Name="Sohan", Email="sohan@techbrij.com", Phone="789-456-3333" }, new{ Name="Karan", Email="karan@techbrij.com", Phone="111-222-1234" }, new{ Name="Brij", Email="brij@techbrij.com", Phone="111-222-3333" } }; ExcelPackage excel = new ExcelPackage(); var workSheet = excel.Workbook.Worksheets.Add("Sheet1"); workSheet.Cells[1, 1].LoadFromCollection(data, true); using (var memoryStream = new MemoryStream()) { Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; Response.AddHeader("content-disposition", "attachment; filename=Contact.xlsx"); excel.SaveAs(memoryStream); memoryStream.WriteTo(Response.OutputStream); Response.Flush(); Response.End(); } }
You can see how easy it is in .xlsx format. This library allows you to set worksheet properties, custom and conditional formatting, Cell Ranges and Cell styling (Border, Color, Fill, Font, Number, Alignments),Formula calculation ..etc.
If you have datatable as datasource, use following to load it
workSheet.Cells[1, 1].LoadFromDataTable(myDataTable, true);
NPOI for .XLS & .XLSX
If you want to generate both .xls and .xlsx files from single library then use NPOI library. This project is the .NET version of POI from Apache Foundation. NPOI can read and write xls (Excel 97-2003), xlsx(Excel 2007+). To install NPOI, run the following command in the Package Manager Console:
Install-Package NPOI
Add following method:
public void WriteExcelWithNPOI(DataTable dt, String extension) { IWorkbook workbook; if (extension == "xlsx") { workbook = new XSSFWorkbook(); } else if (extension == "xls") { workbook = new HSSFWorkbook(); } else { throw new Exception("This format is not supported"); } ISheet sheet1 = workbook.CreateSheet("Sheet 1"); //make a header row IRow row1 = sheet1.CreateRow(0); for (int j = 0; j < dt.Columns.Count; j++) { ICell cell = row1.CreateCell(j); String columnName = dt.Columns[j].ToString(); cell.SetCellValue(columnName); } //loops through data for (int i = 0; i < dt.Rows.Count; i++) { IRow row = sheet1.CreateRow(i + 1); for (int j = 0; j < dt.Columns.Count; j++) { ICell cell = row.CreateCell(j); String columnName = dt.Columns[j].ToString(); cell.SetCellValue(dt.Rows[i][columnName].ToString()); } } using (var exportData = new MemoryStream()) { Response.Clear(); workbook.Write(exportData); if (extension == "xlsx") //xlsx file format { Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", "ContactNPOI.xlsx")); Response.BinaryWrite(exportData.ToArray()); } else if (extension == "xls") //xls file format { Response.ContentType = "application/vnd.ms-excel"; Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", "ContactNPOI.xls")); Response.BinaryWrite(exportData.GetBuffer()); } Response.End(); } }
Note: In above method, It is clearly visible response syntax differences (ContentType, Header and Write) between xls and xlsx.
Consider following datatable as datasource:
DataTable dt = new DataTable(); dt.Columns.Add("UserId", typeof(Int32)); dt.Columns.Add("Name", typeof(string)); dt.Columns.Add("Email", typeof(string)); dt.Columns.Add("Phone", typeof(string)); dt.Rows.Add(1, "Ram", "ram@techbrij.com", "111-222-3333"); dt.Rows.Add(2, "Shyam", "shyam@techbrij.com", "159-222-1596"); dt.Rows.Add(3, "Mohan", "mohan@techbrij.com", "456-222-4569"); dt.Rows.Add(4, "Sohan", "sohan@techbrij.com", "789-456-3333"); dt.Rows.Add(5, "Karan", "karan@techbrij.com", "111-222-1234"); dt.Rows.Add(6, "Brij", "brij@techbrij.com", "111-222-3333");
To export in .xls format:
WriteExcelWithNPOI(dt, "xls");
To export in .xlsx format:
WriteExcelWithNPOI(dt, "xlsx");
Conclusion:
This post explains the different ways to export data in excel (.xls and .xlsx) format and their pros and cons. If you want your plain data in spreadsheet then approach 2 is good. If you are exporting data from any HTML element or you need some data formatting then approach 3 is good. If you need xlsx format then I recommend EPPlus. If you have to do a lot of operations on Excel and need support of both formats then NPOI is the best option for you, but it is very heavy.
Hope, It helps. Feel free to share your opinion in comment box.
HI All
Thanks for sharing the post for multiple approaches for exporting the excel.
Small clarification : Can we export multiple tables result set in multiple excel then zip the excel as attachment.
In depth : i have stored procedure which is giving 3 different result set and this 3 different result set should be exported to different excel sheet then zip and download.
Request you to share the url or approach using any third party .DLL
Regards & Thanks
Karthik
Regards
Praveen
Thanks a lot for providing the great article, now i am able to work on export excel
You can use ZetExcel for empowering you to build cross-platform applications having the ability to generate, modify, convert, render and print spreadsheets without using Microsoft Excel.
How to use it in C# library project?
What is Response?
I would like to thank you for this article, it helps me a lot with my task. Very nice indeed.
Very Nice Article! Thanks
what is Response Variable
Great Tutorial
how to add image to above content/ display content after 5 rows
Nice Code , Thank you sir
Hi, this is a great post, maybe a dumb question from my side but how do I reference once installed via Nugget Package Manager Console.
Hi,
I have requirement edit existing SampleExcelTemplate and then download updated excel.
can do it using EpPlus? If yes, How.
or, Is there any method avalilble to copy worksheet data to new object of WorkSheet?
Thanks In Advance.
Hi I use EPPPlus library.it’s awesome,But in large scales data has TimeOut,
nice information
could you please tell how can I save file under specific folder in application using approach 4, EPPlus for .XLS
Hello,
the code is almost the same, except in the part where is MemoryStream you just edit to something like this:
using (var exportData = new MemoryStream())
using (BinaryWriter bw = new BinaryWriter(File.Create(“C:\\myFile.” + extension)))
{
workbook.Write(exportData);
bw.Write(exportData.ToArray());
}
Thanks ….., another great post filled with nuggets! I converting my important file without Microsoft Excel using ZetExcel.com
Hello,
I have tried to export html (partialview) in to excel it works but when I open file it shows me alert “File data is corrupted” could you please suggest me what I am doing wrong. below is my code
Response.AddHeader(“content-disposition”, “attachment;filename=Report1.xls”);
Response.AddHeader(“Content-Type”, “application/vnd.ms-excel”);
return PartialView(“ExportData”, List);
Great tutorial. Thanks for breaking into down into various possible approached…much appreciated
Hi, thanks for the help – Approach 4 for the win
Can anyone tell me how to insert picture in a cell with above code.
open ur zip take that dick out and start shaking it
Hello,
please help me how do i do this dynamically in my data
var data
Hi, thank you for great blog. Have a look at this library. I think it is quite useful for exporting data.
https://github.com/TopSoftSolutions/TopSoft.ExcelExport