Export to Excel from gridview

Question

I have code for exporting to Excel. In my gridview I set paging to display the number of records in pagecount.

But in my export to Excel it is not giving me entire records, instead it is showing me the same paging with six records.

My code:

string attachment = "attachment; filename=Contacts.xls";
Response.ClearContent();
Response.AddHeader("content-disposition", attachment);
Response.ContentType = "application/ms-excel";
StringWriter sw = new StringWriter();
HtmlTextWriter htw = new HtmlTextWriter(sw);

// Create a form to contain the grid
HtmlForm frm = new HtmlForm();

GrdDynamicControls.AllowPaging = false;
GrdDynamicControls.Parent.Controls.Add(frm);

frm.Attributes["runat"] = "server";
frm.Controls.Add(GrdDynamicControls);
frm.RenderControl(htw);

//GridView1.RenderControl(htw);
Response.Write(sw.ToString());
Response.End();

How do I change or disable the paging to get all the records from my gridview?

I have numbers in gridview as +9199, etc., but after exporting it it is showing me it in the format of 9.99, etc.

How do I pass the formatcells in numbers from here?

Answer 1

I'm not familiar with ASP.NET, but I believe that you bind some datasource (dataTable or List<>) to your grid. Export the data source instead of dataGrid.

Answer 2

You can export a gridview's datasource (for example, dataset) to Excel.

Here is sample code for doing this. You can refer to Howto: Export a dataset to Excel (C# / ASP.NET) for more information.

using System;
using System.Data;
using System.IO;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace Whatever
{
    ///
    /// This class provides a method to write a dataset to the HttpResponse as
    /// an Excel file.
    ///
    public class ExcelExport
    {
        public static void ExportDataSetToExcel(DataSet ds, string filename)
        {
            HttpResponse response = HttpContext.Current.Response;

            // First let's clean up the response.object.
            response.Clear();
            response.Charset = "";

            // Set the response mime type for Excel.
            response.ContentType = "application/vnd.ms-excel";
            response.AddHeader("Content-Disposition", "attachment;filename=\"" + filename + "\"");

            // Create a string writer.
            using (StringWriter sw = new StringWriter())
            {
                using (HtmlTextWriter htw = new HtmlTextWriter(sw))
                {
                    // Instantiate a datagrid
                    DataGrid dg = new DataGrid();
                    dg.DataSource = ds.Tables[0];
                    dg.DataBind();
                    dg.RenderControl(htw);
                    response.Write(sw.ToString());
                    response.End();
                }
            }
        }
    }
}

Answer 3

I believe you would need to rebind your grid after disabling paging to get all your records.

I'd also echo Orsol's comment; where instead of exporting what's in the grid; export the datasource.