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