Grid view is not being refreshed on exporting grid view to excel in asp.net

Question

i am come up with scenario that while exporting grid view to excel its not refreshing my grid data source. Below is my code what i have done before. Below code is updating flag in database and then trying to refresh the grid data source in Fill Grid method.

protected void Process_Command(object sender, CommandEventArgs e)
        {
            if (!string.IsNullOrEmpty(Convert.ToString(e.CommandArgument)))
            {

                using (var transaction = context.Database.BeginTransaction())
                {
                    DocumentOGP objDocumentOGP = context.DocumentOGPs.Find(Convert.ToInt64(e.CommandArgument));

                    objDocumentOGP.UpdationDate = DateTime.Now;
                    objDocumentOGP.DispatchStatusID = 2;
                    context.DocumentOGPs.Add(objDocumentOGP);
                    context.Entry(objDocumentOGP).State = System.Data.Entity.EntityState.Modified;
                    context.SaveChanges();
                    transaction.Commit();
                    FillGrid();

                }
                ExportToExcel(Convert.ToInt64(e.CommandArgument));
            }
        } 

Below is the Export to excel method.

public void ExportToExcel(Int64 OGPCode)
        {

            DataTable dtPickList =GeneralQuery.GetDocumentPickList(OGPCode);
            if (dtPickList != null && dtPickList.Rows.Count>0)
            {
                //Create a dummy GridView
                GridView GridView1 = new GridView();
                GridView1.AllowPaging = false;
                GridView1.DataSource = dtPickList;
                GridView1.DataBind();

                Response.Clear();
                Response.Buffer = true;
                Response.AddHeader("content-disposition", "attachment;filename=Inbox.xls");
                Response.Charset = "";
                Response.ContentType = "application/vnd.ms-excel";
                StringWriter sw = new StringWriter();
                HtmlTextWriter hw = new HtmlTextWriter(sw);

                for (int i = 0; i < GridView1.Rows.Count; i++)
                {
                    //Apply text style to each Row
                    GridView1.Rows[i].Attributes.Add("class", "textmode");
                }
                GridView1.RenderControl(hw);

                //style to format numbers to string
                string style = @"<style> .textmode { mso-number-format:\@; } </style>";
                Response.Write(style);
                Response.Output.Write(sw.ToString());
                Response.Flush();
                Response.End();
                GridView1.DataSource = null;
                Response.Write(Request.RawUrl.ToString());
            }
        }



public override void VerifyRenderingInServerForm(Control control)
{

}

Please help me what i am doing wrong. Thanks

Answer 1

Finally i found the way to refresh the grid view while exporting the grid view to excel. I just create new web form and put ExportGridToExcel() method in Page_Load and on button click it refresh the grid view data source and open new tab to download excel file. Below is my code.

protected void Process_Command(object sender, CommandEventArgs e)
        {
            if (Session["Status"] != "Refreshed")
            {
                if (!string.IsNullOrEmpty(Convert.ToString(e.CommandArgument)))
                {

                    using (var transaction = context.Database.BeginTransaction())
                    {
                        DocumentOGP objDocumentOGP = context.DocumentOGPs.Find(Convert.ToInt64(e.CommandArgument));

                        objDocumentOGP.UpdationDate = DateTime.Now;
                        objDocumentOGP.DispatchStatusID = 2;
                        context.DocumentOGPs.Add(objDocumentOGP);
                        context.Entry(objDocumentOGP).State = System.Data.Entity.EntityState.Modified;
                        context.SaveChanges();
                        transaction.Commit();
                        FillGrid();
                        Session["OGPCode"] = Convert.ToString(e.CommandArgument);

                    }

                    ScriptManager.RegisterStartupScript(Page, Page.GetType(), "OpenWindow", "window.open('http://localhost:56430/DownLoadExcel','_newtab');", true);
                }
            }
        } 

Below is my Download excel file web-form and its implementation

public partial class DownLoadExcel : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!Page.IsPostBack)
            {
                if (!string.IsNullOrEmpty(Convert.ToString(Session["OGPCode"])))
                {
                    ExportToExcel(Convert.ToInt64(Session["OGPCode"]));
                    Session["OGPCode"] = null;
                }
            }
        }
        public void ExportToExcel(Int64 OGPCode)
        {
            DataTable dt = GeneralQuery.GetDocumentPickList(OGPCode);
            //create a new byte array       
            byte[] bin;
            string FileName = "Pick-List-" + DateTime.Now.ToString();
            //create a new excel document
            using (ExcelPackage excelPackage = new ExcelPackage())
            {
                //create a new worksheet
                ExcelWorksheet ws = excelPackage.Workbook.Worksheets.Add(FileName);

                //add the contents of the datatable to the excel file
                ws.Cells["A1"].LoadFromDataTable(dt, true);

                //auto fix the columns
                ws.Cells[ws.Dimension.Address].AutoFitColumns();

                //loop all the columns
                for (int col = 1; col <= ws.Dimension.End.Column; col++)
                {
                    //make all columns just a bit wider, it would sometimes not fit
                    ws.Column(col).Width = ws.Column(col).Width + 1;

                    var cell = ws.Cells[1, col];

                    //make the text bold
                    cell.Style.Font.Bold = true;

                    //make the background of the cell gray
                    var fill = cell.Style.Fill;
                    fill.PatternType = ExcelFillStyle.Solid;
                    fill.BackgroundColor.SetColor(ColorTranslator.FromHtml("#BFBFBF"));

                    //make the header text upper case
                    cell.Value = ((string)cell.Value).ToUpper();
                }

                //convert the excel package to a byte array
                bin = excelPackage.GetAsByteArray();
            }

            //clear the buffer stream
            Response.ClearHeaders();
            Response.Clear();
            Response.Buffer = true;

            //set the correct contenttype
            Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

            //set the correct length of the data being send
            Response.AddHeader("content-length", bin.Length.ToString());

            //set the filename for the excel package
            Response.AddHeader("content-disposition", "attachment; filename=\"" + FileName + ".xlsx\"");

            //send the byte array to the browser
            Response.OutputStream.Write(bin, 0, bin.Length);

            //cleanup
            Response.Flush();
            HttpContext.Current.ApplicationInstance.CompleteRequest();
        }
    }

Answer 2

When you update GridView or any control values dynamically you should render and export it to other application.

Sample: check with RenderControl method of GridView.

private void ExportGridToExcel()
{
    Response.Clear();
    Response.Buffer = true;
    Response.ClearContent();
    Response.ClearHeaders();
    Response.Charset = "";
    string FileName = "xyz.xls";
    StringWriter strwritter = new StringWriter();
    HtmlTextWriter htmltextwrtter = new HtmlTextWriter(strwritter);
    Response.Cache.SetCacheability(HttpCacheability.NoCache);
    Response.ContentType = "application/vnd.ms-excel";
    Response.AddHeader("Content-Disposition", "attachment;filename=" + FileName);
    GridView1.RenderControl(htmltextwrtter);
    Response.Write(strwritter.ToString());
    Response.End();

}