ASP.NET MVC: Export Data to Excel from Database using EPPlus

Updated on September 20, 2015     Kisan Patel

This tutorial will teach you how to export data to Excel file from Database using LINQ in ASP.Net MVC.

In this tutorial, we have to use EPPlus Nuget Package to Export Data to ExcelSheet. So first install EPPlus.dll into your ASP.Net MVC Projects.

Right click on Project ⇒ Manage NuGet packages… ⇒ Search Online for EPPlus ⇒ click Install.

Now, Create controller and name it to ExportController and add below line of code.

ExportController.cs

using CRUDDeom.Models;
using OfficeOpenXml;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.Mvc;

namespace CRUDDeom.Controllers
{
    public class ExportController : Controller
    {
        private DbContextModel db = new DbContextModel();

        public ActionResult Index()
        {
            return View();
        }

        public ActionResult Excel()
        {

            List accounts = db.Accounts.ToList();

            using (ExcelPackage pck = new ExcelPackage())
            {
                ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Accounts"); 
                ws.Cells["A1"].LoadFromCollection(accounts, true);
                // Load your collection "accounts"

                Byte[] fileBytes = pck.GetAsByteArray();
                Response.Clear();
                Response.Buffer = true;
                Response.AddHeader("content-disposition", "attachment;filename=DataTable.xlsx"); 
                // Replace filename with your custom Excel-sheet name.

                Response.Charset = "";
                Response.ContentType = "application/vnd.ms-excel";
                StringWriter sw = new StringWriter();
                Response.BinaryWrite(fileBytes);
                Response.End();
            }

            return RedirectToAction("Index");
        }

    }
}

Now, Right click on Index action of ExportController and add Index.cshtml view.

Index.cshtml

@{
    ViewBag.Title = "Index";
}

@Html.ActionLink("Export to Excel", "Excel")

In above code, we have add hyperlink that call Excel action of ExportController.

Now, run the project then browse the Index action of ExportController (/Export/Index) and click “Export to Excel” link.

Complete Code to Export Data to Excel

List accounts = db.Accounts.ToList();
// Retrive data and store into conllection.

using (ExcelPackage pck = new ExcelPackage())
{
    ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Accounts");
    ws.Cells["A1"].LoadFromCollection(accounts, true);
    Byte[] fileBytes = pck.GetAsByteArray();
    Response.Clear();
    Response.Buffer = true;
    Response.AddHeader("content-disposition", "attachment;filename=DataTable.xlsx");
    Response.Charset = "";
    Response.ContentType = "application/vnd.ms-excel";
    StringWriter sw = new StringWriter();
    Response.BinaryWrite(fileBytes);
    Response.End();
}