asked on

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

raterus

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.

rathiagu

ASKER

Hi raterus thanks for ur solution,let me try with fixing the row width of gridview and update with the result.

rathiagu

ASKER

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.

raterus

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.

rathiagu

ASKER

Thank You rateurs, let me try that.

raterus

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/

rathiagu

ASKER

Thank you thats a nice example page.

rathiagu

ASKER

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

raterus

And that actually spaces out the cells for you?

rathiagu

ASKER

yes it did

raterus

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.

rathiagu

ASKER

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#

raterus

Over my head, sorry..

I'm just a web programmer :-)

rathiagu

ASKER

me too web developer.

raterus

I'd say accept #21816215

Author:rathiagu
Date:06.18.2008 at 02:33PM EST

ASKER CERTIFIED SOLUTION

Computer101

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer