Import Excel Data to SQL Server using ASP.NET

If you want to import data from excel file to SQL Server in asp.net, then first you have to upload the excel file to server and call the following function with correct arguments.

filePath -- full server path of excel file
tableName -- name of table in which you want to import
sheetName -- specify the sheet name (e.g. Sheet1, Sheet2)
mode -- if you want to replace the data, set mode to "replace", otherwise set ""

private void ImportToSqlServer(string filePath,string tableName,string sheetName, string mode)
{

StringBuilder query = new StringBuilder();
string conStr = "Data Source=(local);Initial Catalog=DatabaseName;User Id=myUsername;Password=myPassword";
SqlConnection cn = new SqlConnection(conStr);
if (mode == "replace")
{
query.Append(string.Format("delete from {0};",tableName));
}
query.Append(string.Format("Insert into {0} Select * FROM OPENROWSET",tableName));
query.Append(string.Format("('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database={0};",filePath));
query.Append(string.Format("HDR = No','SELECT * FROM [{0}$]')",sheetName));
SqlCommand cmd = new SqlCommand(query.ToString(),cn);
cn.Open();
cmd.ExecuteNonQuery();
cn.Close();
}