Is there a down and dirty way to code a button in a page (Im using c# and SQL 2005) that will allow a user to save the contents of a gridview locally?

I have a page that I have working just the way I want it. Now I want to give the user the option to save the data to XL.
I can copy and paste, and it acually looks kinda cool with all the formatting, but I'd be happy to have a button that will ask the user where to dave the file MyGridViewData.xls.

TIA for any help

Dan 

The following will pop-up a File Download window asking the user to Open, Save or Cancel.

In your Source Code - avoid EnableEventValidation
---------------------------------------------------------------------------------
<%@ Page Language="C#"
EnableEventValidation="false"
... />

Your EXPORT button:
---------------------------------------------------------------------------------
<asp:ImageButton ID="imgbtnExport2Excel" runat="server"
 AlternateText="Click to export this to Excel"
      ImageUrl="images/goog_spreadsheets.gif"
      OnClick="imgbtnExport2Excel_Click" />

In your code-behind - react to your [Export] button click
---------------------------------------------------------------------------------

    #region Export 2 Excel Code
   
    private string fileName = "NewHires.xls";

    protected void imgbtnExport2Excel_Click(object sender, ImageClickEventArgs e)
    {
        string style = @"<style> .text { mso-number-format:\@; } </script> ";

        if (GridView1.AllowPaging == true)
        {
            DisableControls(GridView1);
        }

        WriteXLS(style);
    }

    private void WriteXLS(string style)
    {
        Response.ClearContent();
        Response.AddHeader("content-disposition", "attachment; filename="
            + fileName);
        Response.ContentType = "application/excel";

        StringWriter sw = new StringWriter();
        HtmlTextWriter htw = new HtmlTextWriter(sw);

        GridView1.RenderControl(htw);
        // Style is added dynamically
        Response.Write(style);
        Response.Write(sw.ToString());
        Response.End();
    }

    // disables the paging so no data is lost in conversion
    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))
            {
                ((LinkButton)gv.Controls[i]).ForeColor = Color.Black;
                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);
            }

            if (gv.Controls[i].HasControls())
            {
                DisableControls(gv.Controls[i]);
            }
        }
    }

    // override to prevent errors when writing excel document
    public override void VerifyRenderingInServerForm(Control control)
    {

    }

    // set up some fake classes for formatting cell colors in excel
    // ensure your GridView has a RowDataBound event that is handled by this code.
    protected void gv_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            e.Row.Cells[1].Attributes.Add("class", "text");
        }
    }

    #endregion

Hope this helps

Cool tidbit of code you got there. I've seen code for exporting DataSets to Excel spreedsheets, but I don't think I've seen it done by rendering an entire control to one before. Gonna have to test this out. Thanks!

You can try Matt Berseth's export utility:

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

 Pixel-

Thanks, great code.

Two ? if I may

1. what does the #region decliration do?

2.Having a hard time getting it to work with stringwrinter even when I feed it system.Text and System.IO, any thoughts?

Thanks

DAn 

 Nice resource, thank you!

Nice to knowit can be done w/o overriding settings, but at the loss of fromatting. Even trade

You can still format selected column with a little extra work. You can define the style for the column through OnRowDataBound event of the GridView:

protected void GridView1_OnRowDataBound(object sender, GridViewRowEventArgs e)
    {
       // if (e.Row.RowType == DataControlRowType.DataRow || e.Row.RowType == DataControlRowType.Header)
              if (e.Row.RowType == DataControlRowType.DataRow )
        {
            //*****************can change the exported excel file format along with the Utility.ExportExcel function

            e.Row.Cells[1].Attributes.Add("class", "text"); //column 2 format number to textnumber

           e.Row.Cells[2].Attributes.Add("class", "myMoney"); //column 3 format money

           
             //*****************

}

Add two lines of code to the ExportExcel class:

string style = @"<style> .text { mso-number-format:\@;} </style> "; and HttpContext.Current.Response.Write(style);

Here I copy the whole class with these two lines(you can find original: (http://mattberseth.com/downloads/GridViewExportUtil.cs).

   public static void ExportExcel(string myFile, GridView gv)
        {

            HttpContext.Current.Response.Clear();
            HttpContext.Current.Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", myFile));
            HttpContext.Current.Response.ContentType = "application/ms-excel";

string style = @"<style> .text { mso-number-format:\@;} </style> "; 
 
           //string style = @"<style> .text { mso-number-format:\@; background-color: AliceBlue;} .myMoney { mso-number-format:\0022$\0022\#\,\#\#0\.00; }</style> "; 
            //string style = @"<style> .changeBackground { background: transparent;} </style> ";
           
            
            using (StringWriter sw = new StringWriter())
            {
                using (HtmlTextWriter htw = new HtmlTextWriter(sw))
                {
                    //  Create a table to contain the grid
                    Table table = new Table();

                    //  include the gridline settings
                    table.GridLines = gv.GridLines;
                    table.BackColor = System.Drawing.Color.AliceBlue;

                    //  add the header row to the table
                    if (gv.HeaderRow != null)
                    {
                        fishUtility.PrepareControlForExport(gv.HeaderRow);
                        table.Rows.Add(gv.HeaderRow);
                    }

                    //  add each of the data rows to the table
                    foreach (GridViewRow row in gv.Rows)
                    {
                        fishUtility.PrepareControlForExport(row);
                        table.Rows.Add(row);
                    }

                    //  add the footer row to the table
                    if (gv.FooterRow != null)
                    {
                        fishUtility.PrepareControlForExport(gv.FooterRow);
                        table.Rows.Add(gv.FooterRow);
                    }


                   
                    //  render the table into the htmlwriter
                    table.RenderControl(htw);

                    //add style
                    HttpContext.Current.Response.Write(style); 

                    //  render the htmlwriter into the response
                    HttpContext.Current.Response.Write(sw.ToString());
                    HttpContext.Current.Response.End();
                }
            }
        }

Harperator:

 Pixel-

Thanks, great code.

Two ? if I may

1. what does the #region decliration do?

2.Having a hard time getting it to work with stringwrinter even when I feed it system.Text and System.IO, any thoughts?

Thanks

DAn 

Hey Dan,

the #region code is just a visual element which allows you to collapse the code into a region within Visual Studio. Lets you hide it so it doesn't eat up too much room in your code-behind. You can remove #region and #endregion if you like.

Where you able to resolve the issue with the StringBuilder? StringWriter is from System.IO.StringWriter and HtmlTextWriter is from System.Web.UI.HtmlTextWriter

Wil