if (Path.GetExtension(FileUpload1.FileName) == ".xls" || Path.GetExtension(FileUpload1.FileName) == ".xlsx" && (FileUpload1.PostedFile.ContentType == "application/vnd.ms-excel" || FileUpload1.PostedFile.ContentType == "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"))
{
string uploadedFilePath = Server.MapPath("~/docs/" + System.IO.Path.GetFileName(FileUpload1.FileName));
FileUpload1.SaveAs(uploadedFilePath);
postedfile = uploadedFilePath;
switch (postedfile.Substring(postedfile.LastIndexOf('.') + 1))
{
case "xls":
stringConExcelMat = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + uploadedFilePath + ";Extended Properties=\"Excel 8.0;HDR=NO;IMEX=1\"";
break;
case "xlsx":
stringConExcelMat = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + uploadedFilePath + ";Extended Properties=\"Excel 12.0;HDR=No;IMEX=2\"";
break;
}
}
else
{
ScriptManager.RegisterStartupScript(this, this.GetType(), "fdfd", "alert('Only (XLS,XLSX) Extension Files are allowed.Or Content-Type Mismatched');", true);
return;
}
DataTable TTable = new DataTable(postedfile);
using(OleDbConnection conn = new OleDbConnection(stringConExcelMat))
{
conn.Open();
using(OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM [Sheet1$A:T]", conn))
{
da.Fill(TTable);
if (TTable.Rows.Count > 0)
{
string sqlConnectionString = System.Configuration.ConfigurationManager.AppSettings["strConn"].ToString();
using(SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString))
{
bulkCopy.DestinationTableName = "Upcoming_SaleList";
bulkCopy.WriteToServer(TTable);
}
}
}
}This code snippet for Import Excel Sheet to Database using C#.