exporting gridview to excel issue

Discussion in 'ASP .Net' started by JohnE, Aug 28, 2009.

  1. JohnE

    JohnE Guest

    I have a gridview that is exported to excel. The code below works. But, the
    only drawback is the gridview has 6 pages (sometimes more, sometimes less).
    When the export occurs, it takes the grid and the pages and puts it into
    excel. I need to have a regular looking spreadsheet (all 112 records)
    without the gridview paging look. I have tried putting GridView1.AllowPaging
    = false in different spots of the code but it is not working. Here is the
    exporting code that I am using.

    protected void btnExportToExcel_Click(object sender, EventArgs e)
    {
    PrepareGridViewForExport(GridView1);
    ExportGridToExcel(GridView1, "test");
    }

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

    public void ExportGridToExcel(GridView grdGridView, string fileName)
    {
    Response.Clear();
    Response.Buffer = true;
    Response.AddHeader("content-disposition",
    string.Format("attachment;filename={0}.xls", fileName));
    Response.Charset = "";
    Response.ContentType =
    "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
    StringWriter stringWrite = new StringWriter();
    HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
    grdGridView.RenderControl(htmlWrite);
    Response.Write(stringWrite.ToString());
    Response.End();
    }

    private void PrepareGridViewForExport(Control gv)
    {
    LinkButton lb = new LinkButton();
    Literal lit = new Literal();
    string name = String.Empty;

    for (int i = 0; i < gv.Controls.Count; i++)
    {
    if (gv.Controls.GetType() == typeof(LinkButton))
    {
    lit.Text = (gv.Controls as LinkButton).Text;
    gv.Controls.Remove(gv.Controls);
    gv.Controls.AddAt(i, lit);
    }
    else if (gv.Controls.GetType() == typeof(DropDownList))
    {
    lit.Text = (gv.Controls as DropDownList).SelectedItem.Text;
    gv.Controls.Remove(gv.Controls);
    gv.Controls.AddAt(i, lit);
    }
    else if (gv.Controls.GetType() == typeof(CheckBox))
    {
    lit.Text = (gv.Controls as CheckBox).Checked ? "True" :
    "False";
    gv.Controls.Remove(gv.Controls);
    gv.Controls.AddAt(i, lit);
    }
    if (gv.Controls.HasControls())
    {
    PrepareGridViewForExport(gv.Controls);
    }
    }
    }

    Can someone see the reason the paging is still there in the spreadsheet and
    what is needed to rectifiy it?

    Thanks.

    John
     
    JohnE, Aug 28, 2009
    #1
    1. Advertising

  2. On Aug 28, 5:48 am, JohnE <> wrote:
    > I have a gridview that is exported to excel.  The code below works.  But, the
    > only drawback is the gridview has 6 pages (sometimes more, sometimes less).  
    > When the export occurs, it takes the grid and the pages and puts it into
    > excel.  I need to have a regular looking spreadsheet (all 112 records)
    > without the gridview paging look.  I have tried putting GridView1.AllowPaging
    > = false in different spots of the code but it is not working.  Here is the
    > exporting code that I am using.
    >
    >     protected void btnExportToExcel_Click(object sender, EventArgs e)
    >     {
    >         PrepareGridViewForExport(GridView1);
    >         ExportGridToExcel(GridView1, "test");
    >     }
    >
    >     public override void VerifyRenderingInServerForm(Control control)
    >     {
    >         // Confirms that an HtmlForm control is rendered for the specified
    > ASP.NET server control at run time.
    >         return;
    >     }
    >
    >     public void ExportGridToExcel(GridView grdGridView, string fileName)
    >     {
    >         Response.Clear();
    >         Response.Buffer = true;
    >         Response.AddHeader("content-disposition",
    > string.Format("attachment;filename={0}.xls", fileName));
    >         Response.Charset = "";
    >         Response.ContentType =
    > "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
    >         StringWriter stringWrite = new StringWriter();
    >         HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
    >         grdGridView.RenderControl(htmlWrite);
    >         Response.Write(stringWrite.ToString());
    >         Response.End();
    >     }
    >
    >     private void PrepareGridViewForExport(Control gv)
    >     {
    >         LinkButton lb = new LinkButton();
    >         Literal lit = new Literal();
    >         string name = String.Empty;
    >
    >         for (int i = 0; i < gv.Controls.Count; i++)
    >         {
    >             if (gv.Controls.GetType() == typeof(LinkButton))
    >             {
    >                 lit.Text = (gv.Controls as LinkButton).Text;
    >                 gv.Controls.Remove(gv.Controls);
    >                 gv.Controls.AddAt(i, lit);
    >             }
    >             else if (gv.Controls.GetType() == typeof(DropDownList))
    >             {
    >                 lit.Text = (gv.Controls as DropDownList).SelectedItem.Text;
    >                 gv.Controls.Remove(gv.Controls);
    >                 gv.Controls.AddAt(i, lit);
    >             }
    >             else if (gv.Controls.GetType() == typeof(CheckBox))
    >             {
    >                 lit.Text = (gv.Controls as CheckBox)..Checked ? "True" :
    > "False";
    >                 gv.Controls.Remove(gv.Controls);
    >                 gv.Controls.AddAt(i, lit);
    >             }
    >             if (gv.Controls.HasControls())
    >             {
    >                 PrepareGridViewForExport(gv.Controls);
    >             }
    >         }
    >     }
    >
    > Can someone see the reason the paging is still there in the spreadsheet and
    > what is needed to rectifiy it?
    >
    > Thanks.
    >
    > John


    If I were you I would export data using the datasource but not the
    grid itself. In this case you don't need to do anything with grid, you
    would just loop through the dataset and push all rows to the browser.

    Similar to this code
    public static void ExportToSpreadsheet(DataTable table, string name)
    {
    HttpContext context = HttpContext.Current;
    context.Response.Clear();
    foreach (DataColumn column in table.Columns)
    {
    context.Response.Write(column.ColumnName + ";");
    }
    context.Response.Write(Environment.NewLine);
    foreach (DataRow row in table.Rows)
    {
    for (int i = 0; i < table.Columns.Count; i++)
    {
    context.Response.Write(row.ToString().Replace(";",
    string.Empty) + ";");
    }
    context.Response.Write(Environment.NewLine);
    }
    context.Response.ContentType = "text/csv";
    context.Response.AppendHeader("Content-Disposition", "attachment;
    filename=" + name + ".csv");
    context.Response.End();
    }

    http://www.webpronews.com/expertarticles/2006/11/28/aspnet-export-a-datatable-to-excel

    Regarding your problem. I don't see where GridView1.AllowPaging is
    set. Did you try to set PageSize?
     
    Alexey Smirnov, Aug 28, 2009
    #2
    1. Advertising

  3. JohnE

    JohnE Guest

    "Alexey Smirnov" wrote:

    > On Aug 28, 5:48 am, JohnE <> wrote:
    > > I have a gridview that is exported to excel. The code below works. But, the
    > > only drawback is the gridview has 6 pages (sometimes more, sometimes less).
    > > When the export occurs, it takes the grid and the pages and puts it into
    > > excel. I need to have a regular looking spreadsheet (all 112 records)
    > > without the gridview paging look. I have tried putting GridView1.AllowPaging
    > > = false in different spots of the code but it is not working. Here is the
    > > exporting code that I am using.
    > >
    > > protected void btnExportToExcel_Click(object sender, EventArgs e)
    > > {
    > > PrepareGridViewForExport(GridView1);
    > > ExportGridToExcel(GridView1, "test");
    > > }
    > >
    > > public override void VerifyRenderingInServerForm(Control control)
    > > {
    > > // Confirms that an HtmlForm control is rendered for the specified
    > > ASP.NET server control at run time.
    > > return;
    > > }
    > >
    > > public void ExportGridToExcel(GridView grdGridView, string fileName)
    > > {
    > > Response.Clear();
    > > Response.Buffer = true;
    > > Response.AddHeader("content-disposition",
    > > string.Format("attachment;filename={0}.xls", fileName));
    > > Response.Charset = "";
    > > Response.ContentType =
    > > "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
    > > StringWriter stringWrite = new StringWriter();
    > > HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
    > > grdGridView.RenderControl(htmlWrite);
    > > Response.Write(stringWrite.ToString());
    > > Response.End();
    > > }
    > >
    > > private void PrepareGridViewForExport(Control gv)
    > > {
    > > LinkButton lb = new LinkButton();
    > > Literal lit = new Literal();
    > > string name = String.Empty;
    > >
    > > for (int i = 0; i < gv.Controls.Count; i++)
    > > {
    > > if (gv.Controls.GetType() == typeof(LinkButton))
    > > {
    > > lit.Text = (gv.Controls as LinkButton).Text;
    > > gv.Controls.Remove(gv.Controls);
    > > gv.Controls.AddAt(i, lit);
    > > }
    > > else if (gv.Controls.GetType() == typeof(DropDownList))
    > > {
    > > lit.Text = (gv.Controls as DropDownList).SelectedItem.Text;
    > > gv.Controls.Remove(gv.Controls);
    > > gv.Controls.AddAt(i, lit);
    > > }
    > > else if (gv.Controls.GetType() == typeof(CheckBox))
    > > {
    > > lit.Text = (gv.Controls as CheckBox)..Checked ? "True" :
    > > "False";
    > > gv.Controls.Remove(gv.Controls);
    > > gv.Controls.AddAt(i, lit);
    > > }
    > > if (gv.Controls.HasControls())
    > > {
    > > PrepareGridViewForExport(gv.Controls);
    > > }
    > > }
    > > }
    > >
    > > Can someone see the reason the paging is still there in the spreadsheet and
    > > what is needed to rectifiy it?
    > >
    > > Thanks.
    > >
    > > John

    >
    > If I were you I would export data using the datasource but not the
    > grid itself. In this case you don't need to do anything with grid, you
    > would just loop through the dataset and push all rows to the browser.
    >
    > Similar to this code
    > public static void ExportToSpreadsheet(DataTable table, string name)
    > {
    > HttpContext context = HttpContext.Current;
    > context.Response.Clear();
    > foreach (DataColumn column in table.Columns)
    > {
    > context.Response.Write(column.ColumnName + ";");
    > }
    > context.Response.Write(Environment.NewLine);
    > foreach (DataRow row in table.Rows)
    > {
    > for (int i = 0; i < table.Columns.Count; i++)
    > {
    > context.Response.Write(row.ToString().Replace(";",
    > string.Empty) + ";");
    > }
    > context.Response.Write(Environment.NewLine);
    > }
    > context.Response.ContentType = "text/csv";
    > context.Response.AppendHeader("Content-Disposition", "attachment;
    > filename=" + name + ".csv");
    > context.Response.End();
    > }
    >
    > http://www.webpronews.com/expertarticles/2006/11/28/aspnet-export-a-datatable-to-excel
    >
    > Regarding your problem. I don't see where GridView1.AllowPaging is
    > set. Did you try to set PageSize?
    >


    I will give it a try and let you know the results. From reading what you
    say and from the link, it seems this way would be cleaner and simpler. It
    may also resolve several other export issues I have with background coloring,
    etc.
    .... John
     
    JohnE, Aug 28, 2009
    #3
  4. JohnE

    JohnE Guest

    Alexey, well I gave it a try. I got all the way to excel but no data showed
    up. I am providing the info below that I used to get as far as I did. The
    table in the Export lin of the btn click errored out until I added the
    DataTable table line in the same event. I even found the correct contenttype.

    See something wrong that I am missing?
    Thanks.
    John

    protected void btnExportToExcel_Click(object sender, EventArgs e)
    {
    GridView1.DataSource = bindgrid();
    GridView1.DataBind();
    DataTable table = new DataTable();
    GridView1.AllowPaging = false;
    GridView1.PageSize = 1;

    PrepareGridViewForExport(GridView1);
    //ExportGridToExcel(GridView1, "test");

    ExportToSpreadsheet(table, "test");
    }

    public static void ExportToSpreadsheet(DataTable table, string name)
    {
    HttpContext context = HttpContext.Current;
    context.Response.Clear();

    foreach (DataColumn column in table.Columns)
    {
    context.Response.Write(column.ColumnName + " ;");
    }
    context.Response.Write(Environment.NewLine);

    foreach (DataRow row in table.Rows)
    {
    for (int i = 0; i < table.Columns.Count; i++)
    {
    context.Response.Write(row.ToString().Replace(";",
    string.Empty) + ";");
    }
    context.Response.Write(Environment.NewLine);
    }
    context.Response.ContentType =
    "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
    context.Response.AppendHeader("content-disposition",
    string.Format("attachment;filename={0}.xlsx", name));
    context.Response.End();
    }





    "Alexey Smirnov" wrote:

    > On Aug 28, 5:48 am, JohnE <> wrote:
    > > I have a gridview that is exported to excel. The code below works. But, the
    > > only drawback is the gridview has 6 pages (sometimes more, sometimes less).
    > > When the export occurs, it takes the grid and the pages and puts it into
    > > excel. I need to have a regular looking spreadsheet (all 112 records)
    > > without the gridview paging look. I have tried putting GridView1.AllowPaging
    > > = false in different spots of the code but it is not working. Here is the
    > > exporting code that I am using.
    > >
    > > protected void btnExportToExcel_Click(object sender, EventArgs e)
    > > {
    > > PrepareGridViewForExport(GridView1);
    > > ExportGridToExcel(GridView1, "test");
    > > }
    > >
    > > public override void VerifyRenderingInServerForm(Control control)
    > > {
    > > // Confirms that an HtmlForm control is rendered for the specified
    > > ASP.NET server control at run time.
    > > return;
    > > }
    > >
    > > public void ExportGridToExcel(GridView grdGridView, string fileName)
    > > {
    > > Response.Clear();
    > > Response.Buffer = true;
    > > Response.AddHeader("content-disposition",
    > > string.Format("attachment;filename={0}.xls", fileName));
    > > Response.Charset = "";
    > > Response.ContentType =
    > > "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
    > > StringWriter stringWrite = new StringWriter();
    > > HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
    > > grdGridView.RenderControl(htmlWrite);
    > > Response.Write(stringWrite.ToString());
    > > Response.End();
    > > }
    > >
    > > private void PrepareGridViewForExport(Control gv)
    > > {
    > > LinkButton lb = new LinkButton();
    > > Literal lit = new Literal();
    > > string name = String.Empty;
    > >
    > > for (int i = 0; i < gv.Controls.Count; i++)
    > > {
    > > if (gv.Controls.GetType() == typeof(LinkButton))
    > > {
    > > lit.Text = (gv.Controls as LinkButton).Text;
    > > gv.Controls.Remove(gv.Controls);
    > > gv.Controls.AddAt(i, lit);
    > > }
    > > else if (gv.Controls.GetType() == typeof(DropDownList))
    > > {
    > > lit.Text = (gv.Controls as DropDownList).SelectedItem.Text;
    > > gv.Controls.Remove(gv.Controls);
    > > gv.Controls.AddAt(i, lit);
    > > }
    > > else if (gv.Controls.GetType() == typeof(CheckBox))
    > > {
    > > lit.Text = (gv.Controls as CheckBox)..Checked ? "True" :
    > > "False";
    > > gv.Controls.Remove(gv.Controls);
    > > gv.Controls.AddAt(i, lit);
    > > }
    > > if (gv.Controls.HasControls())
    > > {
    > > PrepareGridViewForExport(gv.Controls);
    > > }
    > > }
    > > }
    > >
    > > Can someone see the reason the paging is still there in the spreadsheet and
    > > what is needed to rectifiy it?
    > >
    > > Thanks.
    > >
    > > John

    >
    > If I were you I would export data using the datasource but not the
    > grid itself. In this case you don't need to do anything with grid, you
    > would just loop through the dataset and push all rows to the browser.
    >
    > Similar to this code
    > public static void ExportToSpreadsheet(DataTable table, string name)
    > {
    > HttpContext context = HttpContext.Current;
    > context.Response.Clear();
    > foreach (DataColumn column in table.Columns)
    > {
    > context.Response.Write(column.ColumnName + ";");
    > }
    > context.Response.Write(Environment.NewLine);
    > foreach (DataRow row in table.Rows)
    > {
    > for (int i = 0; i < table.Columns.Count; i++)
    > {
    > context.Response.Write(row.ToString().Replace(";",
    > string.Empty) + ";");
    > }
    > context.Response.Write(Environment.NewLine);
    > }
    > context.Response.ContentType = "text/csv";
    > context.Response.AppendHeader("Content-Disposition", "attachment;
    > filename=" + name + ".csv");
    > context.Response.End();
    > }
    >
    > http://www.webpronews.com/expertarticles/2006/11/28/aspnet-export-a-datatable-to-excel
    >
    > Regarding your problem. I don't see where GridView1.AllowPaging is
    > set. Did you try to set PageSize?
    >
     
    JohnE, Aug 29, 2009
    #4
  5. JohnE

    JohnE Guest

    Alexey, I took a break from it all and got back and got it to work.
    Thanks for your patience.
    John


    "JohnE" wrote:

    > Alexey, well I gave it a try. I got all the way to excel but no data showed
    > up. I am providing the info below that I used to get as far as I did. The
    > table in the Export lin of the btn click errored out until I added the
    > DataTable table line in the same event. I even found the correct contenttype.
    >
    > See something wrong that I am missing?
    > Thanks.
    > John
    >
    > protected void btnExportToExcel_Click(object sender, EventArgs e)
    > {
    > GridView1.DataSource = bindgrid();
    > GridView1.DataBind();
    > DataTable table = new DataTable();
    > GridView1.AllowPaging = false;
    > GridView1.PageSize = 1;
    >
    > PrepareGridViewForExport(GridView1);
    > //ExportGridToExcel(GridView1, "test");
    >
    > ExportToSpreadsheet(table, "test");
    > }
    >
    > public static void ExportToSpreadsheet(DataTable table, string name)
    > {
    > HttpContext context = HttpContext.Current;
    > context.Response.Clear();
    >
    > foreach (DataColumn column in table.Columns)
    > {
    > context.Response.Write(column.ColumnName + " ;");
    > }
    > context.Response.Write(Environment.NewLine);
    >
    > foreach (DataRow row in table.Rows)
    > {
    > for (int i = 0; i < table.Columns.Count; i++)
    > {
    > context.Response.Write(row.ToString().Replace(";",
    > string.Empty) + ";");
    > }
    > context.Response.Write(Environment.NewLine);
    > }
    > context.Response.ContentType =
    > "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
    > context.Response.AppendHeader("content-disposition",
    > string.Format("attachment;filename={0}.xlsx", name));
    > context.Response.End();
    > }
    >
    >
    >
    >
    >
    > "Alexey Smirnov" wrote:
    >
    > > On Aug 28, 5:48 am, JohnE <> wrote:
    > > > I have a gridview that is exported to excel. The code below works. But, the
    > > > only drawback is the gridview has 6 pages (sometimes more, sometimes less).
    > > > When the export occurs, it takes the grid and the pages and puts it into
    > > > excel. I need to have a regular looking spreadsheet (all 112 records)
    > > > without the gridview paging look. I have tried putting GridView1.AllowPaging
    > > > = false in different spots of the code but it is not working. Here is the
    > > > exporting code that I am using.
    > > >
    > > > protected void btnExportToExcel_Click(object sender, EventArgs e)
    > > > {
    > > > PrepareGridViewForExport(GridView1);
    > > > ExportGridToExcel(GridView1, "test");
    > > > }
    > > >
    > > > public override void VerifyRenderingInServerForm(Control control)
    > > > {
    > > > // Confirms that an HtmlForm control is rendered for the specified
    > > > ASP.NET server control at run time.
    > > > return;
    > > > }
    > > >
    > > > public void ExportGridToExcel(GridView grdGridView, string fileName)
    > > > {
    > > > Response.Clear();
    > > > Response.Buffer = true;
    > > > Response.AddHeader("content-disposition",
    > > > string.Format("attachment;filename={0}.xls", fileName));
    > > > Response.Charset = "";
    > > > Response.ContentType =
    > > > "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
    > > > StringWriter stringWrite = new StringWriter();
    > > > HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
    > > > grdGridView.RenderControl(htmlWrite);
    > > > Response.Write(stringWrite.ToString());
    > > > Response.End();
    > > > }
    > > >
    > > > private void PrepareGridViewForExport(Control gv)
    > > > {
    > > > LinkButton lb = new LinkButton();
    > > > Literal lit = new Literal();
    > > > string name = String.Empty;
    > > >
    > > > for (int i = 0; i < gv.Controls.Count; i++)
    > > > {
    > > > if (gv.Controls.GetType() == typeof(LinkButton))
    > > > {
    > > > lit.Text = (gv.Controls as LinkButton).Text;
    > > > gv.Controls.Remove(gv.Controls);
    > > > gv.Controls.AddAt(i, lit);
    > > > }
    > > > else if (gv.Controls.GetType() == typeof(DropDownList))
    > > > {
    > > > lit.Text = (gv.Controls as DropDownList).SelectedItem.Text;
    > > > gv.Controls.Remove(gv.Controls);
    > > > gv.Controls.AddAt(i, lit);
    > > > }
    > > > else if (gv.Controls.GetType() == typeof(CheckBox))
    > > > {
    > > > lit.Text = (gv.Controls as CheckBox)..Checked ? "True" :
    > > > "False";
    > > > gv.Controls.Remove(gv.Controls);
    > > > gv.Controls.AddAt(i, lit);
    > > > }
    > > > if (gv.Controls.HasControls())
    > > > {
    > > > PrepareGridViewForExport(gv.Controls);
    > > > }
    > > > }
    > > > }
    > > >
    > > > Can someone see the reason the paging is still there in the spreadsheet and
    > > > what is needed to rectifiy it?
    > > >
    > > > Thanks.
    > > >
    > > > John

    > >
    > > If I were you I would export data using the datasource but not the
    > > grid itself. In this case you don't need to do anything with grid, you
    > > would just loop through the dataset and push all rows to the browser.
    > >
    > > Similar to this code
    > > public static void ExportToSpreadsheet(DataTable table, string name)
    > > {
    > > HttpContext context = HttpContext.Current;
    > > context.Response.Clear();
    > > foreach (DataColumn column in table.Columns)
    > > {
    > > context.Response.Write(column.ColumnName + ";");
    > > }
    > > context.Response.Write(Environment.NewLine);
    > > foreach (DataRow row in table.Rows)
    > > {
    > > for (int i = 0; i < table.Columns.Count; i++)
    > > {
    > > context.Response.Write(row.ToString().Replace(";",
    > > string.Empty) + ";");
    > > }
    > > context.Response.Write(Environment.NewLine);
    > > }
    > > context.Response.ContentType = "text/csv";
    > > context.Response.AppendHeader("Content-Disposition", "attachment;
    > > filename=" + name + ".csv");
    > > context.Response.End();
    > > }
    > >
    > > http://www.webpronews.com/expertarticles/2006/11/28/aspnet-export-a-datatable-to-excel
    > >
    > > Regarding your problem. I don't see where GridView1.AllowPaging is
    > > set. Did you try to set PageSize?
    > >
     
    JohnE, Aug 29, 2009
    #5
  6. On Aug 29, 8:36 pm, JohnE <> wrote:
    > Alexey, I took a break from it all and got back and got it to work.  
    > Thanks for your patience.
    > John
    >
    >
    >
    > "JohnE" wrote:
    > > Alexey, well I gave it a try.  I got all the way to excel but no data showed
    > > up.  I am providing the info below that I used to get as far as I did..  The
    > > table in the Export lin of the btn click errored out until I added the
    > > DataTable table line in the same event.  I even found the correct contenttype.

    >
    > > See something wrong that I am missing?
    > > Thanks.
    > > John

    >
    > >     protected void btnExportToExcel_Click(object sender, EventArgs e)
    > >     {
    > >         GridView1.DataSource = bindgrid();
    > >         GridView1.DataBind();
    > >         DataTable table = new DataTable();
    > >         GridView1.AllowPaging = false;
    > >         GridView1.PageSize = 1;

    >
    > >         PrepareGridViewForExport(GridView1);
    > >         //ExportGridToExcel(GridView1, "test");

    >
    > >         ExportToSpreadsheet(table, "test");
    > >     }

    >
    > >     public static void ExportToSpreadsheet(DataTable table, string name)
    > >     {
    > >         HttpContext context = HttpContext.Current;
    > >         context.Response.Clear();

    >
    > >         foreach (DataColumn column in table.Columns)
    > >         {
    > >             context.Response.Write(column.ColumnName + " ;");
    > >         }
    > >         context.Response.Write(Environment.NewLine);

    >
    > >         foreach (DataRow row in table.Rows)
    > >         {
    > >             for (int i = 0; i < table.Columns.Count; i++)
    > >             {
    > >                 context.Response.Write(row.ToString().Replace(";",
    > > string.Empty) + ";");
    > >             }
    > >             context.Response.Write(Environment.NewLine);
    > >         }
    > >         context.Response.ContentType =
    > > "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
    > >         context.Response.AppendHeader("content-disposition",
    > > string.Format("attachment;filename={0}.xlsx", name));
    > >         context.Response.End();
    > >     }

    >
    > > "Alexey Smirnov" wrote:

    >
    > > > On Aug 28, 5:48 am, JohnE <> wrote:
    > > > > I have a gridview that is exported to excel.  The code below works.  But, the
    > > > > only drawback is the gridview has 6 pages (sometimes more, sometimes less).  
    > > > > When the export occurs, it takes the grid and the pages and puts it into
    > > > > excel.  I need to have a regular looking spreadsheet (all 112 records)
    > > > > without the gridview paging look.  I have tried putting GridView1..AllowPaging
    > > > > = false in different spots of the code but it is not working.  Here is the
    > > > > exporting code that I am using.

    >
    > > > >     protected void btnExportToExcel_Click(object sender, EventArgs e)
    > > > >     {
    > > > >         PrepareGridViewForExport(GridView1);
    > > > >         ExportGridToExcel(GridView1, "test");
    > > > >     }

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

    >
    > > > >     public void ExportGridToExcel(GridView grdGridView, string fileName)
    > > > >     {
    > > > >         Response.Clear();
    > > > >         Response.Buffer = true;
    > > > >         Response.AddHeader("content-disposition",
    > > > > string.Format("attachment;filename={0}.xls", fileName));
    > > > >         Response.Charset = "";
    > > > >         Response.ContentType =
    > > > > "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
    > > > >         StringWriter stringWrite = new StringWriter();
    > > > >         HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
    > > > >         grdGridView.RenderControl(htmlWrite);
    > > > >         Response.Write(stringWrite.ToString());
    > > > >         Response.End();
    > > > >     }

    >
    > > > >     private void PrepareGridViewForExport(Control gv)
    > > > >     {
    > > > >         LinkButton lb = new LinkButton();
    > > > >         Literal lit = new Literal();
    > > > >         string name = String.Empty;

    >
    > > > >         for (int i = 0; i < gv.Controls.Count; i++)
    > > > >         {
    > > > >             if (gv.Controls.GetType() == typeof(LinkButton))
    > > > >             {
    > > > >                 lit.Text = (gv.Controls as LinkButton).Text;
    > > > >                 gv.Controls.Remove(gv.Controls);
    > > > >                 gv.Controls.AddAt(i, lit);
    > > > >             }
    > > > >             else if (gv.Controls.GetType() == typeof(DropDownList))
    > > > >             {
    > > > >                 lit.Text = (gv.Controls as DropDownList).SelectedItem.Text;
    > > > >                 gv.Controls.Remove(gv.Controls);
    > > > >                 gv.Controls.AddAt(i, lit);
    > > > >             }
    > > > >             else if (gv.Controls.GetType() == typeof(CheckBox))
    > > > >             {
    > > > >                 lit.Text = (gv.Controls as CheckBox)..Checked ? "True" :
    > > > > "False";
    > > > >                 gv.Controls.Remove(gv.Controls);
    > > > >                 gv.Controls.AddAt(i, lit);
    > > > >             }
    > > > >             if (gv.Controls.HasControls())
    > > > >             {
    > > > >                 PrepareGridViewForExport(gv.Controls);
    > > > >             }
    > > > >         }
    > > > >     }

    >
    > > > > Can someone see the reason the paging is still there in the spreadsheet and
    > > > > what is needed to rectifiy it?

    >
    > > > > Thanks.

    >
    > > > > John

    >
    > > > If I were you I would export data using the datasource but not the
    > > > grid itself. In this case you don't need to do anything with grid, you
    > > > would just loop through the dataset and push all rows to the browser.

    >
    > > > Similar to this code
    > > > public static void ExportToSpreadsheet(DataTable table, string name)
    > > > {
    > > >    HttpContext context = HttpContext.Current;
    > > >    context.Response.Clear();
    > > >    foreach (DataColumn column in table.Columns)
    > > >    {
    > > >     context.Response.Write(column.ColumnName + ";");
    > > >    }
    > > >    context.Response.Write(Environment.NewLine);
    > > >    foreach (DataRow row in table.Rows)
    > > >    {
    > > >     for (int i = 0; i < table.Columns.Count; i++)
    > > >     {
    > > >      context.Response.Write(row.ToString().Replace(";",
    > > > string.Empty) + ";");
    > > >     }
    > > >     context.Response.Write(Environment.NewLine);
    > > >    }
    > > >    context.Response.ContentType = "text/csv";
    > > >    context.Response.AppendHeader("Content-Disposition", "attachment;
    > > > filename=" + name + ".csv");
    > > >    context.Response.End();
    > > > }

    >
    > > >http://www.webpronews.com/expertarticles/2006/11/28/aspnet-export-a-d....

    >
    > > > Regarding your problem. I don't see where GridView1.AllowPaging is
    > > > set. Did you try to set PageSize?- Hide quoted text -

    >
    > - Show quoted text -


    I think the problem was here: DataTable table = new DataTable();

    Anyway, glad that it works now for you
     
    Alexey Smirnov, Aug 30, 2009
    #6
  7. JohnE

    SAL Guest

    The article below is incorrect. A CSV file is a comma separated file not a
    semi-colon separated file. The author incorrectly inserts semi-colons rather
    than commas.

    S

    "Alexey Smirnov" <> wrote in message
    news:...
    On Aug 28, 5:48 am, JohnE <> wrote:
    > I have a gridview that is exported to excel. The code below works. But,
    > the
    > only drawback is the gridview has 6 pages (sometimes more, sometimes
    > less).
    > When the export occurs, it takes the grid and the pages and puts it into
    > excel. I need to have a regular looking spreadsheet (all 112 records)
    > without the gridview paging look. I have tried putting
    > GridView1.AllowPaging
    > = false in different spots of the code but it is not working. Here is the
    > exporting code that I am using.
    >
    > protected void btnExportToExcel_Click(object sender, EventArgs e)
    > {
    > PrepareGridViewForExport(GridView1);
    > ExportGridToExcel(GridView1, "test");
    > }
    >
    > public override void VerifyRenderingInServerForm(Control control)
    > {
    > // Confirms that an HtmlForm control is rendered for the specified
    > ASP.NET server control at run time.
    > return;
    > }
    >
    > public void ExportGridToExcel(GridView grdGridView, string fileName)
    > {
    > Response.Clear();
    > Response.Buffer = true;
    > Response.AddHeader("content-disposition",
    > string.Format("attachment;filename={0}.xls", fileName));
    > Response.Charset = "";
    > Response.ContentType =
    > "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
    > StringWriter stringWrite = new StringWriter();
    > HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
    > grdGridView.RenderControl(htmlWrite);
    > Response.Write(stringWrite.ToString());
    > Response.End();
    > }
    >
    > private void PrepareGridViewForExport(Control gv)
    > {
    > LinkButton lb = new LinkButton();
    > Literal lit = new Literal();
    > string name = String.Empty;
    >
    > for (int i = 0; i < gv.Controls.Count; i++)
    > {
    > if (gv.Controls.GetType() == typeof(LinkButton))
    > {
    > lit.Text = (gv.Controls as LinkButton).Text;
    > gv.Controls.Remove(gv.Controls);
    > gv.Controls.AddAt(i, lit);
    > }
    > else if (gv.Controls.GetType() == typeof(DropDownList))
    > {
    > lit.Text = (gv.Controls as DropDownList).SelectedItem.Text;
    > gv.Controls.Remove(gv.Controls);
    > gv.Controls.AddAt(i, lit);
    > }
    > else if (gv.Controls.GetType() == typeof(CheckBox))
    > {
    > lit.Text = (gv.Controls as CheckBox).Checked ? "True" :
    > "False";
    > gv.Controls.Remove(gv.Controls);
    > gv.Controls.AddAt(i, lit);
    > }
    > if (gv.Controls.HasControls())
    > {
    > PrepareGridViewForExport(gv.Controls);
    > }
    > }
    > }
    >
    > Can someone see the reason the paging is still there in the spreadsheet
    > and
    > what is needed to rectifiy it?
    >
    > Thanks.
    >
    > John


    If I were you I would export data using the datasource but not the
    grid itself. In this case you don't need to do anything with grid, you
    would just loop through the dataset and push all rows to the browser.

    Similar to this code
    public static void ExportToSpreadsheet(DataTable table, string name)
    {
    HttpContext context = HttpContext.Current;
    context.Response.Clear();
    foreach (DataColumn column in table.Columns)
    {
    context.Response.Write(column.ColumnName + ";");
    }
    context.Response.Write(Environment.NewLine);
    foreach (DataRow row in table.Rows)
    {
    for (int i = 0; i < table.Columns.Count; i++)
    {
    context.Response.Write(row.ToString().Replace(";",
    string.Empty) + ";");
    }
    context.Response.Write(Environment.NewLine);
    }
    context.Response.ContentType = "text/csv";
    context.Response.AppendHeader("Content-Disposition", "attachment;
    filename=" + name + ".csv");
    context.Response.End();
    }

    http://www.webpronews.com/expertarticles/2006/11/28/aspnet-export-a-datatable-to-excel

    Regarding your problem. I don't see where GridView1.AllowPaging is
    set. Did you try to set PageSize?
     
    SAL, Sep 4, 2009
    #7
  8. On Sep 4, 10:41 pm, "SAL" <> wrote:
    > The article below is incorrect. A CSV file is a comma separated file not a
    > semi-colon separated file. The author incorrectly inserts semi-colons rather
    > than commas.
    >


    This is correct for all countries where semi-colon is defined as a
    list separator (in Europe, for instance). US uses comma, you can see
    it if you go to Control Panel - Regional Options - List Separator
     
    Alexey Smirnov, Sep 7, 2009
    #8
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Luis Esteban Valencia
    Replies:
    1
    Views:
    1,396
    Carl Prothman [MVP]
    Jan 12, 2005
  2. =?Utf-8?B?S2VubnkgTS4=?=

    Issue exporting DataGrid to Excel

    =?Utf-8?B?S2VubnkgTS4=?=, Mar 15, 2006, in forum: ASP .Net
    Replies:
    2
    Views:
    383
    =?Utf-8?B?S2VubnkgTS4=?=
    Mar 15, 2006
  3. Mike P

    exporting gridview to excel

    Mike P, Jun 8, 2006, in forum: ASP .Net
    Replies:
    0
    Views:
    623
    Mike P
    Jun 8, 2006
  4. Replies:
    3
    Views:
    7,720
    reena_pgdi
    May 21, 2010
  5. Doogie
    Replies:
    1
    Views:
    455
    sloan
    Nov 19, 2008
Loading...

Share This Page