nedash says:

using (XLWorkbook wb = new XLWorkbook())

                        {

                            wb.Worksheets.Add(dt, "Customers");

 

                            Response.Clear();

                            Response.Buffer = true;

                            Response.Charset = "";

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

                            //ذخیره فایل به تاریخ

                            Response.AddHeader("content-disposition", string.Format("attachment;filename={0}.xls", miladitoshamsi(DateTime.Now.ToString("yyyy-MM-dd"))));

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

                            using (MemoryStream MyMemoryStream = new MemoryStream())

                            {

                                wb.SaveAs(MyMemoryStream);

                                MyMemoryStream.WriteTo(Response.OutputStream);

                                Response.Flush();

                                Response.End();

                            }

                        }

Replace the above with the below code.

XLWorkbook wb = new XLWorkbook();
wb.Worksheets.Add(dt, "Customers");

Response.Clear();
Response.Buffer = true;
Response.Charset = "";
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
//ذخیره فایل به تاریخ
Response.AddHeader("content-disposition", string.Format("attachment;filename={0}.xls", DateTime.Now.ToString("yyyy-MM-dd")));
//Response.AddHeader("content-disposition", "attachment;filename=SqlExport.xlsx");
using (MemoryStream MyMemoryStream = new MemoryStream())
{
    wb.SaveAs(MyMemoryStream);
    wb.Dispose();
    MyMemoryStream.WriteTo(Response.OutputStream);
    Response.Flush();
    Response.End();
}

Hi dharmendr

I wrote your code but this error happen:

object' does not contain a definition for 'Add' and no extension method 'Add' accepting a first argument of type 'object' could be found (are you missing a using directive or an assembly reference?) Source Error:

Line 157:                        sda.Fill(dt);
Line 158:                        XLWorkbook wb = new XLWorkbook();
Line 159:                        wb.Worksheets.Add(dt, "Customers");

Hi nedash,

Below is the working sample that i have checked with and there is no issue with the code.

using (DataTable dt = new DataTable())
{
    dt.Columns.AddRange(new DataColumn[3] { new DataColumn("Id"), new DataColumn("Name"), new DataColumn("Country") });
    dt.Rows.Add(1, "John Hammond", "United States");
    dt.Rows.Add(2, "Mudassar Khan", "India");
    dt.Rows.Add(3, "Suzanne Mathews", "France");
    dt.Rows.Add(4, "Robert Schidner", "Russia");
    XLWorkbook wb = new XLWorkbook();
    wb.Worksheets.Add(dt, "Customers");

    Response.Clear();
    Response.Buffer = true;
    Response.Charset = "";
    Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
    //ذخیره فایل به تاریخ
    Response.AddHeader("content-disposition", string.Format("attachment;filename={0}.xls", DateTime.Now.ToString("yyyy-MM-dd")));
    using (MemoryStream MyMemoryStream = new MemoryStream())
    {
        wb.SaveAs(MyMemoryStream);
        wb.Dispose();
        MyMemoryStream.WriteTo(Response.OutputStream);
        Response.Flush();
        Response.End();
    }
}

hi dharmendr

see below image it make error on Add ( attribut) for Worksheets

and

I think you are using the latest version of dll. So please refer the below article and download the sample from the below article. In that you will get bin folder in the zip from that use the dll instead of the one you are using.

Export data from SQL Server to Excel in ASP.Net using C# and VB.Net