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" />
}
No comments:
Post a Comment