How to export all pages of gridview to excel without columns containing controls?

Question

I have a gridview that has 2 columns that are a dropdownlist and upload control. I am trying to export the gridview to excel and the code works but it only works to export the current view not all the pages. When I try to export all pages the data binding fails and code crashes. Please help if you know a way of getting all the pages exported to excel/csv with or without the columns containing controls.

<asp:ImageButton ID="ExportBtn" runat="server" ImageUrl="../Images/ExcelIcon.jpg" Alt="Export" Width="40px" Height="40px" style="border-width:0px" onclick="ExportBtn_Click" />


protected void ExportBtn_Click(object sender, ImageClickEventArgs e)
{

        gv.Columns[7].Visible = false;
        gv.Columns[8].Visible = false;

        Response.ClearContent();
        Response.AppendHeader("content-disposition", "attachment; filename=Documents.xls");
        Response.ContentType = "application/excel";
        gv.AllowPaging = false;
        gv.DataBind();

        StringWriter sw = new StringWriter();
        HtmlTextWriter htw = new HtmlTextWriter(sw);

        Response.Charset = String.Empty;

        gv.RenderControl(htw);
        Response.Write(sw.ToString());

        Response.End();
    }

The error I get when I try to export all pages is: Server Error in '...20150605_0846_local' Application.

'DocStatusDropDown' has a SelectedValue which is invalid because it does not exist in the list of items. Parameter name: value

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.ArgumentOutOfRangeException: 'DocStatusDropDown' has a SelectedValue which is invalid because it does not exist in the list of items.
Parameter name: value

Source Error: 


Line 194:        gvMainGrid.Columns[8].Visible = false;
Line 195:        gvMainGrid.AllowPaging = false;
Line 196:        gvMainGrid.DataBind();
Line 197:        Response.ClearContent();
Line 198:        Response.AppendHeader("content-disposition", "attachment; filename=MissingDocuments.xls");

You're using an incorrect content type for an excel document. Change it as appropriate according to the accepted answer here: http://stackoverflow.com/questions/2937465/what-is-correct-content-type-for-excel-files — Dan, Jun 10 '15 at 14:35
Camilo, This is the error(posted above) I get when I try to export all pages otherwise it works fine to just export current view/page — Amilah, Jun 10 '15 at 18:16

score 1 · Answer 1 · answered Jun 10 '15 at 16:27

using ExcelLibrary it's pretty easy to achieve it. But you needs to pass a dataSet to CreateWorkbook method.

    protected void CreateExcel_Click(object sender, EventArgs e)
     {
         List<Student> dataSource = (List<Student>)GrdData.DataSource; 
         DataSet ds = new DataSet("NewDataSet"); 
         DataTable dTable = ExtentionHelper.ToDataTable<Student>(dataSource);
         ds.Tables.Add(dTable);
         ExcelLibrary.DataSetHelper.CreateWorkbook("C://MyExcelFile.xlsx", ds);
      }

ExtentionHelper to get DataTable from List<Student>

public static class ExtentionHelper
    {
        public static DataTable ToDataTable<T>(this IEnumerable<T> collection)
        {
            DataTable dt = new DataTable("DataTable");
            Type t = typeof(T);
            PropertyInfo[] pia = t.GetProperties();

            //Inspect the properties and create the columns in the DataTable
            foreach (PropertyInfo pi in pia)
            {
                Type ColumnType = pi.PropertyType;
                if ((ColumnType.IsGenericType))
                {
                    ColumnType = ColumnType.GetGenericArguments()[0];
                }
                dt.Columns.Add(pi.Name, ColumnType);
            }

            //Populate the data table
            foreach (T item in collection)
            {
                DataRow dr = dt.NewRow();
                dr.BeginEdit();
                foreach (PropertyInfo pi in pia)
                {
                    if (pi.GetValue(item, null) != null)
                    {
                        dr[pi.Name] = pi.GetValue(item, null);
                    }
                }
                dr.EndEdit();
                dt.Rows.Add(dr);
            }
            return dt;
        }
    }

Thank you Shana, found another solution /work around before I got a chance to try out this code. I might use it in the future. Thanks again. — Amilah, Jun 12 '15 at 14:33

score 0 · Answer 2 · edited Jun 10 '15 at 14:50

0

Response.ClearContent();
Response.Buffer = true;
Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", "Documents.xls"));
Response.ContentType = "application/vnd.ms-excel";
System.IO.StringWriter sw = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(sw);
gv.AllowPaging = false;
gv.RenderControl(hw);
Response.Write(sw.ToString());
Response.End();

edited Jun 10 '15 at 14:50

Dan

493
2
6
20

answered Jun 10 '15 at 14:22

er_jack

122
9

Thank you! I still encounter the same problem with this solution. but I've found a work around and that's all I need. – Amilah Jun 12 '15 at 14:32

score -1 · Answer 3 · answered Jun 12 '15 at 14:31

-1

Thank you all! I found a work around by duplicating the gridview without the trouble control columns and exporting it. Also turned the duplicate gv to not be displayed so no UI awkwardness.

answered Jun 12 '15 at 14:31

Amilah

1
2

How to export all pages of gridview to excel without columns containing controls?

3 Answers3