In this article I am going to explain how we can export
our data to excel or how we can write our data by applying iteration ie: loop
into excel sheet line by line.
For example: first I will read data from SQLServer
table:
Image 1.
Output will be like below:
Image 2.
Image 3.
Now to achieve this create a new solution and add
reference of Microsoft.Office.Interop.Excel like
below:
Image 4.
Now My aspx code is:
<%@ Page
Language="C#"
AutoEventWireup="true"
CodeFile="Default.aspx.cs"
Inherits="_Default"
%>
<!DOCTYPE html
PUBLIC "-//W3C//DTD
XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Write To Excel </title>
</head>
<body>
<form
id="form1"
runat="server">
<div>
<table
width="50%"
align="center"
style="background-color: ActiveCaption;" cellspacing="20">
<tr>
<td align="center">
<asp:Button ID="btnGenerateExcel" runat="server" Text="Generate
Excel" OnClick="btnGenerateExcel_Click" />
</td>
</tr>
</table>
</div>
</form>
</body>
</html>
My aspx.cs code is:
using System;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using
System.Web.UI.HtmlControls;
using
System.Web.UI.WebControls;
using
System.Web.UI.WebControls.WebParts;
using System.IO;
using
System.Runtime.InteropServices;
sing System.Text;
using
Microsoft.Office.Interop.Excel;
using System.Data.SqlClient;
public partial
class _Default
: System.Web.UI.Page
{
DataSet ds = new
DataSet();
protected void
Page_Load(object sender, EventArgs e)
{
}
protected void
btnGenerateExcel_Click(object sender, EventArgs e)
{
ds = GetData();
Write2Excel(ds.Tables[0], "Employee.xls");
}
private DataSet
GetData()
{
SqlDataAdapter da;
DataSet ds = new
DataSet();
SqlConnection con;
SqlCommand cmd = new SqlCommand();
con = new SqlConnection(@"server=localhost\SqlServer2k8;Integrated
Security=True;;database=Test;");
cmd.CommandText = "select * from
Employee";
cmd.Connection = con;
da = new
SqlDataAdapter(cmd);
da.Fill(ds);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
return ds;
}
void Write2Excel(System.Data.DataTable dataToExcel, string
exportExcelSheet)
{
System.Data.DataTable dt = new System.Data.DataTable();
dt = dataToExcel;
string attachment = "attachment; filename=" +
exportExcelSheet;
Response.ClearContent();
Response.AddHeader("content-disposition",
attachment);
Response.ContentType = "application/vnd.ms-excel";
string currentSheet = "";
foreach (DataColumn
column in dt.Columns)
{
Response.Write(currentSheet +
column.ColumnName);
currentSheet = "\t";
}
Response.Write("\n");
int i;
foreach (DataRow
dr in dt.Rows)
{
currentSheet = "";
for
(i = 0; i < dt.Columns.Count; i++)
{
Response.Write(currentSheet +
dr[i].ToString());
currentSheet = "\t";
}
Response.Write("\n");
}
Response.End();
}
}