Export All Data To Excel Sheet (Paging in a Gridview )

see,

Tell me first thing, when your binding data to your gridview then are you fetching all data or just page size (say 10) ? Or you are fetching all at once then gridview is doing pagination for you?

If you are fetching all records at once then simply use that SP to export to excel with below method, or create one additional SP to get all records.

        public static void DataSetToExcel(DataSet dsInput, string filename)
        {
            System.Web.HttpResponse response = HttpContext.Current.Response;
            var excelXml = GetExcelXml(dsInput, filename);
            response.Clear();
            response.AppendHeader("Content-Type", "application/vnd.ms-excel");
            response.AppendHeader("Content-disposition", "attachment; filename=" + filename);
            response.Write(excelXml);
            response.Flush();
            response.End();
        }

Just pass the dataset to above method and it will work fine. I often use this method.

Hi Deepak,

Use following code, you get exact output

 

HTML Markup

The following HTML Markup consists of an ASP.Net GridView control with three BoundField columns and a Button.

Paging has been enabled for the GridView control.

 

Namespaces

You will need to import the following namespaces

using System.IO;
using System.Data;
using System.Drawing;
using System.Data.SqlClient;
using System.Configuration;

 

Populating the GridView control

Inside the Page Load event, the GridView is populated with records from the Customers Table of the Northwind Database.

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        this.BindGrid();
    }
}
 
private void BindGrid()
{
    string strConnString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(strConnString))
    {
        using (SqlCommand cmd = new SqlCommand("SELECT * FROM Customers"))
        {
            using (SqlDataAdapter sda = new SqlDataAdapter())
            {
                cmd.Connection = con;
                sda.SelectCommand = cmd;
                using (DataTable dt = new DataTable())
                {
                    sda.Fill(dt);
                    GridView1.DataSource = dt;
                    GridView1.DataBind();
                }
            }
        }
    }
}

 

Implement Paging in GridView

The OnPageIndexChanging event handles the Pagination in the GridView.

Inside the OnPageIndexChanging event handler, the GridView’s PageIndex property is updated and the GridView is again populated from Database by making call to the BindGrid method.

protected void OnPageIndexChanging(object sender, GridViewPageEventArgs e)
{
    GridView1.PageIndex = e.NewPageIndex;
    this.BindGrid();
}

 

Export GridView with Paging Enabled to Excel

When the Export Button is clicked, first the Content Type is set in the Response Stream in order to specify the Browser that the downloading file is an Excel file.

The Paging is disabled for the GridView by setting the AllowPaging property to False and the GridView is again populated with records from the Database.

Then a loop is executed over the GridView rows and the style and formatting are applied to the Row and Alternating Row of the GridView control.

protected void ExportToExcel(object sender, EventArgs e)
{
    Response.Clear();
    Response.Buffer = true;
    Response.AddHeader("content-disposition", "attachment;filename=GridViewExport.xls");
    Response.Charset = "";
    Response.ContentType = "application/vnd.ms-excel";
    using (StringWriter sw = new StringWriter())
    {
        HtmlTextWriter hw = new HtmlTextWriter(sw);
 
        //To Export all pages
        GridView1.AllowPaging = false;
        this.BindGrid();
 
        GridView1.HeaderRow.BackColor = Color.White;
        foreach (TableCell cell in GridView1.HeaderRow.Cells)
        {
            cell.BackColor = GridView1.HeaderStyle.BackColor;
        }
        foreach (GridViewRow row in GridView1.Rows)
        {
            row.BackColor = Color.White;
            foreach (TableCell cell in row.Cells)
            {
                if (row.RowIndex % 2 == 0)
                {
                    cell.BackColor = GridView1.AlternatingRowStyle.BackColor;
                }
                else
                {
                    cell.BackColor = GridView1.RowStyle.BackColor;
                }
                cell.CssClass = "textmode";
            }
        }
 
        GridView1.RenderControl(hw);
           
        //style to format numbers to string
        string style = @"";
        Response.Write(style);
        Response.Output.Write(sw.ToString());
        Response.Flush();
        Response.End();
    }
}
 
public override void VerifyRenderingInServerForm(Control control)
{
    /* Verifies that the control is rendered */
}

Hi Deepak,

try bootstrap-table, Please refer below link

https://examples.bootstrap-table.com/index.html#extensions/export.html

Export one excel from multiple paged gridview, just use these line before calling the convert function "ExportToExcel()".

GridView1.AllowPaging = false;
bindGridView();