Export SPGridView to Excel spreadsheet in Sharepoint 2007

In my last post I have show you how to retrieve current user profile in Sharepoint 2007, before that I have also posted on how to display custom list items in SPGridView

Today I am going to work on how to export SPGridView items into Excel spreadsheet in Sharepoint 2007

Most of the code I have used form Matt Berseth blog article Export GridView to Excel so thanks to Matt and also thanks to my colleague Ram Gowri who helped me on this

Download complete source code

To run the code first you need to create a custom list, name it as Countries, then create two columns int he same list Country and State

Sharepoint OOB custom list

Displaying Sharepoint custom list items into SPGridView

Exporting SPGridView to Excel Spreadsheet

After exporting SPGridView items in to Excel spreadsheet

using System;
using System.IO;
using System.Web;
using System.Runtime.InteropServices;
using System.Web.UI;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Serialization;
using System.Web.UI.WebControls;

using Microsoft.SharePoint;
using Microsoft.SharePoint.WebControls;
using Microsoft.SharePoint.WebPartPages;

namespace ExportGridtoExcel
{
    [Guid("2fa65763-1ef1-4173-8a77-685e840f0196")]
    public class ExportGridtoExcel : System.Web.UI.WebControls.WebParts.WebPart
    {
        SPGridView myGridView;
        SPDataSource myDataSource = new SPDataSource();
        Button oBtn_Export;

        protected override void CreateChildControls()
        {
            oBtn_Export = new Button();
            oBtn_Export.Text = "Export to Excel";
            oBtn_Export.CssClass = "ButtonHeightWidth";
            oBtn_Export.Click += new EventHandler(oBtn_Export_Click);
            this.Controls.Add(oBtn_Export);

            myGridView = new SPGridView();
            myGridView.Enabled = true;
            myGridView.AutoGenerateColumns = false;                       

            SPBoundField colTitle = new SPBoundField();
            colTitle.DataField = "Country";
            colTitle.HeaderText = "Country";
            this.myGridView.Columns.Add(colTitle);

            SPBoundField colMission = new SPBoundField();
            colMission.DataField = "State";
            colMission.HeaderText = "State";
            this.myGridView.Columns.Add(colMission);

            this.Controls.Add(myGridView);
        }

        void oBtn_Export_Click(object sender, EventArgs e)
        {
            ExportToExcel("CountryState.xls", myGridView);
        }

        protected override void OnPreRender(EventArgs e)
        {
            base.OnPreRender(e);
            SPSite mySite = SPContext.Current.Site;
            SPWeb myWeb = SPContext.Current.Web;
            SPList list = myWeb.Lists["Countries"];
            myDataSource.List = list;
            myGridView.DataSource = myDataSource;
            myGridView.DataBind();
        }

        public static void ExportToExcel(string strFileName, SPGridView gv)
        {
            using (StringWriter sw = new StringWriter())
            {
                using (HtmlTextWriter htw = new HtmlTextWriter(sw))
                {
                    //  Create a form to contain the grid
                    Table table = new Table();

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

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

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

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

                    HttpContext.Current.Response.Clear();
                    HttpContext.Current.Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", strFileName));
                    HttpContext.Current.Response.ContentType = "application/ms-excel";
                    HttpContext.Current.Response.Cache.SetCacheability(HttpCacheability.NoCache);
                    //render the htmlwriter into the response
                    HttpContext.Current.Response.Write(sw.ToString());
                    HttpContext.Current.Response.End();
                }
            }
        }

        private static void PrepareControlForExport(Control control)
        {
            for (int i = 0; i < control.Controls.Count; i++)
            {
                Control current = control.Controls[i];
                if (current is LinkButton)
                {
                    control.Controls.Remove(current);
                    control.Controls.AddAt(i, new LiteralControl((current as LinkButton).Text));
                }
                else if (current is ImageButton)
                {
                    control.Controls.Remove(current);
                    control.Controls.AddAt(i, new LiteralControl((current as ImageButton).AlternateText));
                }
                else if (current is HyperLink)
                {
                    control.Controls.Remove(current);
                    control.Controls.AddAt(i, new LiteralControl((current as HyperLink).Text));
                }
                else if (current is DropDownList)
                {
                    control.Controls.Remove(current);
                    control.Controls.AddAt(i, new LiteralControl((current as DropDownList).SelectedItem.Text));
                }
                else if (current is CheckBox)
                {
                    control.Controls.Remove(current);
                    control.Controls.AddAt(i, new LiteralControl((current as CheckBox).Checked ? "True" : "False"));
                }

                if (current.HasControls())
                {
                    PrepareControlForExport(current);
                }
            }
        }
    }
}

Tags: Custom WebpartsSharepoint