Export to Excel

[dorandoran]

I should be getting 580 records but getting none (not even the header) to my excel file.

Button Code

    protected void btnExport_Click(object sender, EventArgs e)
    {
        Export2Excel e2e = new Export2Excel();
        e2e.Prepare4Excel();
    }

Class Code

public class Export2Excel
{
	public Export2Excel()
	{
	
	}    
    public void Prepare4Excel()
    {
        string connStr = ConfigurationManager.ConnectionStrings["myCS"].ConnectionString;
        string strSQL = "Select * from Employees";
        SqlConnection conn = new SqlConnection(connStr);

        SqlCommand command = new SqlCommand(strSQL, conn);
        SqlDataAdapter sda = new SqlDataAdapter(command);
        DataSet ds = new DataSet();
        sda.Fill(ds);
        BindExcel(ds);
        conn.Close();
    }

    public void BindExcel(DataSet ds)
    {
        HttpContext.Current.Response.Clear();
        HttpContext.Current.Response.Charset = "";
        HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";

        StringWriter sw = new StringWriter();
        HtmlTextWriter htw = new HtmlTextWriter(sw);
        DataGrid dg = new DataGrid();
        dg.DataSource = ds.Tables[0];
        dg.AllowPaging = true;
        dg.DataBind();
        dg.RenderBeginTag(htw);
        HttpContext.Current.Response.Write(sw.ToString());
        HttpContext.Current.Response.End();    
    }

}

[dorandoran]

Anyone? cause I am working on a deadline and working on this for 48 hours.

[dorandoran]

I finally got it work. here is the working version of the class that exports to Excel.

public class Export2Excel
{
	public Export2Excel()
	{
	
	}    
    public void Prepare4Excel()
    {
        string connStr = ConfigurationManager.ConnectionStrings["connNorthwind"].ConnectionString;
        string strSQL = "Select * from Employees";
        SqlConnection conn = new SqlConnection(connStr);

        SqlCommand command = new SqlCommand(strSQL, conn);
        SqlDataAdapter sda = new SqlDataAdapter(command);
        DataSet ds = new DataSet();
        sda.Fill(ds);
        BindExcel(ds);
        conn.Close();
    }

    public void BindExcel(DataSet dset)
    {
        HttpContext.Current.Response.Clear();
        HttpContext.Current.Response.Buffer = true;
        HttpContext.Current.Response.AddHeader("content-disposition", "attachment;filename=EmployeeList.xls");
        HttpContext.Current.Response.Charset = "";
        HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";

        StringWriter sw = new StringWriter();
        HtmlTextWriter htw = new HtmlTextWriter(sw);
        DataGrid dg = new DataGrid();
        dg.AllowPaging = false;
        dg.DataSource = dset.Tables[0];
        dg.DataBind();

        dg.RenderControl(htw);
        HttpContext.Current.Response.Output.Write(sw.ToString());
        HttpContext.Current.Response.Flush();
        HttpContext.Current.Response.End();
    }


}

[Frinavale]

I'm glad that you've solved your problem :)
Thanks for sharing your solution, I'm sure it'll help others facing the same problem

[YonngTyler]

Easiest way to export to excel using a c# excel component Spire.XLS, I use it long time, name Spire.XLS, may help to you.