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

x
?
Solved

Export Gridview to Excel sheet

Posted on 2008-06-11
18
Medium Priority
?
1,374 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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
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
 
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
Hello there! As a developer I have modified and refactored the unit tests which was written by fellow developers in the past. On the course, I have gone through various misconceptions and technical challenges when it comes to implementation. I would…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

885 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