Export Gridview to Excel sheet

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
rathiaguAsked:
Who is Participating?
 
Computer101Commented:
PAQed with points refunded (125)

Computer101
EE Admin
0
 
raterusCommented:
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
 
rathiaguAuthor Commented:
Hi raterus thanks for ur solution,let me try with fixing the row width of gridview and update with the result.
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.

 
rathiaguAuthor Commented:
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
 
raterusCommented:
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
 
rathiaguAuthor Commented:
Thank You rateurs, let me try that.
0
 
raterusCommented:
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
 
rathiaguAuthor Commented:
Thank you thats a nice example page.
0
 
rathiaguAuthor Commented:
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
 
raterusCommented:
And that actually spaces out the cells for you?
0
 
rathiaguAuthor Commented:
yes it did
0
 
raterusCommented:
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
 
rathiaguAuthor Commented:
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
 
raterusCommented:
Over my head, sorry..

I'm just a web programmer :-)
0
 
rathiaguAuthor Commented:
me too web developer.
0
 
raterusCommented:
I'd say accept #21816215

Author:rathiagu
Date:06.18.2008 at 02:33PM EST
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.