Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

ASP.NET C# Export to Excel

Posted on 2011-02-13
8
Medium Priority
?
606 Views
Last Modified: 2012-05-11
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

0
Comment
Question by:mawingho
  • 3
  • 3
  • 2
8 Comments
 
LVL 39

Accepted Solution

by:
Pratima Pharande earned 1500 total points
ID: 34885829
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
 

Author Comment

by:mawingho
ID: 34885841
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
 
LVL 39

Assisted Solution

by:Pratima Pharande
Pratima Pharande earned 1500 total points
ID: 34885902
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:mawingho
ID: 34885913
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
 
LVL 39

Assisted Solution

by:Pratima Pharande
Pratima Pharande earned 1500 total points
ID: 34885929
0
 
LVL 7

Assisted Solution

by:meispisces
meispisces earned 500 total points
ID: 34886588
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
 
LVL 7

Expert Comment

by:meispisces
ID: 34886597
If my last comment doesn't work out, then refer code written in purple color at http://forums.asp.net/t/936862.aspx
0
 

Author Closing Comment

by:mawingho
ID: 34942465
thanks
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Integration Management Part 2
Suggested Courses

972 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question