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

Export Text To Excel

Article:
Viewed:  1062 
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:

1.png

Image 1.

Output will be like below:

2.png

Image 2.

3.png

Image 3.

Now to achieve this create a new solution and add reference of Microsoft.Office.Interop.Excel like below:

4.png

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();
    }
}

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