How to Export data from database to Excel in MVC 4/5

Dear Readers, Today I am going to explain that how can we export data from database to Excel File in MVC 4/5 application. To understand this blog very clearly, please read this article first –

http://programmingwithrudra.blogspot.in/2016/05/how-to-search-data-by-dates-in-mvc-45.html

In the last application, our controller was Home. Action was SearchDataByDate, Model was SearchDataByDateModel and view was SearchDataByDate.cshtml. 

We retrieved data from the database in the last article according to the input starting and ending dates.

Now to export data, we will have to take following steps:

Step-1: First of all add reference in your project of name – ClosedXML.dll. Download it from here.

Step-2: Add the namespace in the controller - using ClosedXML.Excel;

Step-3: Open MS Excel in your computer system and save it inside your project with name Book1.xlsx. There is no need to create data inside the file, just save it.

Step-4: Refresh your project by using Solution Explorer.

Step-5: Now add an action method in the controller. The code is:

public ActionResult ExportData()

        {

            DateTime datefrom =Convert.ToDateTime(TempData["datefrom"]);

            DateTime todate = Convert.ToDateTime(TempData["todate"]);

            DataTable dt = DAL.GetOrderData(datefrom, todate);

            string filepath = System.Web.HttpContext.Current.Server.MapPath("~/Book1.xlsx");

            using (XLWorkbook wb = new XLWorkbook(filepath))

            {

                wb.Worksheets.Add(dt, "Order-Report");

// “Order-Report” is the name of auto generated worksheet

                wb.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;

                wb.Style.Font.Bold = true;

                Response.Clear();

                Response.Buffer = true;

                Response.Charset = "";

                Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

                Response.AddHeader("content-disposition", "attachment;filename= OrderReport.xlsx");

                using (MemoryStream MyMemoryStream = new MemoryStream())

                {

                    wb.SaveAs(MyMemoryStream);

                    MyMemoryStream.WriteTo(Response.OutputStream);

                    Response.Flush();

                    Response.End();

                }

            }

            return RedirectToAction("SearchDataByDate");

        }

Step-5: Add a link in View to export data. The code is –

<p>@Html.ActionLink("Export This Data","ExportData")</p>

Build and Run the project.

I hope it will be helpful for you.

Happy Coding.

Find my YouTube channel from HERE.

Rudra Pratap Singh

Software Engineer

singhrudrapratap29[at]gmail[dot]com

Web Browser Snap Shots: