Email: Password:       Forgot Password 
A Saarsha Group Online Community for dot net codes group like C#, Asp.NET, VB.NET, Sharepoint, JavaScript, JQuery, Ajax, SQL, WCF, WPF.

Export Text To Excel

Viewed:  1801 
Posted On:  27/08/2014 08:52:33 
How we can write our text line by line into an excel sheet? 

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" "">
<html xmlns="">
<head runat="server">
    <title>Write To Excel </title>
    <form id="form1" runat="server">
        <table width="50%" align="center" style="background-color: ActiveCaption;" cellspacing="20">
                <td align="center">
                    <asp:Button ID="btnGenerateExcel" runat="server" Text="Generate Excel" OnClick="btnGenerateExcel_Click" />

My aspx.cs code is:

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;
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);
        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.AddHeader("content-disposition", attachment);
        Response.ContentType = "application/";
        string currentSheet = "";
        foreach (DataColumn column in dt.Columns)
            Response.Write(currentSheet + column.ColumnName);
            currentSheet = "\t";
        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";

         HOME   |   Submit Article   |   Contact Us   |   About Us   |   Terms & Condition   |   Advertise With us