string ExcelContentType = "application/vnd.ms-excel";
string Excel2010ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
if (fileuploadExcel.HasFile)
{
//Check the Content Type of the file
if (fileuploadExcel.PostedFile.ContentType == ExcelContentType || fileuploadExcel.PostedFile.ContentType == Excel2010ContentType)
{
try
{
//Save file path
string path = string.Concat(Server.MapPath("~/TempFiles/"), fileuploadExcel.FileName);
//Save File as Temp then you can delete it if you want
fileuploadExcel.SaveAs(path);
string excelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+path+";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1;\"";
// string excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="+path+";Extended Properties=\"Excel 12.0 Xml;HDR=YES;\"";
// Create Connection to Excel Workbook
using (OleDbConnection connection = new OleDbConnection(excelConnectionString))
{
OleDbCommand command = new OleDbCommand("Select * FROM [Sheet1$]", connection);
connection.Open();
// Create DbDataReader to Data Worksheet
using (DbDataReader dr = command.ExecuteReader())
{
// SQL Server Connection String
string sqlConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString.ToString();
// Bulk Copy to SQL Server
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString))
{
bulkCopy.DestinationTableName = "Excel_table";
bulkCopy.WriteToServer(dr);
lblMessage.Text = "The data has been exported succefuly from Excel to SQL";
}
}
}
}
catch (Exception ex)
{
lblMessage.Text = ex.Message;
}
}
}