Excel Export

Hi,

This is the method for exporting the data from excel to sql server the only condition for that, excel file columns and sql server tables columns should be same and datatype also should be same.

public void Exel2Sql()
{
OdbcConnection connection;
SqlBulkCopy bulkCopy;
string ConnectionString = @”server=sujitkumar\sqlexpress;database=pubs;uid=sa;pwd=1234;”;
string connstr = @”Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=c:\contact.xls”;
using (connection = new OdbcConnection(connstr))
{
OdbcCommand command = new OdbcCommand(”Select * FROM [Sheet1$]“, connection);

//you can change [Sheet1$] with your sheet name

connection.Open();

// Create DbDataReader to Data Worksheet

using (OdbcDataReader dr = command.ExecuteReader())
{
// Bulk Copy to SQL Server

using (bulkCopy = new SqlBulkCopy(ConnectionString))
{
bulkCopy.DestinationTableName = “Names”;
//”Names” is the sql table where you want to copy all data.

bulkCopy.WriteToServer(dr);
}
dr.Close();
}

}

bulkCopy.Close();
connection.Close();
}

If u want to import data from sql server to excel then u have to made little bit changes in this function.

hi friend,

u can do one thing, retrieve all data into a grid view, and there u can export to a excel sheet.