SharePoint Application Page: How to export DataTable data to Excel file using ClosedXML?

Installing ClosedXML in your project (VS)

Go to Tools > Library Package Manager > Manage NuGet Packages..

Search for ClosedXML and install it. This will install DocumentFormat.OpenXML as well for you.

Reference to above should be added under References automatically. If not then you can add them manually.

Code:

using DocumentFormat.OpenXml;
using ClosedXML.Excel;
using System.Xml;

using System.IO;

protected void Page_Load(object sender, EventArgs e)
        {
//So that page does not stop responding after generating Excel file (Response.End())
btnExportExcel.OnClientClick = "_spFormOnSubmitCalled = false;_spSuppressFormOnSubmitWrapper=true;";
}


protected void btnExportExcel_Click(object sender, EventArgs e)
        {
            try
            {
                DataSet ds = new DataSet();
                //Call method which returns DataTable
                ds.Tables.Add(GetDataTable());
                ExportToExcel(ds);
            }
            catch (Exception ex)
            {
                lblMessage.Text = "Error: " + ex.Message;
            }
        }


protected void ExportToExcel(DataSet ds)
        {
            XLWorkbook wb = new XLWorkbook();
            DataTable dt = ds.Tables[0];
            wb.Worksheets.Add(dt);

            string fileName = Server.UrlEncode("Report" +  "_" + DateTime.Now.ToShortDateString() + ".xlsx");
            MemoryStream stream = (MemoryStream)GetStream(wb);

            Response.Clear();
            Response.Buffer = true;
            Response.AddHeader("content-disposition", "attachment; filename=" + fileName);
            Response.ContentType = "application/vnd.ms-excel";
            Response.BinaryWrite(stream.ToArray());
            Response.Flush();
            Response.End();
        }


        public Stream GetStream(XLWorkbook excelWorkbook)
        {
            Stream fs = new MemoryStream();
            excelWorkbook.SaveAs(fs);
            fs.Position = 0;
            return fs;
        }