Post
Ask Question

Export Data To Excel File In ASP.NET Using EPPlus

We are going to use EPPLus library in this article. It is an open source library and very easy to use for developers.

EPPlus supports many properties like cell ranges, cell styling (border, color, fill, font, number, alignments), charts, pictures, shapes, comments, tables, protection, encryption, pivot tables, data validation, conditional formatting, formula calculation etc.

We have to follow some simple steps for using EPPlus library in C#.

Step 1

We have to install EPPlus through manage NuGet packages, as shown below.


We can install it, using Package Manager console with the line given below.

Install-Package EPPlus 

Step 2

Now, add the line given below in .ASPX page. 

  1. <asp:Button ID="ExportExcel" runat="server" Text="Export Excel" OnClick="ExportExcel_Click" />    

Step 3

Now, add the two namespaces given below on the top of .CS page.

  1. using OfficeOpenXml;  
  2. using OfficeOpenXml.Style;  
  3. using System.IO;  

Step 4

Create a method for exporting Excel file by clicking Export button and writing the logic. 

  1. protected void ExportExcel_Click(object sender, EventArgs e)  
  2. {  
  3.     var students = new []   
  4.     {  
  5.         new {  
  6.             Id = "101", Name = "Vivek", Address = "Hyderabad"  
  7.         },  
  8.         new {  
  9.             Id = "102", Name = "Ranjeet", Address = "Hyderabad"  
  10.         },  
  11.         new {  
  12.             Id = "103", Name = "Sharath", Address = "Hyderabad"  
  13.         },  
  14.         new {  
  15.             Id = "104", Name = "Ganesh", Address = "Hyderabad"  
  16.         },  
  17.         new {  
  18.             Id = "105", Name = "Gajanan", Address = "Hyderabad"  
  19.         },  
  20.         new {  
  21.             Id = "106", Name = "Ashish", Address = "Hyderabad"  
  22.         }  
  23.     };  
  24.     ExcelPackage excel = new ExcelPackage();  
  25.     var workSheet = excel.Workbook.Worksheets.Add("Sheet1");  
  26.     workSheet.TabColor = System.Drawing.Color.Black;  
  27.     workSheet.DefaultRowHeight = 12;  
  28.     //Header of table  
  29.     //  
  30.     workSheet.Row(1).Height = 20;  
  31.     workSheet.Row(1).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;  
  32.     workSheet.Row(1).Style.Font.Bold = true;  
  33.     workSheet.Cells[1, 1].Value = "S.No";  
  34.     workSheet.Cells[1, 2].Value = "Id";  
  35.     workSheet.Cells[1, 3].Value = "Name";  
  36.     workSheet.Cells[1, 4].Value = "Address";  
  37.     //Body of table  
  38.     //  
  39.     int recordIndex = 2;  
  40.     foreach(var student in students) {  
  41.         workSheet.Cells[recordIndex, 1].Value = (recordIndex - 1).ToString();  
  42.         workSheet.Cells[recordIndex, 2].Value = student.Id;  
  43.         workSheet.Cells[recordIndex, 3].Value = student.Name;  
  44.         workSheet.Cells[recordIndex, 4].Value = student.Address;  
  45.         recordIndex++;  
  46.     }  
  47.     workSheet.Column(1).AutoFit();  
  48.     workSheet.Column(2).AutoFit();  
  49.     workSheet.Column(3).AutoFit();  
  50.     workSheet.Column(4).AutoFit();  
  51.     string excelName = "studentsRecord";  
  52.     using(var memoryStream = new MemoryStream()) {  
  53.         Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";  
  54.         Response.AddHeader("content-disposition", "attachment; filename=" + excelName + ".xlsx");  
  55.         excel.SaveAs(memoryStream);  
  56.         memoryStream.WriteTo(Response.OutputStream);  
  57.         Response.Flush();  
  58.         Response.End();  
  59.     }  
  60. }   

In this method, we are taking static values for the students data but in real time, we can use database call and foreach loop for iteration of each record.

In the lines given below, we are creating an instance of ExcelPackage and with that object, we are giving the sheet name as Sheet1. 

  1. ExcelPackage excel = new ExcelPackage();  
  2. var workSheet = excel.Workbook.Worksheets.Add("Sheet1");   

The properties are given below of the first row of Excel sheet. 

  1. workSheet.Row(1).Height = 20;  
  2. workSheet.Row(1).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;  
  3. workSheet.Row(1).Style.Font.Bold = true;   

The code for the first row is given below; i.e., header row for Excel sheet.

  1. workSheet.Cells[1, 1].Value = "S.No";  
  2. workSheet.Cells[1, 2].Value = "Id";  
  3. workSheet.Cells[1, 3].Value = "Name";  
  4. workSheet.Cells[1, 4].Value = "Address";   

We are using foreach loop for inserting the student record into Excel sheet, Here, we are using recordIndex for inserting rows for Excel sheet. We already inserted the 1st row as shown in earlier lines of code, so it will start with the 2nd row due to which we assigned recordIndex = 2 in the lines given below.

  1. int recordIndex = 2;  
  2. foreach(var student in students)  
  3. {  
  4.     workSheet.Cells[recordIndex, 1].Value = (recordIndex - 1).ToString();  
  5.     workSheet.Cells[recordIndex, 2].Value = student.Id;  
  6.     workSheet.Cells[recordIndex, 3].Value = student.Name;  
  7.     workSheet.Cells[recordIndex, 4].Value = student.Address;  
  8.     recordIndex++;  
  9. }   

By default, the column width is not set to auto fit for the content of the range, so we are using AutoFit() method here. We can also use AutoFit (double MinimumWidth) and AutoFit (double MinimumWidth, double MaximumWidth) as per our requirement.

  1. workSheet.Column(2).AutoFit();  
  2. workSheet.Column(3).AutoFit();  
  3. workSheet.Column(4).AutoFit();   

We can give our own name to Excel file, as shown below.

  1. Response.AddHeader("content-disposition", "attachment; filename=" + excelName + ".xlsx");   

Here, we can see the screenshot given below for Excel file.


In this easy way, we can export Excel files in ASP.NET, using EPPlus library.


Similar Articles