CODEWITHSUNDEEP
c#asp.netgridviewexport-to-excel
Ankita
Ankita

Reputation: 83

export the gridview to excel sheet by clicking on submit button

I am creating a c# application. In this application I take inputs from user and using those entries I fire a sql query and dispay the results in the Gridview. So these actions happen when we click on submit button. After this, I want to give the user an option to export the gridview results to an excel sheet by clicking on another submit button. the code for these things is:

aspx code:

     <body>
        <form id="form1" runat="server">
            <div>
                <b>Enter Value 1 :</b>
                <asp:TextBox ID="p1" runat="server" />
                <br />

                <b>Enter value 2 :</b>
                <asp:TextBox ID="p2" runat="server" /><br />

                <asp:Button ID="btn1" runat="server" OnClick="Button1_Click" Text="Start Search" />
                 <asp:Button ID="btn2" runat="server" OnClick="Button2_Click" Text="Export Data to Excel" />
                <hr />

                        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="true"
                            OnPageIndexChanging="gridView_PageIndexChanging"
                            ShowFooter="false"
                            CssClass="gridstyle"
                            EnableViewState="false"
                            AllowPaging="true">
                            <AlternatingRowStyle CssClass="altrowstyle" />
                            <HeaderStyle CssClass="headerstyle" />
                            <RowStyle CssClass="rowstyle" />
                            <RowStyle Wrap="false" />
                            <HeaderStyle Wrap="false" />
                        </asp:GridView>
            </div>
        </form>
    </body>

The code file for this code is:

    public partial class pSearch : System.Web.UI.Page
    {
    SqlConnection sqlconn = new  SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString);
    DataSet dsldata;
    protected void Page_Load(object sender, EventArgs e)
    {

    }


    protected void Button2_Click(object sender, EventArgs e)
    {
        DataSet ds0 = new DataSet();
        ds0 = (DataSet)Session["data"]; 
        DataView dataview_ldata = dsldata.Tables[0].DefaultView;
        DataTable dt = dsldata.Tables[0];
        GridView1.DataSource = dataview_ldata;
        GridView1.DataBind();
        ExportToExcel(GridView1);

    }



    private void ExportToExcel(GridView GrdView)
    {
        try
        {
            Response.Clear();
            Response.AddHeader("content-disposition", "attachment; filename=FileName.xls");
            Response.Charset = "";
            // If you want the option to open the Excel file without saving than
            // comment out the line below
            // Response.Cache.SetCacheability(HttpCacheability.NoCache);
            Response.ContentType = "application/vnd.xls";
            System.IO.StringWriter stringWrite = new System.IO.StringWriter();
            System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
            GrdView.RenderControl(htmlWrite);
            Response.Write(stringWrite.ToString());
            Response.End();
        }
        catch (Exception ex)
        {
            Response.Write("<script>alert('" + ex.Message + "')</script>");
        }
    }



    protected void Button1_Click(object sender, EventArgs e)
        {

        string RName = Page.Request.QueryString["RName"];
        string typeofquery = "mycommand";
        string pv1 = p1.Text ;
        string pv2 = p2.Text ;
        string abc = null;
        abc = "" + typeofquery + " @RName=" + RName + ",@P1='" + pv1 + "',@P2='" + pv2 + "'";
        SqlDataAdapter cmdldata = new SqlDataAdapter(abc, sqlconn);

        GridView1.PageSize = 1000;


        cmdldata.SelectCommand.CommandTimeout = 600;
         dsldata = new DataSet();
        ErrorHandling errhandle = new ErrorHandling();
        try
        {
            cmdldata.Fill(dsldata);
            Session["data"] = dsldata;

            DataView dataview_ldata = dsldata.Tables[0].DefaultView;
            DataTable dt = dsldata.Tables[0];
            GridView1.DataSource = dataview_ldata;
            GridView1.DataBind();

        }//end of try
        catch (Exception ex)
        {
            String errorMessage = errhandle.displayException(ex);
            Response.Write(errorMessage);

        }//end of catch
        finally
        {
            if (errhandle != null)
            {
                errhandle = null;
            }
        }//end of finally
    }

      public override void VerifyRenderingInServerForm(Control control)
    {

        // Confirms that an HtmlForm control is rendered for the specified ASP.NET server control at run time.

    }

    protected void gridView_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        GridView1.PageIndex = e.NewPageIndex;
        GridView1.DataBind();
    }

    }

I am getting an empty excel sheet right now as my output.

From my anaysis I think the following is the problem: When button 1 is clicked Gridview is generated by the query.When button 2 is clicked , all the data that It had previously is lost (even though we can still see it on the screen).That is why I am getting an empty excel sheet right now.

Secondly,Since I am creating the gridview on button click and not on the page load,the data of the gridview is not available in the protected void Button2_Click(object sender, EventArgs e){} function.

Only option now to make it working is I should do the same operations of executing the query again on button2_Click function also.

But again I don't think this is an efficient way.And moreover I am running a complex query that may take 3 -4 mins at times to give the output.So running the query two times is out of question. I also tried caching the dataset but it did not work.Even creating session in not working.

I have been breaking my head on this since last 1 day. Please help me! Thank you.

Upvotes: 0

Views: 4838

Answers (1)

Garrison Neely
Garrison Neely

Reputation: 3289

Cache sounds like a good solution, could you post your cache code?

Otherwise, I would do it in ViewState. Get the result of your long query, store it in ViewState, bind the result to the GridView, then after your button click, you can access the ViewState-stored data. This isn't the best way to do it for large sets of data, because ViewState is sent across the wire with each PostBack and stored on the client side. It can really slow your application down and cause unexpected errors.

Here's my edit, that I got working locally:

Storing it in Session should work fine. Here's what I did with some dummy data. FYI, you can bind a GridView directly to a DataSet, you don't have to drill down to a DataTable or anything like that.

        protected void Button1_Click(object sender, EventArgs e)
        {

            string RName = Page.Request.QueryString["RName"];
            string typeofquery = "mycommand";
            string pv1 = p1.Text;
            string pv2 = p2.Text;
            string abc = null;
            abc = "" + typeofquery + " @RName=" + RName + ",@P1='" + pv1 + "',@P2='" + pv2 + "'";
            SqlDataAdapter cmdldata = new SqlDataAdapter(abc, sqlconn);

            GridView1.PageSize = 1000;


            cmdldata.SelectCommand.CommandTimeout = 600;

            var dummyDt = new DataTable();
            dummyDt.Columns.Add("Sup");
            dummyDt.Columns.Add("Bro");

            dummyDt.Rows.Add("Test1", "test2");
            dummyDt.Rows.Add("Test1", "test2");
            dummyDt.Rows.Add("Test1", "test2");
            dummyDt.Rows.Add("Test1", "test2");
            dummyDt.Rows.Add("Test1", "test2");

            dsldata = new DataSet();
            dsldata.Tables.Add(dummyDt);
            //ErrorHandling errhandle = new ErrorHandling();
            try
            {
                //cmdldata.Fill(dsldata);
                Session["data"] = dsldata;

                //DataView dataview_ldata = dsldata.Tables[0].DefaultView;
                //DataTable dt = dsldata.Tables[0];
                GridView1.DataSource = dsldata;
                GridView1.DataBind();

            }//end of try
            catch (Exception ex)
            {
                //String errorMessage = errhandle.displayException(ex);
                Response.Write(ex.Message);

            }//end of catch
            finally
            {
                //if (errhandle != null)
                //{
                //    errhandle = null;
                //}
            }//end of finally
        }

        protected void Button2_Click(object sender, EventArgs e)
        {
            //DataSet ds0 = new DataSet();
            //ds0 = ;
            //DataView dataview_ldata = dsldata.Tables[0].DefaultView;
            //DataTable dt = dsldata.Tables[0];
            GridView1.DataSource = (DataSet)Session["data"];
            GridView1.DataBind();
            ExportToExcel(GridView1);

        }

        private void ExportToExcel(GridView GrdView)
        {
            try
            {
                Response.Clear();
                Response.AddHeader("content-disposition", "attachment; filename=FileName.xls");
                Response.Charset = "";
                // If you want the option to open the Excel file without saving than
                // comment out the line below
                // Response.Cache.SetCacheability(HttpCacheability.NoCache);
                Response.ContentType = "application/vnd.xls";
                System.IO.StringWriter stringWrite = new System.IO.StringWriter();
                System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
                GrdView.RenderControl(htmlWrite);
                Response.Write(stringWrite.ToString());
                Response.End();
            }
            catch (Exception ex)
            {
                Response.Write("<script>alert('" + ex.Message + "')</script>");
            }
        }

Upvotes: 1

Related Questions