Solved

Export Gridview to Excel sheet

Posted on 2008-06-11
18
1,346 Views
Last Modified: 2013-11-07
I used the below code to export two gridviews on a button click  to excel sheet.the code was working fine and the only problem i had was the contents doesnot autofit inside excel sheet.i have attached the code in code snippet and the output excel sheet is attached as file
protected void btn1_Click(object sender, EventArgs e)

    {

DisableControls(GridView1);

DisableControls(GridView2);

      

        Response.AddHeader("content-disposition", "attachment;filename=FileName.xls");

        Response.Charset = String.Empty; Response.ContentType = "application/vnd.ms-excel";
 

        System.IO.StringWriter sw = new System.IO.StringWriter();

        System.Web.UI.HtmlTextWriter hw = new HtmlTextWriter(sw);

        GridView1.RenderControl(hw);

        Response.Write(sw.ToString());
 

        if (GridView2.Visible == true)

        {

            System.IO.StringWriter sw1 = new System.IO.StringWriter();

            System.Web.UI.HtmlTextWriter hw1 = new HtmlTextWriter(sw1);

            GridView2.RenderControl(hw1);

            Response.Write(sw1.ToString());
 

        }

        Response.End();

}
 
 

private void DisableControls(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(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(Button))

            {

                gv.Controls.Remove(gv.Controls[i]);

            }
 
 

            if (gv.Controls[i].HasControls())

            {
 

                DisableControls(gv.Controls[i]);
 

            }
 

        }
 

    }

Open in new window

FileName-1-.xls
Gridviewoutput.doc
0
Comment
Question by:rathiagu
  • 8
  • 7
18 Comments
 
LVL 33

Expert Comment

by:raterus
ID: 21764029
If it's a display issue with Excel, there really isn't much you can do to change it.  You might get lucky by setting the width to a fixed amount in your gridview, but I doubt that will help.  To get full access to Excel you need to use the Microsoft Office objects, and even then, these aren't recommended to be ran on the server as the require Microsoft Office to be installed on your server as well.

0
 

Author Comment

by:rathiagu
ID: 21764050
Hi raterus thanks for ur solution,let me try with fixing the row width of gridview and update with the result.
0
 

Author Comment

by:rathiagu
ID: 21769212
hi rateurs i increased the width of gridview rows using
<RowStyle BackColor="#F7F7DE" Font-Names="Verdana" Font-Size="11px" Width="200"/>
                    <SelectedRowStyle BackColor="#CE5D5A" ForeColor="White" Font-Names="Verdana" Font-Size="11px" Width="200" />

but nothing seems to change in the output.
0
 
LVL 33

Expert Comment

by:raterus
ID: 21778339
Like I said, that was just a shot in the dark.  If you need fine-tuned control over the excel file, you'll have to write it using the Microsoft Excel Object Library objects on a webserver that has Microsoft Excel installed.
0
 

Author Comment

by:rathiagu
ID: 21778384
Thank You rateurs, let me try that.
0
 
LVL 33

Expert Comment

by:raterus
ID: 21778410
Here's a quick example in C#.  I haven't exported data like this before, just know it can be done, so unfortunately this is about all the help I can give you on this!

http://support.microsoft.com/kb/306023/
0
 

Author Comment

by:rathiagu
ID: 21778463
Thank you thats a nice example page.
0
 

Author Comment

by:rathiagu
ID: 21816215
Hi rateurs i found the solution using the below link

http://mattberseth.com/blog/2007/04/export_gridview_to_excel_1.html

i just added the gridview style to excel sheet using
 table.GridLines = GridView1.GridLines;
table.ApplyStyle(GridView1.RowStyle);

Thanks for our efforts and help
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 33

Expert Comment

by:raterus
ID: 21817094
And that actually spaces out the cells for you?
0
 

Author Comment

by:rathiagu
ID: 21817140
yes it did
0
 
LVL 33

Expert Comment

by:raterus
ID: 21817148
Wow, guess you learn something new every day!  I'll have to remember exporting as an HTML table next time I do it, seems like you can have much more control over the output that way.
0
 

Author Comment

by:rathiagu
ID: 21817173
yes i learnt a lot but i think the problem which i had was not faced by anyone else who used render control.now i am struggling with SNMP polling to device using c#
0
 
LVL 33

Expert Comment

by:raterus
ID: 21817197
Over my head, sorry..

I'm just a web programmer :-)
0
 

Author Comment

by:rathiagu
ID: 21818586
me too web developer.
0
 
LVL 33

Expert Comment

by:raterus
ID: 21998175
I'd say accept #21816215

Author:rathiagu
Date:06.18.2008 at 02:33PM EST
0
 
LVL 1

Accepted Solution

by:
Computer101 earned 0 total points
ID: 22022200
PAQed with points refunded (125)

Computer101
EE Admin
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

705 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now