Downloading the DataTable data to excel

Step 1:

Add a reference with the name "ClosedXML" from the nuget packet manager.Use the old version of closedXml through the below command.Because new version supports dotnet framework 4.5

Install-Package ClosedXML -Version 0.86.0

Step 2:

Add the below code in the controller .Before that add the below namespaces in the controller then add the code

using System.Data.SqlClient;
using System.Data;
using ClosedXML.Excel;


[HttpPost]
public ActionResult Export()
        {

            //Get the data from database into datatable

            string strQuery = "select * " +

                " from employee";

            SqlCommand cmd = new SqlCommand(strQuery);
            DataTable dt = new DataTable("Grid");
            dt = GetData(cmd);
                       var wb = new XLWorkbook();

                       // Add a DataTable as a worksheet
            wb.Worksheets.Add(dt);

            wb.SaveAs("C:\\Users\\User\\Desktop\\AddingDataTableAsWorksheet.xlsx");
            return RedirectToAction("Index");
        }
        private DataTable GetData(SqlCommand cmd)
        {

            DataTable dt = new DataTable();

            String strConnString = System.Configuration.ConfigurationManager.

                 ConnectionStrings["StudentContext"].ConnectionString;

            SqlConnection con = new SqlConnection(strConnString);

            SqlDataAdapter sda = new SqlDataAdapter();

            cmd.CommandType = CommandType.Text;

            cmd.Connection = con;

            try
            {

                con.Open();

                sda.SelectCommand = cmd;

                sda.Fill(dt);
                dt.TableName = "Employee";
                return dt;

            }

            catch (Exception ex)
            {

                throw ex;

            }

            finally
            {

                con.Close();

                sda.Dispose();

                con.Dispose();

            }

        }


Step 3 

Add the below code in the view below the table in the Index view

<br />
<br />
@using (Html.BeginForm("Export", "Employee", FormMethod.Post))
{
    <input type="submit" value="Export" />
}