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

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 < dt.Columns.Count; i++)

{

wr.Write(dt.Columns[i].ToString().ToUpper() + "\t");

}

wr.WriteLine();

//write rows to excel file

for (int i = 0; i < (dt.Rows.Count); i++)

{

for (int j = 0; j < dt.Columns.Count; j++)

{

if (dt.Rows[i][j] != null)

{

wr.Write(Convert.ToString(dt.Rows[i][j]) + "\t");

}

else

{

wr.Write("\t");

}

//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

Your design.aspx page:

<%@PageLanguage="C#"AutoEventWireup="true"CodeFile="Default.aspx.cs"Inherits="_Default"%>

DOCTYPEhtmlPUBLIC"-//W3C//DTD XHTML 1.0 Transitional//EN""http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<htmlxmlns="http://www.w3.org/1999/xhtml">

<headrunat="server">

<title>title>

head>

<body>

<formid="form1"runat="server">

<div>

<asp:GridViewID="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">

<AlternatingRowStyleBackColor="#DCDCDC"/>

<asp:TemplateFieldHeaderText="Name">

<asp:TextBoxID="TextBox1"runat="server"Text='<%# Bind("name") %>'>asp:TextBox>

EditItemTemplate>

<asp:LabelID="Label1"runat="server"Text='<%# Bind("name") %>'>asp:Label>

ItemTemplate>

asp:TemplateField>

<asp:TemplateFieldHeaderText="Education">

<asp:TextBoxID="TextBox2"runat="server"Text='<%# Bind("education") %>'>asp:TextBox>

EditItemTemplate>

<asp:LabelID="Label2"runat="server"Text='<%# Bind("education") %>'>asp:Label>

ItemTemplate>

asp:TemplateField>

<asp:TemplateFieldHeaderText="Email">

<asp:TextBoxID="TextBox3"runat="server"Text='<%# Bind("email") %>'>asp:TextBox>

EditItemTemplate>

<asp:LabelID="Label3"runat="server"Text='<%# Bind("email") %>'>asp:Label>

ItemTemplate>

asp:TemplateField>

<asp:TemplateFieldHeaderText="Location">

<asp:TextBoxID="TextBox4"runat="server"Text='<%# Bind("location") %>'>asp:TextBox>

EditItemTemplate>

<asp:LabelID="Label4"runat="server"Text='<%# Bind("location") %>'>asp:Label>

ItemTemplate>

asp:TemplateField>

Columns>

<FooterStyleBackColor="#CCCCCC"ForeColor="Black"/>

<HeaderStyleBackColor="#000084"Font-Bold="True"ForeColor="White"/>

<PagerStyleBackColor="#999999"ForeColor="Black"HorizontalAlign="Center"/>

<RowStyleBackColor="#EEEEEE"ForeColor="Black"/>

<SelectedRowStyleBackColor="#008A8C"Font-Bold="True"ForeColor="White"/>

<SortedAscendingCellStyleBackColor="#F1F1F1"/>

<SortedAscendingHeaderStyleBackColor="#0000A9"/>

<SortedDescendingCellStyleBackColor="#CAC9C9"/>

<SortedDescendingHeaderStyleBackColor="#000065"/>

asp:GridView>

<br/>

div>

<asp:ButtonID="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 = newSqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True;User Instance=True");

SqlCommand cmd = newSqlCommand("select * from tbl_data", con);

SqlDataAdapter sda = newSqlDataAdapter(cmd);

DataTable dt = newDataTable();

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 = newStringWriter();

HtmlTextWriter htmlwr = newHtmlTextWriter(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

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Anshul Jain11y ago
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 < dt.Columns.Count; i++)
{
wr.Write(dt.Columns[i].ToString().ToUpper() + "\t");
}
wr.WriteLine();
//write rows to excel file
for (int i = 0; i < (dt.Rows.Count); i++)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
if (dt.Rows[i][j] != null)
{
wr.Write(Convert.ToString(dt.Rows[i][j]) + "\t");
}
else
{
wr.Write("\t");
}
}
//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
0
Upendra Pratap Shahi11y ago
yes,use SpreadSheetGear, this is a DLL and use this in your code and validate through c#.
This works with c#
0
Naeem Khan11y ago
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 ..
0
Upendra Pratap Shahi11y ago
Try to use spread sheet gear which is very easy and performing fast.
All the validation done easily through spreadsheet gear.
0
Nilesh Jadav11y ago
Your design.aspx page:

<%@PageLanguage="C#"AutoEventWireup="true"CodeFile="Default.aspx.cs"Inherits="_Default"%>

DOCTYPEhtmlPUBLIC"-//W3C//DTD XHTML 1.0 Transitional//EN""http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<htmlxmlns="http://www.w3.org/1999/xhtml">

<headrunat="server">

<title>title>

head>

<body>

<formid="form1"runat="server">

<div>

<asp:GridViewID="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">

<AlternatingRowStyleBackColor="#DCDCDC"/>

<Columns>

<asp:TemplateFieldHeaderText="Name">

<EditItemTemplate>

<asp:TextBoxID="TextBox1"runat="server"Text='<%# Bind("name") %>'>asp:TextBox>

EditItemTemplate>

<ItemTemplate>

<asp:LabelID="Label1"runat="server"Text='<%# Bind("name") %>'>asp:Label>

ItemTemplate>

asp:TemplateField>

<asp:TemplateFieldHeaderText="Education">

<EditItemTemplate>

<asp:TextBoxID="TextBox2"runat="server"Text='<%# Bind("education") %>'>asp:TextBox>

EditItemTemplate>

<ItemTemplate>

<asp:LabelID="Label2"runat="server"Text='<%# Bind("education") %>'>asp:Label>

ItemTemplate>

asp:TemplateField>

<asp:TemplateFieldHeaderText="Email">

<EditItemTemplate>

<asp:TextBoxID="TextBox3"runat="server"Text='<%# Bind("email") %>'>asp:TextBox>

EditItemTemplate>

<ItemTemplate>

<asp:LabelID="Label3"runat="server"Text='<%# Bind("email") %>'>asp:Label>

ItemTemplate>

asp:TemplateField>

<asp:TemplateFieldHeaderText="Location">

<EditItemTemplate>

<asp:TextBoxID="TextBox4"runat="server"Text='<%# Bind("location") %>'>asp:TextBox>

EditItemTemplate>

<ItemTemplate>

<asp:LabelID="Label4"runat="server"Text='<%# Bind("location") %>'>asp:Label>

ItemTemplate>

asp:TemplateField>

Columns>

<FooterStyleBackColor="#CCCCCC"ForeColor="Black"/>

<HeaderStyleBackColor="#000084"Font-Bold="True"ForeColor="White"/>

<PagerStyleBackColor="#999999"ForeColor="Black"HorizontalAlign="Center"/>

<RowStyleBackColor="#EEEEEE"ForeColor="Black"/>

<SelectedRowStyleBackColor="#008A8C"Font-Bold="True"ForeColor="White"/>

<SortedAscendingCellStyleBackColor="#F1F1F1"/>

<SortedAscendingHeaderStyleBackColor="#0000A9"/>

<SortedDescendingCellStyleBackColor="#CAC9C9"/>

<SortedDescendingHeaderStyleBackColor="#000065"/>

asp:GridView>

<br/>

<br/>

div>

<asp:ButtonID="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 = newSqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True;User Instance=True");

SqlCommand cmd = newSqlCommand("select * from tbl_data", con);

SqlDataAdapter sda = newSqlDataAdapter(cmd);

DataTable dt = newDataTable();

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 = newStringWriter();

HtmlTextWriter htmlwr = newHtmlTextWriter(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
0