Excel file download in ASP.NET

In this article we are going to learn how we can download data as an excel file. For Excel file download in ASP.NET, we have several methods and here I’m going to explain a very easy method using a nuget package called EPPlus. EPPlus is  sophisticate enough to perform simple to complex excel operations. Lets see how this can be done in a MVC project.

Here we are focusing on the code implementation only. For the package installations you can refer to our old posts like Blob Storage or Logging Application Block. Once we install latest EPPlus package, we can start coding for Excel file download. First add the namespaces for EPPlus. 

using OfficeOpenXml;

using OfficeOpenXml.Style;

Then add a Post method in the controller like below.

[HttpPost]
public ActionResult ExportEmployeeList()
{
           ExcelPackage excel = new ExcelPackage();
           var workSheet = excel.Workbook.Worksheets.Add("Sheet1");
           workSheet.TabColor = System.Drawing.Color.Black;
           workSheet.DefaultRowHeight = 12;
           //Header of table  
           //  
           workSheet.Row(1).Height = 20;
           workSheet.Row(1).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
           workSheet.Row(1).Style.Font.Bold = true;
           workSheet.Cells[1, 1].Value = "Employee No";
           workSheet.Cells[1, 2].Value = "Name";
           workSheet.Cells[1, 3].Value = "Joined Date";
           workSheet.Cells[1, 4].Value = "Designation";
           workSheet.Cells[1, 5].Value = "Email";
           workSheet.Cells[1, 6].Value = "Work Location";
 
           int recordIndex = 2;

           
//Read employees from DB
var employees = employeeRepository.GetAllEmployees();


//Employee add to excel            
            foreach (var employee in employees)
            {
                workSheet.Cells[recordIndex, 1].Value = (recordIndex - 1).ToString();
                workSheet.Cells[recordIndex, 2].Value = employee.EmpNo;
                workSheet.Cells[recordIndex, 3].Value = employee.Name;
                workSheet.Cells[recordIndex, 4].Value = employee.JoinedDate;
                workSheet.Cells[recordIndex, 5].Value = employee.Designation;
                workSheet.Cells[recordIndex, 6].Value = employee.Email;
                workSheet.Cells[recordIndex, 7].Value = employee.Location;
                recordIndex++;
            }
  
            workSheet.Column(1).AutoFit();
            workSheet.Column(2).AutoFit();
            workSheet.Column(3).AutoFit();
            workSheet.Column(4).AutoFit();
            workSheet.Column(5).AutoFit();
            workSheet.Column(6).AutoFit();
            string excelName = "EmployeeList";
// Download excel
            using (var memoryStream = new MemoryStream())
            {
                Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                Response.AddHeader("content-disposition", "attachment; filename=" + excelName + ".xlsx");
                excel.SaveAs(memoryStream);
                memoryStream.WriteTo(Response.OutputStream);
                Response.Flush();
                Response.End();
            }
 
            return new EmptyResult();
        }

Now we can call this post method in a View for a button click event. For this we can use an Ajax call which is the easy way. To call an ajax call you can refer to the post Download CSV data

First part is to setup the excel sheet and second part is for the Excel header setup. Then we read all employees from the Database and adding each employee to the excel sheet. Next we are setting AutoFit option to format the excel sheet into better fitting. Last part is for the Excel file download which is a common method we use in .NET applications.

We must keep note on the index numbers in header and body. Those index numbers should be matched during data adding. Otherwise Header column name and data column will be mismatched.