exporting gridviews to excel into 3 worksheets

Question

From my website, I have a button which calls a method and then generates reports to excel. the code works. The problem is that the code displays all gridviews (there are 3) on the same tab. What code may I add to this so that when i click on the button, it will download the document but have each gridview displayed on their separate tabs. The tab names will be top 1, top 2, and top 3. So to clarify, right now all of the gridviews are displayed on the top 1 tab, I need to make 2 more tabs (top 2 and top 3) and put gridview2 on top 2 tab, and gridview3 on the top 3 tab. There is only 1 data set. What code could i use to loop it so that it displays each table into different worksheets?

public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
    DataSet dataSet = new DataSet();

    SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ISALog1ConnectionString"].ToString());

    SqlCommand cmd = new SqlCommand("exec ProxyReport", conn);
    cmd.CommandTimeout = 200;

    SqlDataAdapter ad = new SqlDataAdapter(cmd);
    ad.Fill(dataSet);

    GridView1.DataSource = dataSet.Tables[0];
    GridView1.DataBind();
    GridView2.DataSource = dataSet.Tables[1];
    GridView2.DataBind();
    GridView3.DataSource = dataSet.Tables[2];
    GridView3.DataBind();

}
protected void Button1_Click(object sender, EventArgs e)
{
    string attachment = "attachment; filename=Top 1.xls";
    Response.ClearContent();
    Response.AddHeader("content-disposition", attachment);
    Response.Cache.SetCacheability(HttpCacheability.NoCache);
    Response.ContentType = "application/ms-excel";
    StringWriter sw = new StringWriter();
    HtmlTextWriter htw = new HtmlTextWriter(sw);
    GridView1.RenderControl(htw);
    GridView2.RenderControl(htw);
    GridView3.RenderControl(htw);
    Response.Write(sw.ToString());
    Response.End();
}
public override void VerifyRenderingInServerForm(Control control)
{

}


}

I don't know the exact answer offhand, but I would imagine it would involve something like wrapping the output of each `GridView.RenderControl` in a `` tag. And unless I'm missing something, shouldn't the output be an `.xlsx` file? — lc., Jul 12 '12 at 16:16
Yes, it should be an xlsx. but im thinking i need to change alot of code with that.. and adding some kind of loop code. and replace the contents of the button1_click method.. i just don't know how.. — Cloud, Jul 12 '12 at 16:29

score 0 · Accepted Answer · edited May 23 '17 at 12:12

0

Looks like a duplicate of Export GridView to multiple Excel sheet. It seems that you will be looking into interops though.

Good luck.

edited May 23 '17 at 12:12

Community

1
1

answered Jul 12 '12 at 21:39

Hanlet Escaño

17,114
8
52
75

exporting gridviews to excel into 3 worksheets

1 Answers1