Export to Excel using ClosedXML in ASP.Net

using System;

using System.Web.Mvc;

using JetSaveToursProject.Models;

using System.IO;

using System.Data;

using System.Data.SqlClient;

using ClosedXML.Excel;

        public ActionResult ExportToXLSX(string CountryId, string Deliverystatus, string FromDate, string ToDate)

        {

            if (CountryId == null)

                CountryId = "";

            if (CountryId == "")

                CountryId = "0";

            if (Deliverystatus == null)

                Deliverystatus = "";

            if (FromDate == null)

                FromDate = "";

            if (FromDate.Trim() == "")

                FromDate = "01 jan 2000";

            if (ToDate == null)

                ToDate = "";

            if (ToDate.Trim() == "")

                ToDate = DateTime.Now.ToString("dd MMM yyyy");

            SqlDataAccessLayer sqlobj = new SqlDataAccessLayer();

            SqlParameter[] p = {

                    new SqlParameter("@ID", Convert.ToInt64(Session[eSession.ID.ToString()])),

                    new SqlParameter("@COUNTRY_ID",CountryId),

                    new SqlParameter("@CG_DELIVERYSTATUS",Deliverystatus),

                    new SqlParameter("@CG_ENTRY_DATE", FromDate),

                    new SqlParameter("@CG_END_DATE", ToDate)

            };

            DataTable dt = sqlobj.ExecuteDataset(CommandType.StoredProcedure, "ProcedureName", p).Tables[0];

            var aCode = 65;

            using (XLWorkbook wb = new XLWorkbook())

            {

                var ws = wb.Worksheets.Add("Summary");

                int rowIndex = 1;

                int columnIndex = 0;

                ws.ColumnWidth = 30;

                foreach (DataColumn column in dt.Columns)

                {

                    ws.Cell(string.Format("{0}{1}", Char.ConvertFromUtf32(aCode + columnIndex), rowIndex)).Value = column.ColumnName;

                    ws.Cell(string.Format("{0}{1}", Char.ConvertFromUtf32(aCode + columnIndex), rowIndex)).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;

                    ws.Cell(string.Format("{0}{1}", Char.ConvertFromUtf32(aCode + columnIndex), rowIndex)).Style.Font.Bold = true;

                    ws.Cell(string.Format("{0}{1}", Char.ConvertFromUtf32(aCode + columnIndex), rowIndex)).Style.Font.FontSize = 13;

                    ws.Cell(string.Format("{0}{1}", Char.ConvertFromUtf32(aCode + columnIndex), rowIndex)).Style.Fill.BackgroundColor = XLColor.DodgerBlue;

                    ws.Cell(string.Format("{0}{1}", Char.ConvertFromUtf32(aCode + columnIndex), rowIndex)).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;

                    columnIndex++;

                }

                rowIndex++;

                foreach (DataRow row in dt.Rows)

                {

                    int valueCount = 0;

                    foreach (object rowValue in row.ItemArray)

                    {

                        ws.Cell(string.Format("{0}{1}", Char.ConvertFromUtf32(aCode + valueCount), rowIndex)).Value = rowValue;

                        ws.Cell(string.Format("{0}{1}", Char.ConvertFromUtf32(aCode + valueCount), rowIndex)).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;

                        ws.Cell(string.Format("{0}{1}", Char.ConvertFromUtf32(aCode + valueCount), rowIndex)).Style.Fill.BackgroundColor = XLColor.LightGray;

                        ws.Cell(string.Format("{0}{1}", Char.ConvertFromUtf32(aCode + valueCount), rowIndex)).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;

                        ws.Cell(string.Format("{0}{1}", Char.ConvertFromUtf32(aCode + valueCount), rowIndex)).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;

                        valueCount++;

                    }

                    rowIndex++;

                }

                Response.Clear();

                Response.Buffer = true;

                Response.Charset = "";

                Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

                Response.AddHeader("content-disposition", "attachment;filename=Summary.xlsx");

                using (MemoryStream MyMemoryStream = new MemoryStream())

                {

                    wb.SaveAs(MyMemoryStream);

                    MyMemoryStream.WriteTo(Response.OutputStream);

                    Response.Flush();

                    Response.End();

                }

            }

            return View();

        }