Export Gridview data to excel file

Question

I am showing some data in gridview and hide 2 columns in gridview and i want show that hiden column in excel. how to do that.Please help me.

Sachin Singh · Answer

the below code will export all columns ,please test this 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 */ }

Rajanikant Hawaldar · Answer

in that case you can export your datatable/dataset to excell

Mageshwaran R · Answer

Hi Sonali, Try this code, div > asp:GridView ID = "GridView1" runat = "server" AutoGenerateColumns = "false" Font-Names = "Arial" Font-Size = "11pt" AlternatingRowStyle-BackColor = "#C2D69B" HeaderStyle-BackColor = "green" AllowPaging = "true" OnPageIndexChanging = "OnPaging" > Columns > asp:TemplateField > HeaderTemplate > asp:CheckBox ID = "chkCol0" runat = "server" Checked = "true" /> asp:Label ID = "lblCol0" runat = "server" Text = "CustomerID" /> HeaderTemplate > ItemTemplate > asp:Label ID = "lblCustomerID" runat = "server" Text = ' ' /> ItemTemplate > asp:TemplateField > asp:TemplateField > HeaderTemplate > asp:CheckBox ID = "chkCol1" runat = "server" Checked = "true" /> asp:Label ID = "lblCol1" runat = "server" Text = "ContactName" /> HeaderTemplate > ItemTemplate > asp:Label ID = "lblContactName" runat = "server" Text = ' ' /> ItemTemplate > asp:TemplateField > asp:TemplateField > HeaderTemplate > asp:CheckBox ID = "chkCol2" runat = "server" Checked = "true" /> asp:Label ID = "lblCol2" runat = "server" Text = "City" > asp:Label > HeaderTemplate > ItemTemplate > asp:Label ID = "lblCity" runat = "server" Text = ' ' /> ItemTemplate > asp:TemplateField > Columns > asp:GridView > div > br /> asp:Button ID = "btnExportExcel" runat = "server" Text = "ExportToExcel" OnClick = "btnExportExcel_Click" /> using System.Data; using System.Data.SqlClient; using System.IO; protected void Page_Load( object sender, EventArgs e) { if (IsPostBack) GetCheckBoxStates(); BindGrid(); } private void BindGrid() { string strQuery = "select CustomerID,City,ContactName from customers" ; DataTable dt = new DataTable(); String strConnString = System.Configuration.ConfigurationManager.ConnectionStrings[ "conString" ].ConnectionString; SqlConnection con = new SqlConnection(strConnString); SqlDataAdapter sda = new SqlDataAdapter(); SqlCommand cmd = new SqlCommand(strQuery); cmd.CommandType = CommandType.Text; cmd.Connection = con; try { con.Open(); sda.SelectCommand = cmd; sda.Fill(dt); GridView1.DataSource = dt; GridView1.DataBind(); } catch (Exception ex) { throw ex; } finally { con.Close(); sda.Dispose(); con.Dispose(); } } private void GetCheckBoxStates() { CheckBox chkCol0 = (CheckBox)GridView1.HeaderRow.Cells[0].FindControl( "chkCol0" ); CheckBox chkCol1 = (CheckBox)GridView1.HeaderRow.Cells[0].FindControl( "chkCol1" ); CheckBox chkCol2 = (CheckBox)GridView1.HeaderRow.Cells[0].FindControl( "chkCol2" ); ArrayList arr; if (ViewState[ "States" ] == null ) { arr = new ArrayList(); } else { arr = (ArrayList)ViewState[ "States" ]; } arr.Add(chkCol0.Checked); arr.Add(chkCol1.Checked); arr.Add(chkCol2.Checked); ViewState[ "States" ] = arr; } public override void VerifyRenderingInServerForm(Control control) { /* Verifies that the control is rendered */ } protected void btnExportExcel_Click( 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" ; StringWriter sw = new StringWriter(); HtmlTextWriter hw = new HtmlTextWriter(sw); GridView1.AllowPaging = false ; GridView1.DataBind(); GridView1.HeaderRow.Style.Add( "background-color" , "#FFFFFF" ); GridView1.HeaderRow.Cells[0].Style.Add( "background-color" , "green" ); GridView1.HeaderRow.Cells[1].Style.Add( "background-color" , "green" ); GridView1.HeaderRow.Cells[2].Style.Add( "background-color" , "green" ); ArrayList arr = (ArrayList)ViewState[ "States" ]; GridView1.HeaderRow.Cells[0].Visible = Convert.ToBoolean(arr[0]); GridView1.HeaderRow.Cells[1].Visible = Convert.ToBoolean(arr[1]); GridView1.HeaderRow.Cells[2].Visible = Convert.ToBoolean(arr[2]); GridView1.HeaderRow.Cells[0].FindControl( "chkCol0" ).Visible = false ; GridView1.HeaderRow.Cells[1].FindControl( "chkCol1" ).Visible = false ; GridView1.HeaderRow.Cells[2].FindControl( "chkCol2" ).Visible = false ; for ( int i = 0; i { GridViewRow row = GridView1.Rows[i]; row.Cells[0].Visible = Convert.ToBoolean(arr[0]); row.Cells[1].Visible = Convert.ToBoolean(arr[1]); row.Cells[2].Visible = Convert.ToBoolean(arr[2]); row.BackColor = System.Drawing.Color.White; row.Attributes.Add( "class" , "textmode" ); if (i % 2 != 0) { row.Cells[0].Style.Add( "background-color" , "#C2D69B" ); row.Cells[1].Style.Add( "background-color" , "#C2D69B" ); row.Cells[2].Style.Add( "background-color" , "#C2D69B" ); } } GridView1.RenderControl(hw); string style = @ " .textmode { } " ; Response.Write(style); Response.Output.Write(sw.ToString()); Response.End(); } protected void OnPaging( object sender, GridViewPageEventArgs e) { GridView1.PageIndex = e.NewPageIndex; GridView1.DataBind(); }

Sapna Girish · Answer

check this link out https://www.e-iceblue.com/Tutorials/Spire.XLS/Spire.XLS-Program-Guide/Data-Export-/Import-Export-Datatable-to-Excel-from-Database.html

Amit Gupta · Answer

What's complication in this? Loop through all the columns when exporting. Share your code snippet so we can make amendments on that

Export Gridview data to excel file

Answers (5)