How to Export DataTable Data to Excel Report in MVC 4/5

Tags: How to Export Data into excel file in MVC 4/5, Generate Excel Report in MVC 4/5, Generate SpreadSheet Report in MVC 4/5.

Dear Readers, I going to explain export data of DataTable records into Excel file in MVC application. I am using SQL Server for database and Visual Studio 2015 IDE.

This blog is especially for those programmers who have not used Entity Framework till now and are into the habit of using old ADO.NET framework for database coding.

Note: Here I am considering that you are aware of basics of MVC application creation such as View, Model and Controller. If not, please get some basics of MVC First Application.

So, let’s start with an example.

You have to get the data in DataTable object according to your condition. Then export the data into excel file. See the sample code:

Extra Namespaces:

using System.Data;

using System.Web.UI.WebControls;

using System.IO;

using System.Web.UI;

using System.Text;

Function to Export data

public void DownLoadReportInExcel()

        {

            SqlConnection con = new SqlConnection("Data Source=serverName;Initial Catalog=DBName;Integrated Security=True");

            SqlDataAdapter da = new SqlDataAdapter("select * from OrderData", con);

            DataTable dt = new DataTable();

            da.Fill(dt); //Filling DataTable Object

     GridView GridView1 = new GridView(); //Creating GridView Instance

            GridView1.AllowPaging = false;

            GridView1.DataSource = dt;

            GridView1.DataBind();

            Response.Clear();

            Response.Buffer = true;

            Response.AddHeader("content-disposition",

             "attachment;filename=MyOrderReport.xls");

            Response.Charset = "";

            Response.ContentType = "application/vnd.ms-excel";

            StringWriter sw = new StringWriter();

            HtmlTextWriter hw = new HtmlTextWriter(sw);

            for (int i = 0; i < GridView1.Rows.Count; i++)

            {

                //Apply text style to each Row

                GridView1.Rows[i].Attributes.Add("class", "textmode");

            }

            GridView1.RenderControl(hw);

            //Code, if Add image in the report. Supposing that image is inside folder- ‘images’

     // You can omit this code if no need of image

            string path = HttpContext.Server.MapPath("/images/red.png");

            string headerTable = @"<Table><tr><td><img src='" + path + "' /></td></tr></Table>";

//Adding Style

            string style = @"<style> .textmode { mso-number-format:\@; } </style>";

            Response.Write(style);

            Response.Write(headerTable);

            Response.Output.Write(sw.ToString());

            Response.Flush();

            Response.End();

}

You have to call this function from Action method of controller. You can also pass arguments in the function to implement the logic as per your requirements.

For Example:

public ActionResult ActionMethodName()

 {
DownLoadReportInExcel();

return View();

}

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