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

Question

Hye all, I want to Export All Data (Paging in a Gridview ) To Excel sheet.

Current Status: Export Data Working But getting only 1 Page data when i am into that page

I want to export data ( Data in all pages ) into excel sheet ..

Sachin Singh · Answer

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.

Uday Dodiya · Answer

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 = @" .textmode { } "; Response.Write(style); Response.Output.Write(sw.ToString()); Response.Flush(); Response.End(); } } public override void VerifyRenderingInServerForm(Control control) { /* Verifies that the control is rendered */ }

Vishal Yelve · Answer

Hi Deepak, try bootstrap-table, Please refer below link https://examples.bootstrap-table.com/index.html#extensions/export.html

Vishal Joshi · Answer

Export one excel from multiple paged gridview, just use these line before calling the convert function "ExportToExcel()". GridView1.AllowPaging = false; bindGridView();

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

Answers (4)