Export to Excel using Dynamic data only exporting the visible rows.

Question

User1204604062 posted

 

HI, I have a function that exports to excel but its only exporting the visible records, not all of the records in the other pages.

 

The export should export ALL rows. Not only the visible rows

 

 

using System.Collections;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System;
using System.Text;

namespace Sodexo.Business.Excel
{
    /// <summary>
    /// Summary description for ExcelHelper
    /// </summary>
    public static class ExcelHelper
    {
        public static void ExportGridView(HttpResponse response, GridView grdView, string filename, ArrayList excludedColumnList)
        {

            PrepareGridViewForExport(grdView,excludedColumnList);

            // Clear response content & headers
            response.Clear();
            response.ClearContent();
            response.ClearHeaders();

            // Add header
            response.AddHeader("content-disposition", "attachment;filename=" + filename + ".xls");

            response.ContentEncoding = Encoding.UTF8;
            response.Charset = "utf-8";
            response.Cache.SetCacheability(System.Web.HttpCacheability.Public);
            response.ContentType = "application/vnd.xls";

            // Create stringWriter
            System.IO.StringWriter stringWrite = new System.IO.StringWriter();

            // Create HtmlTextWriter
            HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);

            // Call gridview's renderControl
            grdView.RenderControl(htmlWrite);

            // Write Response to browser
            response.Write(stringWrite.ToString());

            response.End();

        }

        private static void PrepareGridViewForExport(GridView gv, ArrayList excludedColumns)
        {
            gv.GridLines = GridLines.Both;
            // Remove controls from Column Headers
            if (gv.HeaderRow != null && gv.HeaderRow.Cells != null)
            {
                for (int ct = 0; ct < gv.HeaderRow.Cells.Count; ct++)
                {
                    // Save initial text if found
                    string headerText = gv.HeaderRow.Cells[ct].Text;

                    // Check for controls in header
                    if (gv.HeaderRow.Cells[ct].HasControls())
                    {
                        // Check for link button
                        if (gv.HeaderRow.Cells[ct].Controls[0].GetType().ToString() == "System.Web.UI.WebControls.DataControlLinkButton")
                        {
                            // link button found, get text
                            headerText = ((LinkButton)gv.HeaderRow.Cells[ct].Controls[0]).Text;
                        }

                        // Remove controls from header
                        gv.HeaderRow.Cells[ct].Controls.Clear();
                    }

                    // Reassign header text
                    gv.HeaderRow.Cells[ct].Text = headerText;
                }
            }

            // Remove unwanted columns (header text listed in removeColumnList arraylist)
            if (excludedColumns != null)
            {
                foreach (DataControlField field in gv.Columns)
                {
                    if (excludedColumns.Contains(field.HeaderText))
                    {
                        field.Visible = false;
                    }
                }
            }

            //Remove unwanted rows
            foreach (GridViewRow row in gv.Rows)
            {
                if (row.RowType == DataControlRowType.Pager
                   || row.RowType == DataControlRowType.Footer
                    || row.RowType == DataControlRowType.Separator)
                {
                    row.Controls.Clear();
                    row.Visible = false;
                }
            }

            if(gv.BottomPagerRow != null) gv.BottomPagerRow.Visible = false;
            if (gv.TopPagerRow != null) gv.TopPagerRow.Visible = false;

            ReplaceControlsForLiterals(gv);
        }

        private static void ReplaceControlsForLiterals(Control gv)
        {
            LinkButton lb = new LinkButton();
            Literal l = new Literal();
            string name = String.Empty;
            for (int i = 0; i < gv.Controls.Count; i++)
            {
                if (gv.Controls[i].GetType() == typeof(LinkButton))
                {
                    l.Text = (gv.Controls[i] as LinkButton).Text;
                    gv.Controls.Remove(gv.Controls[i]);
                    gv.Controls.AddAt(i, l);
                }
                else if (gv.Controls[i].GetType().ToString() == "System.Web.UI.WebControls.DataControlLinkButton")
                {
                    l.Text = (gv.Controls[i] as LinkButton).Text;
                    gv.Controls.Remove(gv.Controls[i]);
                    gv.Controls.AddAt(i, l);
                }
                else if (gv.Controls[i].GetType() == typeof(DropDownList))
                {
                    l.Text = (gv.Controls[i] as DropDownList).SelectedItem.Text;
                    gv.Controls.Remove(gv.Controls[i]);
                    gv.Controls.AddAt(i, l);
                }
                else if (gv.Controls[i].GetType() == typeof(CheckBox))
                {
                    l.Text = (gv.Controls[i] as CheckBox).Checked ? "Verdadero" : "Falso";
                    gv.Controls.Remove(gv.Controls[i]);
                    gv.Controls.AddAt(i, l);
                }
                else if (gv.Controls[i].GetType() == typeof(HyperLink))
                {
                    l.Text = (gv.Controls[i] as HyperLink).Text;
                    gv.Controls.Remove(gv.Controls[i]);
                    gv.Controls.AddAt(i, l);
                }
                else if (gv.Controls[i].GetType() == typeof(Image))
                {
                    gv.Controls.Remove(gv.Controls[i]);
                }

                if (gv.Controls[i].HasControls())
                {
                    ReplaceControlsForLiterals(gv.Controls[i]);
                }
            }
        }
    }
}

  protected void btnExcel_Click(object sender, EventArgs e)
        {
            ExcelHelper.ExportGridView(this.Response, GridView1, GridDataSource.GetTable().DisplayName , GetExcludedColumns());
        }

        private ArrayList GetExcludedColumns()
        {
            ArrayList ExcludedColumns = new ArrayList();
            MetaTable table = GridDataSource.GetTable();

            foreach (MetaColumn col in table.Columns)
            {
                if (!col.Scaffold) continue;
                if (col is MetaChildrenColumn) ExcludedColumns.Add(col.DisplayName);
            }

            ExcludedColumns.Add(GridView1.Columns[GridView1.Columns.Count-1].HeaderText);

            return ExcludedColumns;
        }

 public override void VerifyRenderingInServerForm(Control control)
            {
                // Confirms that an HtmlForm control is rendered for the
                //specified ASP.NET server control at run time.
            }

I have tried everything with no success

  protected void btnExcel_Click(object sender, EventArgs e)
        {
            //ExcelHelper.ExportGridView(this.Response, GridView1, GridDataSource.GetTable().DisplayName , GetExcludedColumns());
            
            DataSourceView dv=GridView1.DataSourceObject.GetView(string.Empty);
            dv..
           
            //dv.Select(
            //var data = ModelDataContext.Instance.GetTable(GridDataSource.GetTable().EntityType);

            
            //DataTable da = (from c in dv
            //               select c).ToDataTable();   
            DataSet ds = (DataSet)GridView1.DataSource;

            //da.Columns.RemoveAt(dt.Columns.Count - 1);
            //DataSet ds= new DataSet();
            //ds.Tables.Add(da);

            //DataSetToExcel.Convert(ds, 0, this.Response);
        }