Hi,

I want export the records to excel file from table. Can any one give me an idea?

__________________
The OXYGEN
Delivers edgy, intelligent Technology to all...

Hi,

you can do this using the following code.

string fileExcel, filePath, fileName, strLine, sql;
FileStream objFileStream;
StreamWriter objStreamWriter;
Random nRandom = new Random(DateTime.Now.Millisecond);
SqlConnection cnn = new SqlConnection("Data Source=.; Initial Catalog=Test; User ID=test1; Password=123456");

//Create a random file name.
fileExcel = "t" + nRandom.Next().ToString() + ".xls";

//Set a virtual folder to save the file.
//Make sure to change the application name to match your folder.
filePath = Server.MapPath("\\ExcelCSTest");
fileName = filePath + "\\" + fileExcel;

//Use FileSystem objects to create the .xls file.
objFileStream = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.Write);
objStreamWriter = new StreamWriter(objFileStream);

//Use a DataReader object to connect to the database.
cnn.Open();
sql = "select * from users";
SqlCommand cmd = new SqlCommand(sql, cnn);
SqlDataReader dr;
dr = cmd.ExecuteReader();

//Initialize the string that is used to build the file.
strLine = "";

//Enumerate the field names and the records that are used to build
//the file.
for (int i = 0; i <= dr.FieldCount - 1; i++)
{
strLine = strLine + dr.GetName(i).ToString() + Convert.ToChar(9);
}

//Write the field name information to the file.
objStreamWriter.WriteLine(strLine);

//Reinitialize the string for data.
strLine = "";

//Enumerate the database that is used to populate the file.
while (dr.Read())
{
for (int i = 0; i <= dr.FieldCount - 1; i++)
{
strLine = strLine + dr.GetValue(i).ToString()+ Convert.ToChar(9);
}
objStreamWriter.WriteLine(strLine);
strLine = "";
}

//Clean up.
dr.Close();
cnn.Close();
objStreamWriter.Close();
objFileStream.Close();

__________________
S.Balasubramanian
Nothing is impossible

string excelConnectionString = @"Provider=Microsoft
.Jet.OLEDB.4.0;Data Source=Book1.xls;Extended
Properties=""Excel 8.0;HDR=YES;""";

using (OleDbConnection connection =
new OleDbConnection(excelConnectionString))
{
OleDbCommand command = new OleDbCommand
("Select ID,Data FROM [Data$]", connection);

connection.Open();


using (DbDataReader dr = command.ExecuteReader())
{
// SQL Server Connection String
string sqlConnectionString = "Data Source=.;
Initial Catalog=Test;Integrated Security=True";

// Bulk Copy to SQL Server
using (SqlBulkCopy bulkCopy =
new SqlBulkCopy(sqlConnectionString))
{
bulkCopy.DestinationTableName = "ExcelData";
bulkCopy.WriteToServer(dr);
}
}
}