Export to Excel in MVC

Export to excel in MVC 
EPPlus

• EPPlus is a .net library that reads and writes Excel 2007/2010 files using the Open Office Xml format (xlsx). 

1. Download Epplus from here or you can also nuget package manager to install epplus

2. Download DocumentFormat.OpenXml from here

3. Create New Project  (web project ) MVC 

4. Add Reference of Epplus and DocumentFormat.OpenXml.dll to your project 



Add controller to your project.
as the following .

using OfficeOpenXml;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Mvc;

namespace ExportToExcel.Controllers
{
    public class ReportGenController : Controller
    {
        public ActionResult GenerateReport()
        {
            return View();
        }
        [HttpPost]
        public ActionResult GenerateReport(string Employees)
        {
            ExcelPackage pck = new ExcelPackage();
            ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Report");
            ws.Cells.Style.Font.Size = 11; //Default font size for whole sheet
            ws.Cells.Style.Font.Name = "Calibri"; //Default Font name for whole sheet    
            if (System.Threading.Thread.CurrentThread.CurrentCulture.TextInfo.IsRightToLeft)   // Right to Left for Arabic lang
            {
                ExcelWorksheetView wv = ws.View;
                wv.ZoomScale = 100;
                wv.RightToLeft = true;
                ws.PrinterSettings.Orientation = eOrientation.Landscape;
                ws.Cells.AutoFitColumns();
            }
            else
            {
                ExcelWorksheetView wv = ws.View;
                wv.ZoomScale = 100;
                wv.RightToLeft = false;
                ws.PrinterSettings.Orientation = eOrientation.Landscape;
                ws.Cells.AutoFitColumns();
            }
            ws.Cells.AutoFitColumns();
            DataTable dt = new DataTable(); // Read records from database here
            DataColumn[] cols = { new DataColumn("Employee ID"), 

             new DataColumn("Employee Name"), new DataColumn("NIC"), 

             new DataColumn("Address") };
            dt.Columns.AddRange(cols);
            for (int i = 0; i < 20; i++)
            {
                DataRow row = dt.NewRow();
                row[0] = i;
                row[1] = "Emp Name " + i;
                row[2] = "34234234 " + i;
                row[3] = "Address " + i;
                dt.Rows.Add(row);
            }
            ws.Cells[2, 1].LoadFromDataTable(dt, true, OfficeOpenXml.Table.TableStyles.Medium24);  // Print headers true          
            HttpContext.Response.Clear();
            HttpContext.Response.AddHeader("", "");
            HttpContext.Response.Charset = System.Text.UTF8Encoding.UTF8.WebName;
            HttpContext.Response.ContentEncoding = System.Text.UTF8Encoding.UTF8;
            HttpContext.Response.AddHeader("content-disposition", "attachment;  filename=Report.xlsx");
            HttpContext.Response.ContentType = "application/text";
            HttpContext.Response.ContentEncoding = System.Text.Encoding.GetEncoding("utf-8");
            HttpContext.Response.BinaryWrite(pck.GetAsByteArray());
            HttpContext.Response.End();
            return View();
        }
    }
}

Add View to the action Method GenerateReport()
The view should Look like this



@{
    ViewBag.Title = "GenerateReport";
}

<h2>Generate Report</h2>
@using (Html.BeginForm())
{
    <input type="hidden" id="Employees" />
    <div class="form-horizontal">
        <div class="form-group">
            <div class=" col-md-offset-2 col-md-2">
                <button type="submit" value="" class="btn btn-success">Generate Report</button>
            </div>
        </div>
    </div>
}