how to export data grid view into excel and validate c#

Question

Hello friends
I m facing a very Big problem right now actually I have transaction form which is bind by data grid view.and all the columns is validated in data grid view now when I m export it all the validation is not coming in excel for example how to define range in cell or columns in excel ...
If user enter 10 marks from 10 then grade should be come A in next cell so I don't have any idea how to make it in excel file.... Hope u r understand what I want ...

Thanks in advance

Anshul Jain · Answer

Hii Naeem Add this straight forwad funtion used to help convert datatable data to excel DataTable dt = (DataTable)DataGridView1.DataSource(); public bool exporttoexcel(DataTable dt string path) { bool export = false; //open file StreamWriter wr = new StreamWriter(@path); try { for (int i = 0; i { wr.Write(dt.Columns[i].ToString().ToUpper() + 	); } wr.WriteLine(); //write rows to excel file for (int i = 0; i { for (int j = 0; j { if (dt.Rows[i][j] != null) { wr.Write(Convert.ToString(dt.Rows[i][j]) + 	); } else { wr.Write(	); } } //go to next line wr.WriteLine(); } //close file wr.Close(); export = true; } catch (Exception ex) { export = false; throw ex; } return export; } With Thanks ---------------- web design in agra

Upendra Pratap Shahi · Answer

yesuse SpreadSheetGear this is a DLL and use this in your code and validate through c#. This works with c#

Naeem Khan · Answer

thank you so much for your reply sir but i want to give validation from C#.. if i validate it that user enter only 10 marks or less then 10 marks then user can't enter greater then 10.. i want to validate it in this way ..

Upendra Pratap Shahi · Answer

Try to use spread sheet gear which is very easy and performing fast. All the validation done easily through spreadsheet gear.

Nilesh Jadav · Answer

Your design.aspx page: @ Page Language =C# AutoEventWireup =true CodeFile =Default.aspx.cs Inherits =_Default %> DOCTYPE htmlPUBLIC -//W3C//DTD XHTML 1.0 Transitional//ENhttp://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd> html xmlns =http://www.w3.org/1999/xhtml> head runat =server> title > title > head > body > form id =form1 runat =server> div > asp : GridView ID =GridView1 runat =server AllowPaging =True AutoGenerateColumns =False BackColor =White BorderColor =#999999 BorderStyle =None BorderWidth =1px CellPadding =3 DataKeyNames =id GridLines =Vertical onpageindexchanging =GridView1_PageIndexChanging1 PageSize =5> AlternatingRowStyle BackColor =#DCDCDC/> Columns > asp : TemplateField HeaderText =Name> EditItemTemplate > asp : TextBox ID =TextBox1 runat =server Text =' # Bind(name) %> '> asp : TextBox > EditItemTemplate > ItemTemplate > asp : Label ID =Label1 runat =server Text =' # Bind(name) %> '> asp : Label > ItemTemplate > asp : TemplateField > asp : TemplateField HeaderText =Education> EditItemTemplate > asp : TextBox ID =TextBox2 runat =server Text =' # Bind(education) %> '> asp : TextBox > EditItemTemplate > ItemTemplate > asp : Label ID =Label2 runat =server Text =' # Bind(education) %> '> asp : Label > ItemTemplate > asp : TemplateField > asp : TemplateField HeaderText =Email> EditItemTemplate > asp : TextBox ID =TextBox3 runat =server Text =' # Bind(email) %> '> asp : TextBox > EditItemTemplate > ItemTemplate > asp : Label ID =Label3 runat =server Text =' # Bind(email) %> '> asp : Label > ItemTemplate > asp : TemplateField > asp : TemplateField HeaderText =Location> EditItemTemplate > asp : TextBox ID =TextBox4 runat =server Text =' # Bind(location) %> '> asp : TextBox > EditItemTemplate > ItemTemplate > asp : Label ID =Label4 runat =server Text =' # Bind(location) %> '> asp : Label > ItemTemplate > asp : TemplateField > Columns > FooterStyle BackColor =#CCCCCC ForeColor =Black/> HeaderStyle BackColor =#000084 Font-Bold =True ForeColor =White/> PagerStyle BackColor =#999999 ForeColor =Black HorizontalAlign =Center/> RowStyle BackColor =#EEEEEE ForeColor =Black/> SelectedRowStyle BackColor =#008A8C Font-Bold =True ForeColor =White/> SortedAscendingCellStyle BackColor =#F1F1F1/> SortedAscendingHeaderStyle BackColor =#0000A9/> SortedDescendingCellStyle BackColor =#CAC9C9/> SortedDescendingHeaderStyle BackColor =#000065/> asp : GridView > br /> br /> div > asp : Button ID =Button1 runat =server onclick =Button1_Click1 Text =Export To PDF/> form > body > html > Your design.aspx.cs: using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; using System.Data; using System.Data.SqlClient; using System.IO; using System.Text; using iTextSharp.text; using iTextSharp.text.html; using iTextSharp.text.html.simpleparser; using iTextSharp.text.pdf; publicpartialclass _Default : System.Web.UI. Page { protectedvoid Page_Load( object sender EventArgs e) { if (!Page.IsPostBack) { refreshdata(); } } // Bind the gridview here publicvoid refreshdata() { SqlConnection con = new SqlConnection ( @Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True;User Instance=True ); SqlCommand cmd = new SqlCommand ( select * from tbl_data  con); SqlDataAdapter sda = new SqlDataAdapter (cmd); DataTable dt = new DataTable (); sda.Fill(dt); GridView1.DataSource = dt; GridView1.DataBind(); } // Gridview Paging code here protectedvoid GridView1_PageIndexChanging1( object sender GridViewPageEventArgs e) { GridView1.PageIndex = e.NewPageIndex; refreshdata(); } // Exporting Gridview to pdf code here protectedvoid Button1_Click1( object sender EventArgs e) { Response.Clear(); Response.Buffer = true ; Response.ContentType = application/vnd.ms-excel ; Response.AddHeader( content-disposition  attachment;filename=GridViewExport.xls ); Response.Cache.SetCacheability( HttpCacheability .NoCache); StringWriter swr = new StringWriter (); HtmlTextWriter htmlwr = new HtmlTextWriter (swr); GridView1.AllowPaging = false ; refreshdata(); GridView1.RenderControl(htmlwr); Response.Output.Write(swr.ToString()); Response.Flush(); Response.End(); } publicoverridevoid VerifyRenderingInServerForm( Control control) { } } here you have to download Itextsharp library from this source: http://sourceforge.net/projects/itextsharp/files/latest/download Here from this source your get zip folder import all dll file to your project - As - ADD Reference. Hope this works: Thank you