Solved

ASP.NET C# Export to Excel

Posted on 2011-02-13
8
603 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 375 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 375 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

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 375 total points
ID: 34885929
0
 
LVL 7

Assisted Solution

by:meispisces
meispisces earned 125 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
ASP.net VB.net Errors when sending data to SQL 5 20
asp day pilot 3 21
How to call weather service by using asmx? 4 32
InputLanguage 1 27
Today is the age of broadband.  More and more people are going this route determined to experience the web and it’s multitude of services as quickly and painlessly as possible. Coupled with the move to broadband, people are experiencing the web via …
We all know that functional code is the leg that any good program stands on when it comes right down to it, however, if your program lacks a good user interface your product may not have the appeal needed to keep your customers happy. This issue can…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

832 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