Export DataSet or DataTable to Excel

Function to get the results in datatable

private DataTable GetData(SqlCommand cmd)
{
    DataTable dt = new DataTable();
    String strConnString = System.Configuration.ConfigurationManager.
         ConnectionStrings["conString"].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);
        return dt;
    }
    catch (Exception ex)
    {
        throw ex;
    }
    finally
    {
        con.Close();
        sda.Dispose();
        con.Dispose();
    }
}

Export to Excel

protected void ExportToExcel(object sender, EventArgs e)
{
    //Get the data from database into datatable
    string strQuery = "select CustomerID, ContactName, City, PostalCode" +
        " from customers";
    SqlCommand cmd = new SqlCommand(strQuery);
    DataTable dt = GetData(cmd);
 
    //Create a dummy GridView
    GridView GridView1 = new GridView();
    GridView1.AllowPaging = false;
    GridView1.DataSource = dt;
    GridView1.DataBind();
 
    Response.Clear();
    Response.Buffer = true;
    Response.AddHeader("content-disposition",
     "attachment;filename=DataTable.xls");
    Response.Charset = "";
    Response.ContentType = "application/vnd.ms-excel";
    StringWriter sw = new StringWriter();
    HtmlTextWriter hw = new HtmlTextWriter(sw);
 
    for (int i = 0; i < GridView1.Rows.Count; i++)
    {
        //Apply text style to each Row
        GridView1.Rows[i].Attributes.Add("class", "textmode");
    }
    GridView1.RenderControl(hw);
 
    //style to format numbers to string
    string style = @"<style> .textmode { mso-number-format:\@; } </style>";
    Response.Write(style);
    Response.Output.Write(sw.ToString());
    Response.Flush();
    Response.End();
}

Source from ASP Snippets

Advertisements

One thought on “Export DataSet or DataTable to Excel

  1. Hey Derek….for a native Excel version to do this in C# here….
    http://kellyschronicles.wordpress.com/2011/07/29/write-data-from-datatable-to-excel-with-an-array-using-c/

    or vb.net
    http://kellyschronicles.wordpress.com/2008/05/16/write-data-to-excel-with-an-array/

    Love what you are doing here though…..writing as HTML I have done before. I had abandoned it though cause naturally users wanted more excel type functionality then I could do in HTML…..this is a good way to do it.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s