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
Sharepoint OOB custom list
Displaying Sharepoint custom list items into SPGridView
Displaying Sharepoint custom list items into SPGridView
Exporting SPGridView to Excel Spreadsheet
Exporting SPGridView to Excel Spreadsheet
After exporting SPGridView items in 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);
                }
            }
        }
    }
}


Published by

G Vijai Kumar

Passion towards Microsoft Technologies, Specialized in MOSS