locked
Export to excel gridview image button is not firing at first time in sharepoint 2010 RRS feed

  • Question

  • Hi,

    I have gridview i want to export gridview data into excel for that i am using image button.My issue is when i click first time on image button to export gridview to excel.Image button event is not firing at first time.Second time it is firing.

    Please help me to resolve this issue.

    Sunday, May 15, 2016 6:27 AM

Answers

  • Hi,

    I modify the code as below:

    InventoryByCycleUserControl.ascx

    <%@ Assembly Name="$SharePoint.Project.AssemblyFullName$" %>
    <%@ Assembly Name="Microsoft.Web.CommandUI, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>
    <%@ Register TagPrefix="SharePoint" Namespace="Microsoft.SharePoint.WebControls"
        Assembly="Microsoft.SharePoint, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>
    <%@ Register TagPrefix="Utilities" Namespace="Microsoft.SharePoint.Utilities" Assembly="Microsoft.SharePoint, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>
    <%@ Register TagPrefix="asp" Namespace="System.Web.UI" Assembly="System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" %>
    <%@ Import Namespace="Microsoft.SharePoint" %>
    <%@ Register TagPrefix="WebPartPages" Namespace="Microsoft.SharePoint.WebPartPages"
        Assembly="Microsoft.SharePoint, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>
    <%@ Control Language="C#" AutoEventWireup="true" CodeBehind="InventoryByCycleUserControl.ascx.cs"
        Inherits="BackupManagementSystem.WebParts.InventoryByCycle.InventoryByCycleUserControl" %>
    
    <script type="text/javascript" language="javascript">
    
        //sharepoint postback to work after clicking on telerik export
        if (typeof (_spBodyOnLoadFunctionNames) != 'undefined' && _spBodyOnLoadFunctionNames != null) {
            _spBodyOnLoadFunctionNames.push("supressSubmitWraper");
        }
    
        function supressSubmitWraper() {
            _spSuppressFormOnSubmitWrapper = true;
        }
        
    </script>
    <div id="divExport" runat="server">
        <asp:ImageButton ID="btnExportToExcel" Visible="true" runat="server" OnClick="btnExportToExcel_Click"
            AlternateText="Export" />
    </div>
    <div id="ListView">
        <table>
            <tr>
                <td>
                    <SharePoint:SPGridView Width="100%" ID="grdInventoryByCycle" CellSpacing="2" runat="server"
                        AutoGenerateColumns="false" AllowPaging="true" PageSize="2" OnPageIndexChanging="grdInventoryByCycle_OnPageIndexChanging">
                        <Columns>
                            <SharePoint:SPBoundField HeaderStyle-BackColor="#BDC5D7" HeaderText="Label Number"
                                DataField="Label Number" HeaderStyle-HorizontalAlign="Center" ItemStyle-HorizontalAlign="Left">
                            </SharePoint:SPBoundField>
                            <asp:BoundField HeaderStyle-BackColor="#BDC5D7" HeaderText="Description" HtmlEncode="false"
                                HeaderStyle-HorizontalAlign="Center" DataField="Description" ItemStyle-HorizontalAlign="Left" />
                            <SharePoint:SPBoundField HeaderStyle-BackColor="#BDC5D7" HeaderText="Sequence Number"
                                HeaderStyle-HorizontalAlign="Center" DataField="Sequence Number" ItemStyle-HorizontalAlign="Left">
                            </SharePoint:SPBoundField>
                            <SharePoint:SPBoundField HeaderStyle-BackColor="#BDC5D7" HeaderText="Backup Date"
                                HeaderStyle-HorizontalAlign="Center" DataField="Backup Date" ItemStyle-HorizontalAlign="Left">
                            </SharePoint:SPBoundField>
                            <SharePoint:SPBoundField HeaderStyle-BackColor="#BDC5D7" HeaderText="Address" HeaderStyle-HorizontalAlign="Center"
                                DataField="Address" ItemStyle-HorizontalAlign="Left">
                            </SharePoint:SPBoundField>
                            <SharePoint:SPBoundField HeaderStyle-BackColor="#BDC5D7" HeaderText="Media Type"
                                HeaderStyle-HorizontalAlign="Center" DataField="Media Type" ItemStyle-HorizontalAlign="Left">
                            </SharePoint:SPBoundField>
                        </Columns>
                    </SharePoint:SPGridView>
                    <asp:ObjectDataSource ID="gdrsDataSource" runat="server"></asp:ObjectDataSource>
                </td>
            </tr>
        </table>
    </div>
    <div id="mainDiv" runat="server" style=">
    </div>
    

    InventoryByCycleUserControl.ascx.cs

    using System;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    using System.Web.UI.WebControls.WebParts;
    
    using System.Data;
    using Microsoft.SharePoint;
    using System.Web;
    using System.IO;
    using Microsoft.SharePoint.Utilities;
    using System.Text.RegularExpressions;
    using System.Text;
    
    namespace BackupManagementSystem.WebParts.InventoryByCycle
    {
        public partial class InventoryByCycleUserControl : UserControl
        {
            DataTable dtInventory = new DataTable();
            StringBuilder sb = new StringBuilder();
    
            protected sealed override void LoadViewState(object savedState)
            {
                try
                {
                    base.LoadViewState(savedState);
    
                    if (Context.Request.Form["__EVENTARGUMENT"] != null &&
                       Context.Request.Form["__EVENTARGUMENT"].EndsWith("__ClearFilter__"))
                    {
                        // Clear FilterExpression
                        ViewState.Remove("FilterExpression");
                    }
                }
                catch (Exception ex)
                {
                    ex.Message.ToString();
                }
            }
    
    
            protected void Page_Load(object sender, EventArgs e)
            {
                grdInventoryByCycle.DataSourceID = gdrsDataSource.ID;
                gdrsDataSource.SelectMethod = "BindGrid";
                gdrsDataSource.TypeName = this.GetType().AssemblyQualifiedName;
                gdrsDataSource.ObjectCreating += new ObjectDataSourceObjectEventHandler(gdrsDataSource_ObjectCreating);
    
                //Filtering
                gdrsDataSource.Filtering += new ObjectDataSourceFilteringEventHandler(gdrsDataSource_Filtering);
                grdInventoryByCycle.AllowFiltering = true;
                grdInventoryByCycle.FilterDataFields = "Label Number,Description,Sequence Number,Backup Date,Address,Media Type";
                grdInventoryByCycle.FilteredDataSourcePropertyName = "FilterExpression";
                //grdInventoryByCycle.FilteredDataSourcePropertyFormat = "{1} = '{0}'";
                grdInventoryByCycle.FilteredDataSourcePropertyFormat = "[{1}] LIKE '{0}'";
    
                grdInventoryByCycle.PagerTemplate = null;
    
                //Sorting
                grdInventoryByCycle.AllowSorting = true;
                grdInventoryByCycle.Sorting += new GridViewSortEventHandler(grdInventoryByCycle_Sorting);
    
                //btnExportToExcel.ImageUrl = SPContext.Current.Web.Url + "/Style%20Library/BMS/Images/Excel-icon.png";
                if (!IsPostBack)
                {
                    mainDiv.InnerHtml = getHtmlFormat();
                }
            }
    
            public DataTable BindGrid()
            {
                try
                {
                    SPSecurity.RunWithElevatedPrivileges(delegate()
                    {
                        using (SPSite site = new SPSite(SPContext.Current.Site.ID))
                        {
                            using (SPWeb web = site.OpenWeb())
                            {
                                #region Datatable creation
    
                                dtInventory.Columns.Add("Label Number", typeof(string));
                                dtInventory.Columns.Add("Description", typeof(string));
                                dtInventory.Columns.Add("Sequence Number", typeof(string));
                                dtInventory.Columns.Add("Backup Date", typeof(string));
                                dtInventory.Columns.Add("Address", typeof(string));
                                dtInventory.Columns.Add("Media Type", typeof(string));
    
                                #endregion
    
                                SPList list = web.Lists.TryGetList("Tape Inventory");
    
                                if (list != null)
                                {
                                    SPListItemCollection items = list.GetItems();
    
                                    foreach (SPListItem item in items)
                                    {
                                        DataRow dr = dtInventory.NewRow();
                                        dr["Label Number"] = item["Title"].ToString();
                                        dr["Description"] = Regex.Replace(item["Description"].ToString(), "<.*?>", string.Empty);
                                        //dr["Description"] = item["Description"].ToString();
                                        dr["Sequence Number"] = item["Batch_x0020_Number"].ToString();
                                        dr["Backup Date"] = Convert.ToDateTime(item["Backup_x0020_Date"]).ToShortDateString();
                                        dr["Address"] = item["Address"].ToString();
                                        dr["Media Type"] = item["Media_x0020_Type"].ToString();
    
                                        dtInventory.Rows.Add(dr);
    
                                    }
                                }
                            }
                        }
                    });
                }
                catch (Exception ex)
                {
                    ex.Message.ToString();
                }
                if (dtInventory.Rows.Count > 0)
                {
                    btnExportToExcel.Visible = true;
                    return dtInventory;
                }
                else
                {
                    btnExportToExcel.Visible = false;
                    grdInventoryByCycle.EmptyDataText = "No Records found based on the selection..";
                    return null;
                }
            }
    
            protected void gdrsDataSource_ObjectCreating(object sender, ObjectDataSourceEventArgs e)
            {
                e.ObjectInstance = this;
            }
    
            private void gdrsDataSource_Filtering(object sender, ObjectDataSourceFilteringEventArgs e)
            {
                ViewState["FilterExpression"] = ((ObjectDataSourceView)sender).FilterExpression;
            }
    
            protected void grdInventoryByCycle_Sorting(object sender, GridViewSortEventArgs e)
            {
                try
                {
                    if (ViewState["FilterExpression"] != null)
                    {
                        gdrsDataSource.FilterExpression = (string)ViewState["FilterExpression"];
                    }
                }
                catch (Exception ex)
                {
                    ex.Message.ToString();
                }
            }
    
            protected void grdInventoryByCycle_OnPageIndexChanging(object sender, GridViewPageEventArgs e)
            {
                try
                {
                    grdInventoryByCycle.PageIndex = e.NewPageIndex;
    
                    //bind grid with the data
                    if (ViewState["FilterExpression"] != null)
                    {
                        gdrsDataSource.FilterExpression = (string)ViewState["FilterExpression"];
                    }
                    grdInventoryByCycle.DataBind();
                }
                catch (Exception ex)
                {
                    ex.Message.ToString();
                    throw;
                }
            }
    
            protected void btnExportToExcel_Click(object sender, ImageClickEventArgs e)
            {
                HttpContext.Current.Response.ClearContent();
                HttpContext.Current.Response.ClearHeaders();
                string attachment = "attachment; filename=InventoryByCycle" + "_" + DateTime.Now.ToShortDateString() + ".xls";
                HttpContext.Current.Response.AddHeader("content-disposition", attachment);
                HttpContext.Current.Response.ContentType = "application/Excel";
                StringWriter sw = new StringWriter();
                HtmlTextWriter htw = new HtmlTextWriter(sw);
    
                StringWriter sw1 = new StringWriter();
                HtmlTextWriter htw1 = new HtmlTextWriter(sw1);
    
                StringWriter sw2 = new StringWriter();
                HtmlTextWriter htw2 = new HtmlTextWriter(sw2);
    
                StringWriter sw3 = new StringWriter();
                HtmlTextWriter htw3 = new HtmlTextWriter(sw3);
    
                GridView spGridToExport = new GridView();
                spGridToExport.DataSource = gdrsDataSource;
                spGridToExport.DataBind();
                spGridToExport.HeaderRow.Style.Add("background-color", "#FFFFFF");
                foreach (TableCell tableCell in spGridToExport.HeaderRow.Cells)
                {
                    tableCell.Style["background-color"] = "#BDA65A";
                }
                foreach (GridViewRow gridViewRow in spGridToExport.Rows)
                {
                    gridViewRow.BackColor = System.Drawing.Color.White;
                    foreach (TableCell gridviewRowTableCell in gridViewRow.Cells)
                    {
                        gridviewRowTableCell.Style["background-color"] = "#DDDDDD";
                    }
                }
                Label lblRecord = new Label();
                lblRecord.Text = spGridToExport.Rows.Count.ToString();
    
                Label lblDate = new Label();
                lblDate.Text = "Date : " + DateTime.Now.ToShortDateString();
    
                mainDiv.RenderControl(htw1);
                HttpContext.Current.Response.Write(sw1.ToString() + "<br/>");
    
                spGridToExport.RenderControl(htw);
                HttpContext.Current.Response.Write(sw.ToString() + "<br/>");
    
                lblRecord.RenderControl(htw2);
                HttpContext.Current.Response.Write("<font style='font-size:10.0pt; font-family:Times New Roman; font-weight:bold'>Record Printed : " + sw2.ToString() + "</font><br/>");
    
                lblDate.RenderControl(htw3);
                HttpContext.Current.Response.Write("<font style='font-size:10.0pt; font-family:Times New Roman'> " + sw3.ToString() + "</font><br/>");
    
                HttpContext.Current.Response.Flush();
                HttpContext.Current.Response.Close();
                HttpContext.Current.Response.End();
            }
    
            public string getHtmlFormat()
            {
                sb.Append("<font style='font-size:10.0pt; font-family:Times New Roman; font-weight:bold'>Saudi British Bank - Head Office</font><br />");
                sb.Append("<font style='font-size:10.0pt; font-family:Times New Roman; font-weight:bold'>Backup Inventory System</font>" + "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;");
                sb.Append("<font style='font-size:15.0pt; font-family:Times New Roman'>Inventory By Cycle</font>");
                sb.Append("<br/><font style='font-size:10.0pt; font-family:Times New Roman; font-weight:bold'>ITO - Data Center</font><br/>");
                sb.Append("<font style='font-size:10.0pt; font-family:Times New Roman; font-weight:bold'>Backup Type</font> : Name");
                return sb.ToString();
            }        
        }
    }
    

    It works in my test environment.

    Best Regards,

    Dennis


    TechNet Community Support
    Please remember to mark the replies as answers if they help, and unmark the answers if they provide no help. If you have feedback for TechNet Support, contact tnmff@microsoft.com.

    • Marked as answer by Masroorul Wednesday, May 18, 2016 11:29 AM
    Wednesday, May 18, 2016 2:48 AM

All replies

  • Hi,

    Please try to use the following JavaScript snippet in the Webpart or Page.

    <script type="text/javascript" language="javascript">
    
         //sharepoint postback to work after clicking on telerik export
         if (typeof (_spBodyOnLoadFunctionNames) != 'undefined' && _spBodyOnLoadFunctionNames != null) {
             _spBodyOnLoadFunctionNames.push("supressSubmitWraper");
         }
    
         function supressSubmitWraper() {
             _spSuppressFormOnSubmitWrapper = true;
         }
        
     </script>
    

    http://ovaiskhatri.blogspot.sg/2013/11/button-not-working-after-first-time.html

    If the issue still exists, please provide the complete code in use if possible, which should make the issue easier to be researched.

    Best Regards,

    Dennis


    TechNet Community Support
    Please remember to mark the replies as answers if they help, and unmark the answers if they provide no help. If you have feedback for TechNet Support, contact tnmff@microsoft.com.

    • Proposed as answer by Gnanasekhar Monday, May 16, 2016 3:31 PM
    Monday, May 16, 2016 6:20 AM
  • Hi Dennis,The code you send me it didn't work. still i am facing the same issue.Below is my code.Please review it why image button click is not working on first time.First time page is loading.second time image button click is working.

    InventoryByCycleUserControl.ascx<%@ Assembly Name="$SharePoint.Project.AssemblyFullName$" %><%@ Assembly Name="Microsoft.Web.CommandUI, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %><%@ Register TagPrefix="SharePoint" Namespace="Microsoft.SharePoint.WebControls"    Assembly="Microsoft.SharePoint, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %><%@ Register TagPrefix="Utilities" Namespace="Microsoft.SharePoint.Utilities" Assembly="Microsoft.SharePoint, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %><%@ Register TagPrefix="asp" Namespace="System.Web.UI" Assembly="System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" %><%@ Import Namespace="Microsoft.SharePoint" %><%@ Register TagPrefix="WebPartPages" Namespace="Microsoft.SharePoint.WebPartPages"    Assembly="Microsoft.SharePoint, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %><%@ Control Language="C#" AutoEventWireup="true" CodeBehind="InventoryByCycleUserControl.ascx.cs"    Inherits="BackupManagementSystem.WebParts.InventoryByCycle.InventoryByCycleUserControl" %><script type="text/javascript" src="/Style%20Library/BMS/JS/jquery-1.10.2.min.js"></script><script type="text/javascript">    if (typeof (_spBodyOnLoadFunctionNames) != 'undefined' && _spBodyOnLoadFunctionNames != null) {        _spBodyOnLoadFunctionNames.push("supressSubmitWraper");    }    function supressSubmitWraper() {                _spSuppressFormOnSubmitWrapper = true;    }</script><div id="divExport" runat="server">    <asp:ImageButton ID="btnExportToExcel" Visible="false" runat="server" OnClick="btnExportToExcel_Click" /></div><div id="mainDiv" runat="server" style="visibility: collapse"></div><table>    <tr>        <td>            <SharePoint:SPGridView Width="100%" ID="grdInventoryByCycle" CellSpacing="2" runat="server"                AutoGenerateColumns="false" AllowPaging="true" PageSize="2" OnPageIndexChanging="grdInventoryByCycle_OnPageIndexChanging">                <Columns>                    <SharePoint:SPBoundField HeaderStyle-BackColor="#BDC5D7" HeaderText="Label Number"                        DataField="Label Number" HeaderStyle-HorizontalAlign="Center" ItemStyle-HorizontalAlign="Left">                    </SharePoint:SPBoundField>                    <asp:BoundField HeaderStyle-BackColor="#BDC5D7" HeaderText="Description" HtmlEncode="false"                        HeaderStyle-HorizontalAlign="Center" DataField="Description" ItemStyle-HorizontalAlign="Left" />                    <SharePoint:SPBoundField HeaderStyle-BackColor="#BDC5D7" HeaderText="Sequence Number"                        HeaderStyle-HorizontalAlign="Center" DataField="Sequence Number" ItemStyle-HorizontalAlign="Left">                    </SharePoint:SPBoundField>                    <SharePoint:SPBoundField HeaderStyle-BackColor="#BDC5D7" HeaderText="Backup Date"                        HeaderStyle-HorizontalAlign="Center" DataField="Backup Date" ItemStyle-HorizontalAlign="Left">                    </SharePoint:SPBoundField>                    <SharePoint:SPBoundField HeaderStyle-BackColor="#BDC5D7" HeaderText="Address" HeaderStyle-HorizontalAlign="Center"                        DataField="Address" ItemStyle-HorizontalAlign="Left">                    </SharePoint:SPBoundField>                 <SharePoint:SPBoundField HeaderStyle-BackColor="#BDC5D7" HeaderText="Media Type"                   HeaderStyle-HorizontalAlign="Center" DataField="Media Type" ItemStyle-HorizontalAlign="Left">                </SharePoint:SPBoundField>                </Columns>            </SharePoint:SPGridView>            <asp:ObjectDataSource ID="gdrsDataSource" runat="server"></asp:ObjectDataSource>        </td>    </tr></table> 


    Tuesday, May 17, 2016 7:48 AM
  • Hi,

    Please provide your InventoryByCycleUserControl.ascx.cs (C#) code, I suggest you add your code in Code Block, we can see the code easily.

    Thanks,

    Dennis


    TechNet Community Support
    Please remember to mark the replies as answers if they help, and unmark the answers if they provide no help. If you have feedback for TechNet Support, contact tnmff@microsoft.com.

    Tuesday, May 17, 2016 7:59 AM
  • Hi Dennis,

    Thanks for quick response.Please see my code behind file.

    using System;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    using System.Web.UI.WebControls.WebParts;
    
    using System.Data;
    using Microsoft.SharePoint;
    using System.Web;
    using System.IO;
    using Microsoft.SharePoint.Utilities;
    using System.Text.RegularExpressions;
    using System.Text;
    
    namespace BackupManagementSystem.WebParts.InventoryByCycle
    {
        public partial class InventoryByCycleUserControl : UserControl
        {
            DataTable dtInventory = new DataTable();
            StringBuilder sb = new StringBuilder();
    
            protected sealed override void LoadViewState(object savedState)
            {
                try
                {
                    base.LoadViewState(savedState);
    
                    if (Context.Request.Form["__EVENTARGUMENT"] != null &&
                       Context.Request.Form["__EVENTARGUMENT"].EndsWith("__ClearFilter__"))
                    {
                        // Clear FilterExpression
                        ViewState.Remove("FilterExpression");
                    }
                }
                catch (Exception ex)
                {
                    ex.Message.ToString();
                }
            }
    
    
            protected void Page_Load(object sender, EventArgs e)
            {
                grdInventoryByCycle.DataSourceID = gdrsDataSource.ID;
                gdrsDataSource.SelectMethod = "BindGrid";
                gdrsDataSource.TypeName = this.GetType().AssemblyQualifiedName;
                gdrsDataSource.ObjectCreating += new ObjectDataSourceObjectEventHandler(gdrsDataSource_ObjectCreating);
    
                //Filtering
                gdrsDataSource.Filtering += new ObjectDataSourceFilteringEventHandler(gdrsDataSource_Filtering);
                grdInventoryByCycle.AllowFiltering = true;
                grdInventoryByCycle.FilterDataFields = "Label Number,Description,Sequence Number,Backup Date,Address,Media Type";
                grdInventoryByCycle.FilteredDataSourcePropertyName = "FilterExpression";
                //grdInventoryByCycle.FilteredDataSourcePropertyFormat = "{1} = '{0}'";
                grdInventoryByCycle.FilteredDataSourcePropertyFormat = "[{1}] LIKE '{0}'";
    
                grdInventoryByCycle.PagerTemplate = null;
    
                //Sorting
                grdInventoryByCycle.AllowSorting = true;
                grdInventoryByCycle.Sorting += new GridViewSortEventHandler(grdInventoryByCycle_Sorting);
    
                //btnExportToExcel.ImageUrl = SPContext.Current.Web.Url + "/Style%20Library/BMS/Images/Excel-icon.png";
                if (!IsPostBack)
                {
                    mainDiv.InnerHtml = getHtmlFormat();                
                }
            }
    
            public DataTable BindGrid()
            {
                try
                {
                    SPSecurity.RunWithElevatedPrivileges(delegate()
                    {
                        using (SPSite site = new SPSite(SPContext.Current.Site.ID))
                        {
                            using (SPWeb web = site.OpenWeb())
                            {
                                #region Datatable creation
    
                                dtInventory.Columns.Add("Label Number", typeof(string));
                                dtInventory.Columns.Add("Description", typeof(string));
                                dtInventory.Columns.Add("Sequence Number", typeof(string));
                                dtInventory.Columns.Add("Backup Date", typeof(string));
                                dtInventory.Columns.Add("Address", typeof(string));
                                dtInventory.Columns.Add("Media Type", typeof(string));
    
                                #endregion
    
                                SPList list = web.Lists.TryGetList("Tape Inventory");
    
                                if (list != null)
                                {
                                    SPListItemCollection items = list.GetItems();
    
                                    foreach (SPListItem item in items)
                                    {
                                        DataRow dr = dtInventory.NewRow();
                                        dr["Label Number"] = item["Title"].ToString();
                                        dr["Description"] = Regex.Replace(item["Description"].ToString(), "<.*?>", string.Empty);
                                        //dr["Description"] = item["Description"].ToString();
                                        dr["Sequence Number"] = item["Batch_x0020_Number"].ToString();
                                        dr["Backup Date"] = Convert.ToDateTime(item["Backup_x0020_Date"]).ToShortDateString();
                                        dr["Address"] = item["Address"].ToString();
                                        dr["Media Type"] = item["Media_x0020_Type"].ToString();
    
                                        dtInventory.Rows.Add(dr);
    
                                    }
                                }
                            }
                        }
                    });
                }
                catch (Exception ex)
                {
                    ex.Message.ToString();
                }
                if (dtInventory.Rows.Count > 0)
                {
                    btnExportToExcel.Visible = true;
                    return dtInventory;
                }
                else
                {
                    btnExportToExcel.Visible = false;
                    grdInventoryByCycle.EmptyDataText = "No Records found based on the selection..";
                    return null;
                }
            }
           
            protected void gdrsDataSource_ObjectCreating(object sender, ObjectDataSourceEventArgs e)
            {
                e.ObjectInstance = this;
            }
    
            private void gdrsDataSource_Filtering(object sender, ObjectDataSourceFilteringEventArgs e)
            {
                ViewState["FilterExpression"] = ((ObjectDataSourceView)sender).FilterExpression;
            }
    
            protected void grdInventoryByCycle_Sorting(object sender, GridViewSortEventArgs e)
            {
                try
                {
                    if (ViewState["FilterExpression"] != null)
                    {
                        gdrsDataSource.FilterExpression = (string)ViewState["FilterExpression"];
                    }
                }
                catch (Exception ex)
                {
                    ex.Message.ToString();
                }
            }
    
            //protected void grdInventoryByCycle_OnPageIndexChanging(object sender, GridViewPageEventArgs e)
            //{
            //    try
            //    {
            //        grdInventoryByCycle.PageIndex = e.NewPageIndex;
    
            //        //bind grid with the data
            //        if (ViewState["FilterExpression"] != null)
            //        {
            //            gdrsDataSource.FilterExpression = (string)ViewState["FilterExpression"];
            //        }
            //        grdInventoryByCycle.DataBind();
            //    }
            //    catch (Exception ex)
            //    {
            //        ex.Message.ToString();
            //        throw;
            //    }
            //}
    
            protected void btnExportToExcel_Click(object sender, ImageClickEventArgs e)
            {            
                HttpContext.Current.Response.ClearContent();
                HttpContext.Current.Response.ClearHeaders();
                string attachment = "attachment; filename=InventoryByCycle" + "_" + DateTime.Now.ToShortDateString() + ".xls";
                HttpContext.Current.Response.AddHeader("content-disposition", attachment);
                HttpContext.Current.Response.ContentType = "application/Excel";
                StringWriter sw = new StringWriter();
                HtmlTextWriter htw = new HtmlTextWriter(sw);
    
                StringWriter sw1 = new StringWriter();            
                HtmlTextWriter htw1 = new HtmlTextWriter(sw1);
    
                StringWriter sw2 = new StringWriter();
                HtmlTextWriter htw2 = new HtmlTextWriter(sw2);
    
                StringWriter sw3 = new StringWriter();
                HtmlTextWriter htw3 = new HtmlTextWriter(sw3);
    
                GridView spGridToExport = new GridView();
                spGridToExport.DataSource = gdrsDataSource;
                spGridToExport.DataBind();
                spGridToExport.HeaderRow.Style.Add("background-color", "#FFFFFF");
                foreach (TableCell tableCell in spGridToExport.HeaderRow.Cells)
                {
                    tableCell.Style["background-color"] = "#BDA65A";
                }
                foreach (GridViewRow gridViewRow in spGridToExport.Rows)
                {
                    gridViewRow.BackColor = System.Drawing.Color.White;
                    foreach (TableCell gridviewRowTableCell in gridViewRow.Cells)
                    {
                        gridviewRowTableCell.Style["background-color"] = "#DDDDDD";
                    }                
                }
                Label lblRecord = new Label();
                lblRecord.Text = spGridToExport.Rows.Count.ToString();
    
                Label lblDate = new Label();
                lblDate.Text ="Date : "+ DateTime.Now.ToShortDateString();            
                
                mainDiv.RenderControl(htw1);
                HttpContext.Current.Response.Write(sw1.ToString()+"<br/>");
    
                spGridToExport.RenderControl(htw);
                HttpContext.Current.Response.Write(sw.ToString() + "<br/>");
                           
                lblRecord.RenderControl(htw2);
                HttpContext.Current.Response.Write("<font style='font-size:10.0pt; font-family:Times New Roman; font-weight:bold'>Record Printed : " + sw2.ToString() + "</font><br/>");
    
                lblDate.RenderControl(htw3);
                HttpContext.Current.Response.Write("<font style='font-size:10.0pt; font-family:Times New Roman'> " + sw3.ToString() + "</font><br/>");
    
                HttpContext.Current.Response.Flush();
                HttpContext.Current.Response.Close();
                HttpContext.Current.Response.End();
            }
    
            public string getHtmlFormat()
            {
                sb.Append("<font style='font-size:10.0pt; font-family:Times New Roman; font-weight:bold'>Saudi British Bank - Head Office</font><br />");
                sb.Append("<font style='font-size:10.0pt; font-family:Times New Roman; font-weight:bold'>Backup Inventory System</font>" + "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;");
                sb.Append("<font style='font-size:15.0pt; font-family:Times New Roman'>Inventory By Cycle</font>");
                sb.Append("<br/><font style='font-size:10.0pt; font-family:Times New Roman; font-weight:bold'>ITO - Data Center</font><br/>");
                sb.Append("<font style='font-size:10.0pt; font-family:Times New Roman; font-weight:bold'>Backup Type</font> : Name");
                return sb.ToString();
            }        
        }
    }
    

    Tuesday, May 17, 2016 1:41 PM
  • Hi,

    I modify the code as below:

    InventoryByCycleUserControl.ascx

    <%@ Assembly Name="$SharePoint.Project.AssemblyFullName$" %>
    <%@ Assembly Name="Microsoft.Web.CommandUI, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>
    <%@ Register TagPrefix="SharePoint" Namespace="Microsoft.SharePoint.WebControls"
        Assembly="Microsoft.SharePoint, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>
    <%@ Register TagPrefix="Utilities" Namespace="Microsoft.SharePoint.Utilities" Assembly="Microsoft.SharePoint, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>
    <%@ Register TagPrefix="asp" Namespace="System.Web.UI" Assembly="System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" %>
    <%@ Import Namespace="Microsoft.SharePoint" %>
    <%@ Register TagPrefix="WebPartPages" Namespace="Microsoft.SharePoint.WebPartPages"
        Assembly="Microsoft.SharePoint, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>
    <%@ Control Language="C#" AutoEventWireup="true" CodeBehind="InventoryByCycleUserControl.ascx.cs"
        Inherits="BackupManagementSystem.WebParts.InventoryByCycle.InventoryByCycleUserControl" %>
    
    <script type="text/javascript" language="javascript">
    
        //sharepoint postback to work after clicking on telerik export
        if (typeof (_spBodyOnLoadFunctionNames) != 'undefined' && _spBodyOnLoadFunctionNames != null) {
            _spBodyOnLoadFunctionNames.push("supressSubmitWraper");
        }
    
        function supressSubmitWraper() {
            _spSuppressFormOnSubmitWrapper = true;
        }
        
    </script>
    <div id="divExport" runat="server">
        <asp:ImageButton ID="btnExportToExcel" Visible="true" runat="server" OnClick="btnExportToExcel_Click"
            AlternateText="Export" />
    </div>
    <div id="ListView">
        <table>
            <tr>
                <td>
                    <SharePoint:SPGridView Width="100%" ID="grdInventoryByCycle" CellSpacing="2" runat="server"
                        AutoGenerateColumns="false" AllowPaging="true" PageSize="2" OnPageIndexChanging="grdInventoryByCycle_OnPageIndexChanging">
                        <Columns>
                            <SharePoint:SPBoundField HeaderStyle-BackColor="#BDC5D7" HeaderText="Label Number"
                                DataField="Label Number" HeaderStyle-HorizontalAlign="Center" ItemStyle-HorizontalAlign="Left">
                            </SharePoint:SPBoundField>
                            <asp:BoundField HeaderStyle-BackColor="#BDC5D7" HeaderText="Description" HtmlEncode="false"
                                HeaderStyle-HorizontalAlign="Center" DataField="Description" ItemStyle-HorizontalAlign="Left" />
                            <SharePoint:SPBoundField HeaderStyle-BackColor="#BDC5D7" HeaderText="Sequence Number"
                                HeaderStyle-HorizontalAlign="Center" DataField="Sequence Number" ItemStyle-HorizontalAlign="Left">
                            </SharePoint:SPBoundField>
                            <SharePoint:SPBoundField HeaderStyle-BackColor="#BDC5D7" HeaderText="Backup Date"
                                HeaderStyle-HorizontalAlign="Center" DataField="Backup Date" ItemStyle-HorizontalAlign="Left">
                            </SharePoint:SPBoundField>
                            <SharePoint:SPBoundField HeaderStyle-BackColor="#BDC5D7" HeaderText="Address" HeaderStyle-HorizontalAlign="Center"
                                DataField="Address" ItemStyle-HorizontalAlign="Left">
                            </SharePoint:SPBoundField>
                            <SharePoint:SPBoundField HeaderStyle-BackColor="#BDC5D7" HeaderText="Media Type"
                                HeaderStyle-HorizontalAlign="Center" DataField="Media Type" ItemStyle-HorizontalAlign="Left">
                            </SharePoint:SPBoundField>
                        </Columns>
                    </SharePoint:SPGridView>
                    <asp:ObjectDataSource ID="gdrsDataSource" runat="server"></asp:ObjectDataSource>
                </td>
            </tr>
        </table>
    </div>
    <div id="mainDiv" runat="server" style=">
    </div>
    

    InventoryByCycleUserControl.ascx.cs

    using System;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    using System.Web.UI.WebControls.WebParts;
    
    using System.Data;
    using Microsoft.SharePoint;
    using System.Web;
    using System.IO;
    using Microsoft.SharePoint.Utilities;
    using System.Text.RegularExpressions;
    using System.Text;
    
    namespace BackupManagementSystem.WebParts.InventoryByCycle
    {
        public partial class InventoryByCycleUserControl : UserControl
        {
            DataTable dtInventory = new DataTable();
            StringBuilder sb = new StringBuilder();
    
            protected sealed override void LoadViewState(object savedState)
            {
                try
                {
                    base.LoadViewState(savedState);
    
                    if (Context.Request.Form["__EVENTARGUMENT"] != null &&
                       Context.Request.Form["__EVENTARGUMENT"].EndsWith("__ClearFilter__"))
                    {
                        // Clear FilterExpression
                        ViewState.Remove("FilterExpression");
                    }
                }
                catch (Exception ex)
                {
                    ex.Message.ToString();
                }
            }
    
    
            protected void Page_Load(object sender, EventArgs e)
            {
                grdInventoryByCycle.DataSourceID = gdrsDataSource.ID;
                gdrsDataSource.SelectMethod = "BindGrid";
                gdrsDataSource.TypeName = this.GetType().AssemblyQualifiedName;
                gdrsDataSource.ObjectCreating += new ObjectDataSourceObjectEventHandler(gdrsDataSource_ObjectCreating);
    
                //Filtering
                gdrsDataSource.Filtering += new ObjectDataSourceFilteringEventHandler(gdrsDataSource_Filtering);
                grdInventoryByCycle.AllowFiltering = true;
                grdInventoryByCycle.FilterDataFields = "Label Number,Description,Sequence Number,Backup Date,Address,Media Type";
                grdInventoryByCycle.FilteredDataSourcePropertyName = "FilterExpression";
                //grdInventoryByCycle.FilteredDataSourcePropertyFormat = "{1} = '{0}'";
                grdInventoryByCycle.FilteredDataSourcePropertyFormat = "[{1}] LIKE '{0}'";
    
                grdInventoryByCycle.PagerTemplate = null;
    
                //Sorting
                grdInventoryByCycle.AllowSorting = true;
                grdInventoryByCycle.Sorting += new GridViewSortEventHandler(grdInventoryByCycle_Sorting);
    
                //btnExportToExcel.ImageUrl = SPContext.Current.Web.Url + "/Style%20Library/BMS/Images/Excel-icon.png";
                if (!IsPostBack)
                {
                    mainDiv.InnerHtml = getHtmlFormat();
                }
            }
    
            public DataTable BindGrid()
            {
                try
                {
                    SPSecurity.RunWithElevatedPrivileges(delegate()
                    {
                        using (SPSite site = new SPSite(SPContext.Current.Site.ID))
                        {
                            using (SPWeb web = site.OpenWeb())
                            {
                                #region Datatable creation
    
                                dtInventory.Columns.Add("Label Number", typeof(string));
                                dtInventory.Columns.Add("Description", typeof(string));
                                dtInventory.Columns.Add("Sequence Number", typeof(string));
                                dtInventory.Columns.Add("Backup Date", typeof(string));
                                dtInventory.Columns.Add("Address", typeof(string));
                                dtInventory.Columns.Add("Media Type", typeof(string));
    
                                #endregion
    
                                SPList list = web.Lists.TryGetList("Tape Inventory");
    
                                if (list != null)
                                {
                                    SPListItemCollection items = list.GetItems();
    
                                    foreach (SPListItem item in items)
                                    {
                                        DataRow dr = dtInventory.NewRow();
                                        dr["Label Number"] = item["Title"].ToString();
                                        dr["Description"] = Regex.Replace(item["Description"].ToString(), "<.*?>", string.Empty);
                                        //dr["Description"] = item["Description"].ToString();
                                        dr["Sequence Number"] = item["Batch_x0020_Number"].ToString();
                                        dr["Backup Date"] = Convert.ToDateTime(item["Backup_x0020_Date"]).ToShortDateString();
                                        dr["Address"] = item["Address"].ToString();
                                        dr["Media Type"] = item["Media_x0020_Type"].ToString();
    
                                        dtInventory.Rows.Add(dr);
    
                                    }
                                }
                            }
                        }
                    });
                }
                catch (Exception ex)
                {
                    ex.Message.ToString();
                }
                if (dtInventory.Rows.Count > 0)
                {
                    btnExportToExcel.Visible = true;
                    return dtInventory;
                }
                else
                {
                    btnExportToExcel.Visible = false;
                    grdInventoryByCycle.EmptyDataText = "No Records found based on the selection..";
                    return null;
                }
            }
    
            protected void gdrsDataSource_ObjectCreating(object sender, ObjectDataSourceEventArgs e)
            {
                e.ObjectInstance = this;
            }
    
            private void gdrsDataSource_Filtering(object sender, ObjectDataSourceFilteringEventArgs e)
            {
                ViewState["FilterExpression"] = ((ObjectDataSourceView)sender).FilterExpression;
            }
    
            protected void grdInventoryByCycle_Sorting(object sender, GridViewSortEventArgs e)
            {
                try
                {
                    if (ViewState["FilterExpression"] != null)
                    {
                        gdrsDataSource.FilterExpression = (string)ViewState["FilterExpression"];
                    }
                }
                catch (Exception ex)
                {
                    ex.Message.ToString();
                }
            }
    
            protected void grdInventoryByCycle_OnPageIndexChanging(object sender, GridViewPageEventArgs e)
            {
                try
                {
                    grdInventoryByCycle.PageIndex = e.NewPageIndex;
    
                    //bind grid with the data
                    if (ViewState["FilterExpression"] != null)
                    {
                        gdrsDataSource.FilterExpression = (string)ViewState["FilterExpression"];
                    }
                    grdInventoryByCycle.DataBind();
                }
                catch (Exception ex)
                {
                    ex.Message.ToString();
                    throw;
                }
            }
    
            protected void btnExportToExcel_Click(object sender, ImageClickEventArgs e)
            {
                HttpContext.Current.Response.ClearContent();
                HttpContext.Current.Response.ClearHeaders();
                string attachment = "attachment; filename=InventoryByCycle" + "_" + DateTime.Now.ToShortDateString() + ".xls";
                HttpContext.Current.Response.AddHeader("content-disposition", attachment);
                HttpContext.Current.Response.ContentType = "application/Excel";
                StringWriter sw = new StringWriter();
                HtmlTextWriter htw = new HtmlTextWriter(sw);
    
                StringWriter sw1 = new StringWriter();
                HtmlTextWriter htw1 = new HtmlTextWriter(sw1);
    
                StringWriter sw2 = new StringWriter();
                HtmlTextWriter htw2 = new HtmlTextWriter(sw2);
    
                StringWriter sw3 = new StringWriter();
                HtmlTextWriter htw3 = new HtmlTextWriter(sw3);
    
                GridView spGridToExport = new GridView();
                spGridToExport.DataSource = gdrsDataSource;
                spGridToExport.DataBind();
                spGridToExport.HeaderRow.Style.Add("background-color", "#FFFFFF");
                foreach (TableCell tableCell in spGridToExport.HeaderRow.Cells)
                {
                    tableCell.Style["background-color"] = "#BDA65A";
                }
                foreach (GridViewRow gridViewRow in spGridToExport.Rows)
                {
                    gridViewRow.BackColor = System.Drawing.Color.White;
                    foreach (TableCell gridviewRowTableCell in gridViewRow.Cells)
                    {
                        gridviewRowTableCell.Style["background-color"] = "#DDDDDD";
                    }
                }
                Label lblRecord = new Label();
                lblRecord.Text = spGridToExport.Rows.Count.ToString();
    
                Label lblDate = new Label();
                lblDate.Text = "Date : " + DateTime.Now.ToShortDateString();
    
                mainDiv.RenderControl(htw1);
                HttpContext.Current.Response.Write(sw1.ToString() + "<br/>");
    
                spGridToExport.RenderControl(htw);
                HttpContext.Current.Response.Write(sw.ToString() + "<br/>");
    
                lblRecord.RenderControl(htw2);
                HttpContext.Current.Response.Write("<font style='font-size:10.0pt; font-family:Times New Roman; font-weight:bold'>Record Printed : " + sw2.ToString() + "</font><br/>");
    
                lblDate.RenderControl(htw3);
                HttpContext.Current.Response.Write("<font style='font-size:10.0pt; font-family:Times New Roman'> " + sw3.ToString() + "</font><br/>");
    
                HttpContext.Current.Response.Flush();
                HttpContext.Current.Response.Close();
                HttpContext.Current.Response.End();
            }
    
            public string getHtmlFormat()
            {
                sb.Append("<font style='font-size:10.0pt; font-family:Times New Roman; font-weight:bold'>Saudi British Bank - Head Office</font><br />");
                sb.Append("<font style='font-size:10.0pt; font-family:Times New Roman; font-weight:bold'>Backup Inventory System</font>" + "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;");
                sb.Append("<font style='font-size:15.0pt; font-family:Times New Roman'>Inventory By Cycle</font>");
                sb.Append("<br/><font style='font-size:10.0pt; font-family:Times New Roman; font-weight:bold'>ITO - Data Center</font><br/>");
                sb.Append("<font style='font-size:10.0pt; font-family:Times New Roman; font-weight:bold'>Backup Type</font> : Name");
                return sb.ToString();
            }        
        }
    }
    

    It works in my test environment.

    Best Regards,

    Dennis


    TechNet Community Support
    Please remember to mark the replies as answers if they help, and unmark the answers if they provide no help. If you have feedback for TechNet Support, contact tnmff@microsoft.com.

    • Marked as answer by Masroorul Wednesday, May 18, 2016 11:29 AM
    Wednesday, May 18, 2016 2:48 AM
  • Hi Dennis,

    The Modified code which you send me it is working fine.

    Thanks for your great job.

    Also i have  requirement for the other report.This report is called as "Audit Report".Audit report should be as like below table.

    This report will generate based on anything added,updated,deleted and edited in the site.How i will achieve this requirement.Please help me for this requirement.

    Wednesday, May 18, 2016 11:29 AM
  • Hi,

    I modify the code as below:

    InventoryByCycleUserControl.ascx

    <%@ Assembly Name="$SharePoint.Project.AssemblyFullName$" %>
    <%@ Assembly Name="Microsoft.Web.CommandUI, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>
    <%@ Register TagPrefix="SharePoint" Namespace="Microsoft.SharePoint.WebControls"
        Assembly="Microsoft.SharePoint, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>
    <%@ Register TagPrefix="Utilities" Namespace="Microsoft.SharePoint.Utilities" Assembly="Microsoft.SharePoint, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>
    <%@ Register TagPrefix="asp" Namespace="System.Web.UI" Assembly="System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" %>
    <%@ Import Namespace="Microsoft.SharePoint" %>
    <%@ Register TagPrefix="WebPartPages" Namespace="Microsoft.SharePoint.WebPartPages"
        Assembly="Microsoft.SharePoint, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>
    <%@ Control Language="C#" AutoEventWireup="true" CodeBehind="InventoryByCycleUserControl.ascx.cs"
        Inherits="BackupManagementSystem.WebParts.InventoryByCycle.InventoryByCycleUserControl" %>
    
    <script type="text/javascript" language="javascript">
    
        //sharepoint postback to work after clicking on telerik export
        if (typeof (_spBodyOnLoadFunctionNames) != 'undefined' && _spBodyOnLoadFunctionNames != null) {
            _spBodyOnLoadFunctionNames.push("supressSubmitWraper");
        }
    
        function supressSubmitWraper() {
            _spSuppressFormOnSubmitWrapper = true;
        }
        
    </script>
    <div id="divExport" runat="server">
        <asp:ImageButton ID="btnExportToExcel" Visible="true" runat="server" OnClick="btnExportToExcel_Click"
            AlternateText="Export" />
    </div>
    <div id="ListView">
        <table>
            <tr>
                <td>
                    <SharePoint:SPGridView Width="100%" ID="grdInventoryByCycle" CellSpacing="2" runat="server"
                        AutoGenerateColumns="false" AllowPaging="true" PageSize="2" OnPageIndexChanging="grdInventoryByCycle_OnPageIndexChanging">
                        <Columns>
                            <SharePoint:SPBoundField HeaderStyle-BackColor="#BDC5D7" HeaderText="Label Number"
                                DataField="Label Number" HeaderStyle-HorizontalAlign="Center" ItemStyle-HorizontalAlign="Left">
                            </SharePoint:SPBoundField>
                            <asp:BoundField HeaderStyle-BackColor="#BDC5D7" HeaderText="Description" HtmlEncode="false"
                                HeaderStyle-HorizontalAlign="Center" DataField="Description" ItemStyle-HorizontalAlign="Left" />
                            <SharePoint:SPBoundField HeaderStyle-BackColor="#BDC5D7" HeaderText="Sequence Number"
                                HeaderStyle-HorizontalAlign="Center" DataField="Sequence Number" ItemStyle-HorizontalAlign="Left">
                            </SharePoint:SPBoundField>
                            <SharePoint:SPBoundField HeaderStyle-BackColor="#BDC5D7" HeaderText="Backup Date"
                                HeaderStyle-HorizontalAlign="Center" DataField="Backup Date" ItemStyle-HorizontalAlign="Left">
                            </SharePoint:SPBoundField>
                            <SharePoint:SPBoundField HeaderStyle-BackColor="#BDC5D7" HeaderText="Address" HeaderStyle-HorizontalAlign="Center"
                                DataField="Address" ItemStyle-HorizontalAlign="Left">
                            </SharePoint:SPBoundField>
                            <SharePoint:SPBoundField HeaderStyle-BackColor="#BDC5D7" HeaderText="Media Type"
                                HeaderStyle-HorizontalAlign="Center" DataField="Media Type" ItemStyle-HorizontalAlign="Left">
                            </SharePoint:SPBoundField>
                        </Columns>
                    </SharePoint:SPGridView>
                    <asp:ObjectDataSource ID="gdrsDataSource" runat="server"></asp:ObjectDataSource>
                </td>
            </tr>
        </table>
    </div>
    <div id="mainDiv" runat="server" style=">
    </div>
    

    InventoryByCycleUserControl.ascx.cs

    using System;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    using System.Web.UI.WebControls.WebParts;
    
    using System.Data;
    using Microsoft.SharePoint;
    using System.Web;
    using System.IO;
    using Microsoft.SharePoint.Utilities;
    using System.Text.RegularExpressions;
    using System.Text;
    
    namespace BackupManagementSystem.WebParts.InventoryByCycle
    {
        public partial class InventoryByCycleUserControl : UserControl
        {
            DataTable dtInventory = new DataTable();
            StringBuilder sb = new StringBuilder();
    
            protected sealed override void LoadViewState(object savedState)
            {
                try
                {
                    base.LoadViewState(savedState);
    
                    if (Context.Request.Form["__EVENTARGUMENT"] != null &&
                       Context.Request.Form["__EVENTARGUMENT"].EndsWith("__ClearFilter__"))
                    {
                        // Clear FilterExpression
                        ViewState.Remove("FilterExpression");
                    }
                }
                catch (Exception ex)
                {
                    ex.Message.ToString();
                }
            }
    
    
            protected void Page_Load(object sender, EventArgs e)
            {
                grdInventoryByCycle.DataSourceID = gdrsDataSource.ID;
                gdrsDataSource.SelectMethod = "BindGrid";
                gdrsDataSource.TypeName = this.GetType().AssemblyQualifiedName;
                gdrsDataSource.ObjectCreating += new ObjectDataSourceObjectEventHandler(gdrsDataSource_ObjectCreating);
    
                //Filtering
                gdrsDataSource.Filtering += new ObjectDataSourceFilteringEventHandler(gdrsDataSource_Filtering);
                grdInventoryByCycle.AllowFiltering = true;
                grdInventoryByCycle.FilterDataFields = "Label Number,Description,Sequence Number,Backup Date,Address,Media Type";
                grdInventoryByCycle.FilteredDataSourcePropertyName = "FilterExpression";
                //grdInventoryByCycle.FilteredDataSourcePropertyFormat = "{1} = '{0}'";
                grdInventoryByCycle.FilteredDataSourcePropertyFormat = "[{1}] LIKE '{0}'";
    
                grdInventoryByCycle.PagerTemplate = null;
    
                //Sorting
                grdInventoryByCycle.AllowSorting = true;
                grdInventoryByCycle.Sorting += new GridViewSortEventHandler(grdInventoryByCycle_Sorting);
    
                //btnExportToExcel.ImageUrl = SPContext.Current.Web.Url + "/Style%20Library/BMS/Images/Excel-icon.png";
                if (!IsPostBack)
                {
                    mainDiv.InnerHtml = getHtmlFormat();
                }
            }
    
            public DataTable BindGrid()
            {
                try
                {
                    SPSecurity.RunWithElevatedPrivileges(delegate()
                    {
                        using (SPSite site = new SPSite(SPContext.Current.Site.ID))
                        {
                            using (SPWeb web = site.OpenWeb())
                            {
                                #region Datatable creation
    
                                dtInventory.Columns.Add("Label Number", typeof(string));
                                dtInventory.Columns.Add("Description", typeof(string));
                                dtInventory.Columns.Add("Sequence Number", typeof(string));
                                dtInventory.Columns.Add("Backup Date", typeof(string));
                                dtInventory.Columns.Add("Address", typeof(string));
                                dtInventory.Columns.Add("Media Type", typeof(string));
    
                                #endregion
    
                                SPList list = web.Lists.TryGetList("Tape Inventory");
    
                                if (list != null)
                                {
                                    SPListItemCollection items = list.GetItems();
    
                                    foreach (SPListItem item in items)
                                    {
                                        DataRow dr = dtInventory.NewRow();
                                        dr["Label Number"] = item["Title"].ToString();
                                        dr["Description"] = Regex.Replace(item["Description"].ToString(), "<.*?>", string.Empty);
                                        //dr["Description"] = item["Description"].ToString();
                                        dr["Sequence Number"] = item["Batch_x0020_Number"].ToString();
                                        dr["Backup Date"] = Convert.ToDateTime(item["Backup_x0020_Date"]).ToShortDateString();
                                        dr["Address"] = item["Address"].ToString();
                                        dr["Media Type"] = item["Media_x0020_Type"].ToString();
    
                                        dtInventory.Rows.Add(dr);
    
                                    }
                                }
                            }
                        }
                    });
                }
                catch (Exception ex)
                {
                    ex.Message.ToString();
                }
                if (dtInventory.Rows.Count > 0)
                {
                    btnExportToExcel.Visible = true;
                    return dtInventory;
                }
                else
                {
                    btnExportToExcel.Visible = false;
                    grdInventoryByCycle.EmptyDataText = "No Records found based on the selection..";
                    return null;
                }
            }
    
            protected void gdrsDataSource_ObjectCreating(object sender, ObjectDataSourceEventArgs e)
            {
                e.ObjectInstance = this;
            }
    
            private void gdrsDataSource_Filtering(object sender, ObjectDataSourceFilteringEventArgs e)
            {
                ViewState["FilterExpression"] = ((ObjectDataSourceView)sender).FilterExpression;
            }
    
            protected void grdInventoryByCycle_Sorting(object sender, GridViewSortEventArgs e)
            {
                try
                {
                    if (ViewState["FilterExpression"] != null)
                    {
                        gdrsDataSource.FilterExpression = (string)ViewState["FilterExpression"];
                    }
                }
                catch (Exception ex)
                {
                    ex.Message.ToString();
                }
            }
    
            protected void grdInventoryByCycle_OnPageIndexChanging(object sender, GridViewPageEventArgs e)
            {
                try
                {
                    grdInventoryByCycle.PageIndex = e.NewPageIndex;
    
                    //bind grid with the data
                    if (ViewState["FilterExpression"] != null)
                    {
                        gdrsDataSource.FilterExpression = (string)ViewState["FilterExpression"];
                    }
                    grdInventoryByCycle.DataBind();
                }
                catch (Exception ex)
                {
                    ex.Message.ToString();
                    throw;
                }
            }
    
            protected void btnExportToExcel_Click(object sender, ImageClickEventArgs e)
            {
                HttpContext.Current.Response.ClearContent();
                HttpContext.Current.Response.ClearHeaders();
                string attachment = "attachment; filename=InventoryByCycle" + "_" + DateTime.Now.ToShortDateString() + ".xls";
                HttpContext.Current.Response.AddHeader("content-disposition", attachment);
                HttpContext.Current.Response.ContentType = "application/Excel";
                StringWriter sw = new StringWriter();
                HtmlTextWriter htw = new HtmlTextWriter(sw);
    
                StringWriter sw1 = new StringWriter();
                HtmlTextWriter htw1 = new HtmlTextWriter(sw1);
    
                StringWriter sw2 = new StringWriter();
                HtmlTextWriter htw2 = new HtmlTextWriter(sw2);
    
                StringWriter sw3 = new StringWriter();
                HtmlTextWriter htw3 = new HtmlTextWriter(sw3);
    
                GridView spGridToExport = new GridView();
                spGridToExport.DataSource = gdrsDataSource;
                spGridToExport.DataBind();
                spGridToExport.HeaderRow.Style.Add("background-color", "#FFFFFF");
                foreach (TableCell tableCell in spGridToExport.HeaderRow.Cells)
                {
                    tableCell.Style["background-color"] = "#BDA65A";
                }
                foreach (GridViewRow gridViewRow in spGridToExport.Rows)
                {
                    gridViewRow.BackColor = System.Drawing.Color.White;
                    foreach (TableCell gridviewRowTableCell in gridViewRow.Cells)
                    {
                        gridviewRowTableCell.Style["background-color"] = "#DDDDDD";
                    }
                }
                Label lblRecord = new Label();
                lblRecord.Text = spGridToExport.Rows.Count.ToString();
    
                Label lblDate = new Label();
                lblDate.Text = "Date : " + DateTime.Now.ToShortDateString();
    
                mainDiv.RenderControl(htw1);
                HttpContext.Current.Response.Write(sw1.ToString() + "<br/>");
    
                spGridToExport.RenderControl(htw);
                HttpContext.Current.Response.Write(sw.ToString() + "<br/>");
    
                lblRecord.RenderControl(htw2);
                HttpContext.Current.Response.Write("<font style='font-size:10.0pt; font-family:Times New Roman; font-weight:bold'>Record Printed : " + sw2.ToString() + "</font><br/>");
    
                lblDate.RenderControl(htw3);
                HttpContext.Current.Response.Write("<font style='font-size:10.0pt; font-family:Times New Roman'> " + sw3.ToString() + "</font><br/>");
    
                HttpContext.Current.Response.Flush();
                HttpContext.Current.Response.Close();
                HttpContext.Current.Response.End();
            }
    
            public string getHtmlFormat()
            {
                sb.Append("<font style='font-size:10.0pt; font-family:Times New Roman; font-weight:bold'>Saudi British Bank - Head Office</font><br />");
                sb.Append("<font style='font-size:10.0pt; font-family:Times New Roman; font-weight:bold'>Backup Inventory System</font>" + "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;");
                sb.Append("<font style='font-size:15.0pt; font-family:Times New Roman'>Inventory By Cycle</font>");
                sb.Append("<br/><font style='font-size:10.0pt; font-family:Times New Roman; font-weight:bold'>ITO - Data Center</font><br/>");
                sb.Append("<font style='font-size:10.0pt; font-family:Times New Roman; font-weight:bold'>Backup Type</font> : Name");
                return sb.ToString();
            }        
        }
    }
    

    It works in my test environment.

    Best Regards,

    Dennis


    TechNet Community Support
    Please remember to mark the replies as answers if they help, and unmark the answers if they provide no help. If you have feedback for TechNet Support, contact tnmff@microsoft.com.


    Great job Dennis.Your code is working fine.
    Wednesday, May 18, 2016 11:30 AM