Exporting List or Library in Excel Report using custom web part

Exporting List or Library in Excel Report using custom web part

To export the list or library in excel sheet, we have to do the following points:

- pull the items from list or library and Drop it in grid view.

- create a button for Click event to export the Excel sheet

- create override render function for Exporting the Excel sheet

* Pull the items from list or library and Drop it in grid view

C# Code To display The Library & List Items:

SPWeb objWeb;

SPSite objSpSite;

SPList oList;

objSpSite = SPContext.Current.Site;

objWeb = objSpSite.OpenWeb();

oList = objWeb.Lists["GBMListNew"];

SPView objView = oList.Views["All Documents"];

SPListItemCollection collItem = oList.GetItems(objView);

DataTable dtSponsorships = collItem.GetDataTable();

gvGBMFormLib.DataSource = dtSponsorships;

gvGBMFormLib.DataBind();

Client Side Code:

<asp:GridView ID="gvGBMFormLib" runat="server" Visible ="true" >

</asp:GridView>

* create a button for Click event to export the Excel sheet

<asp:Button ID="btnexcel" runat="server" Text="Export to Excel"

                CssClass="ms-ButtonHeightWidth" Visible="true" onclick="btnexcel_Click" />

  

* create override render function for exporting the Excel sheet

Declare bExport as global variable (Eg: private bool bExport = false)

Add the code in server Side:

protected override void Render(HtmlTextWriter writer)

        {

            if (Page != null)

            {

                if (bExport)

                {

                    string stFileName = "AmexSummaryReport_";

                    gvGBMFormLib.Visible = true;

                    this.bExport = false;

                    Page.Response.Clear();

                    Page.Response.Buffer = true;

                    Page.Response.ContentType = "application/ms-excel";

                    Page.Response.AddHeader("content-disposition", "attachment; filename=" + stFileName + DateTime.Now + ".xls");

                    Page.Response.Charset = "UTF-8";

                    DateTime ct = DateTime.Now;

                    string currentdate = Convert.ToString(ct);

                    HttpContext.Current.Response.Write("<b><h1 style='Text-align:left; padding-left:65px; color: #008000; font-weight: bold; font-style: normal; font-size: large'>GABM Full Excel Summary</b></h1>");

                    this.EnableViewState = false;

                    System.IO.StringWriter sw = new System.IO.StringWriter();

                    System.Web.UI.HtmlTextWriter htw = new System.Web.UI.HtmlTextWriter(sw);

                    gvGBMFormLib.RenderControl(htw);

                    Page.Response.Write(sw.ToString() + "<br/><br/><b>Report Date : " + currentdate + "</b>");

                    Page.Response.End();

                }

            }

         

            base.Render(writer);

        }