ASP.NET C# Export to Excel

I use the following code to export things to excel
however, my gridview enabled paging...
so the my code can only export the currernt page..
but I want to export all data...
how can I do that?

private void ExportGridView()
        {
            string attachment = "attachment; filename=RiskRating.xls";
            Response.ClearContent();
            Response.AddHeader("content-disposition", attachment);
            Response.ContentType = "application/ms-excel";
            StringWriter sw = new StringWriter();
            HtmlTextWriter htw = new HtmlTextWriter(sw);

            // Create a form to contain the grid
            HtmlForm frm = new HtmlForm();
            GridView1.Parent.Controls.Add(frm);
            frm.Attributes["runat"] = "server";
            frm.Controls.Add(GridView1);

            frm.RenderControl(htw);
            //GridView1.RenderControl(htw);
            Response.Write(sw.ToString());
            Response.End();
        }

        private void PrepareGridViewForExport(Control gv)
        {
            LinkButton lb = new LinkButton();
            Literal l = new Literal();
            string name = String.Empty;

            for (int i = 0; i < gv.Controls.Count; i++)
            {
                if (gv.Controls[i].GetType() == typeof(Label))
                {
                    l.Text = (gv.Controls[i] as Label).Text;
                    gv.Controls.Remove(gv.Controls[i]);
                    gv.Controls.AddAt(i, l);
                }
                else if (gv.Controls[i].GetType() == typeof(TextBox))
                {
                    l.Text = (gv.Controls[i] as TextBox).Text;
                    gv.Controls.Remove(gv.Controls[i]);
                    gv.Controls.AddAt(i, l);
                }
                else if (gv.Controls[i].GetType() == typeof(LinkButton))
                {
                    l.Text = (gv.Controls[i] as LinkButton).Text;
                    gv.Controls.Remove(gv.Controls[i]);
                    gv.Controls.AddAt(i, l);
                }
                else if (gv.Controls[i].GetType() == typeof(DropDownList))
                {
                    l.Text = (gv.Controls[i] as DropDownList).SelectedItem.Text;
                    gv.Controls.Remove(gv.Controls[i]);
                    gv.Controls.AddAt(i, l);
                }
                else if (gv.Controls[i].GetType() == typeof(CheckBox))
                {
                    l.Text = (gv.Controls[i] as CheckBox).Checked ? "True" : "False";
                    gv.Controls.Remove(gv.Controls[i]);
                    gv.Controls.AddAt(i, l);
                }

                if (gv.Controls[i].HasControls())
                {
                    PrepareGridViewForExport(gv.Controls[i]);
                }
            }
        }

Open in new window

mawinghoAsked:
Who is Participating?
 
Pratima PharandeConnect With a Mentor Commented:
you can directly export Dataset ( which you are binding to Grid) to excel which contain all data

See teh link
http://geekswithblogs.net/VROD/archive/2008/04/20/121433.aspx
0
 
mawinghoAuthor Commented:
is it possible to get the dataset from the gridview?
because I do not wanna make the ds as a global variable....

and I don't wanna do all the query again to create another dataset..

Is there any way can get the dataset out of the gridview immediately?
0
 
Pratima PharandeConnect With a Mentor Commented:
I don't think it is possible.
You need to get dataset gain here
do one thing put bindcode in one function asn return dataset from that function
so at this stage you need to call function and get dataset
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
mawinghoAuthor Commented:
oh i see,
How should I modify the code?

I don't think I can use HTMLForm and HtmlTextWriter....

private void ExportGridView()
        {
            string attachment = "attachment; filename=RiskRating.xls";
            Response.ClearContent();
            Response.AddHeader("content-disposition", attachment);
            Response.ContentType = "application/ms-excel";
            StringWriter sw = new StringWriter();
            HtmlTextWriter htw = new HtmlTextWriter(sw);

            // Create a form to contain the grid
            HtmlForm frm = new HtmlForm();
            GridView1.Parent.Controls.Add(frm);
            frm.Attributes["runat"] = "server";
            frm.Controls.Add(GridView1);

            frm.RenderControl(htw);
            //GridView1.RenderControl(htw);
            Response.Write(sw.ToString());
            Response.End();
        }

Open in new window

0
 
meispiscesConnect With a Mentor Commented:
turn off paging and rebind the grid.

See the sample here: http://mattberseth.com/blog/2007/04/export_gridview_to_excel_1.html

Hope this helps!
0
 
meispiscesCommented:
If my last comment doesn't work out, then refer code written in purple color at http://forums.asp.net/t/936862.aspx
0
 
mawinghoAuthor Commented:
thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.