Export data into MS Excel from MVC in ASP.NET MVC

Export to export data into MS Excel from ASP.NET MVC?

To export data from database to MS Excel, we can use GridView control of the System.Web.UI.WebControls namespace that is part of ASP.NET Web Form. If this namespace is not in the project, we need to add reference of it.

CONTROLLER ACTION METHOD

private TrainingMVCContext db = new TrainingMVCContext();

        // GET: ExportData
        public ActionResult ExportToExcel()
        {
            // Step 1 - get the data from database
            var data = db.PersonalDetails.ToList();

            // instantiate the GridView control from System.Web.UI.WebControls namespace
            // set the data source
            GridView gridview = new GridView();
            gridview.DataSource = data;
            gridview.DataBind();

            // Clear all the content from the current response
            Response.ClearContent();
            Response.Buffer = true;
            // set the header
            Response.AddHeader("content-disposition", "attachment;
filename=itfunda.xls");
            Response.ContentType = "application/ms-excel";
            Response.Charset = "";
            // create HtmlTextWriter object with StringWriter
            using (StringWriter sw = new StringWriter())
            {
                using (HtmlTextWriter htw = new HtmlTextWriter(sw))
                {
                    // render the GridView to the HtmlTextWriter
                    gridview.RenderControl(htw);
                    // Output the GridView content saved into StringWriter
                    Response.Output.Write(sw.ToString());
                    Response.Flush();
                    Response.End();
                }
   }
   return View();
}

In the above action method, most of the codes are documented and self understood however in short we are doing following

• first we are getting the data from the database and setting to the source of the GridView
• clearing all the content of the response
• setting the response header and ContentType
• rendering the GridView conent using HtmlTextWriter and StringWriter

Calling the ExportToExcel action method will ask user to Open or Save the itfunda.xls file like below.

How to limit the number of columns appearing in the exported excel file?

To limit the number of columns appearing in the exported MS Excel file, set the data source of the GridView that has selected columns like below

PART OF CONTROLLER ACTION METHOD

var data = db.PersonalDetails.ToList().Select(
                 p => new
                 {
                     p.Active,
                     p.FirstName,
                     p.LastName
                 }
                );

// instantiate the GridView control from System.Web.UI.WebControls namespace
            // set the data source
            GridView gridview = new GridView();
            gridview.DataSource = data;
            gridview.DataBind();

Now, the exported excel file will have only Active, FirstName and LastName columns data only.

OUTPUT