Solved

Export Gridview to Excel sheet

Posted on 2008-06-11
18
1,369 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
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

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

623 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