First we can use OleDBConnection to connect to Excel file as a data source. After that, we use DbDataReader to access Excel data we want. Then, we can use the SqlBulkCopy
to copy the data from the DataReader to the target SQL Server table. All we need are just the following codes,
// Connection String to Excel Workbook
string excelConnectionString = @"Provider=Microsoft
.Jet.OLEDB.4.0;Data Source=Book1.xls;Extended
Properties=""Excel 8.0;HDR=YES;""";
// Create Connection to Excel Workbook
using (OleDbConnection connection =
new OleDbConnection(excelConnectionString))
{
OleDbCommand command = new OleDbCommand
("Select ID,Data FROM [Data$]", connection);
connection.Open();
// Create DbDataReader to Data Worksheet
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);
}
}
}
The SqlBulk will make the importing process at highest performance. The idea and codes are from David Hayden’s blog article,
http://davidhayden.com/blog/dave/archive/2006/05/31/2976.aspx
Related Threads:
http://social.msdn.microsoft.com/Forums/en-US/vbide/thread/15e9e292-247c-45dd-8510-6f1325fbc404/
http://social.msdn.microsoft.com/Forums/en-US/vbinterop/thread/3e9e8441-36be-4f70-826b-51badb9a9bb3/
