1.Get data from from Excel file in to data table by using below function..
public static DataTable exceldata(string filePath)
{
DataTable dtexcel = new DataTable();
bool hasHeaders = false;
string HDR = hasHeaders ? "Yes" : "No";
string strConn;
if (filePath.Substring(filePath.LastIndexOf('.')).ToLower() == ".xlsx")
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=\"Excel 12.0;HDR=" + HDR + ";IMEX=0\"";
else
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=\"Excel 8.0;HDR=" + HDR + ";IMEX=0\"";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
DataRow schemaRow = schemaTable.Rows[0];
string sheet = schemaRow["TABLE_NAME"].ToString();
if (!sheet.EndsWith("_"))
{
string query = "SELECT * FROM [" + sheet + "]";
OleDbDataAdapter daexcel = new OleDbDataAdapter(query, conn);
dtexcel.Locale = CultureInfo.CurrentCulture;
daexcel.Fill(dtexcel);
}
conn.Close();
return dtexcel;
}
2.In second step insert the data table value into data base by using Bulk copy process...
public void BulkImport(DataTable ExcelDatatable, string TABLENAME)
{
if (ExcelDatatable.Rows.Count > 1)
{
try
{
if (con.State == ConnectionState.Closed)
con.Open();
using (SqlBulkCopy bulkCopy = new SqlBulkCopy
(
con,
SqlBulkCopyOptions.TableLock |
SqlBulkCopyOptions.FireTriggers |
SqlBulkCopyOptions.UseInternalTransaction,
null
))
{
bulkCopy.DestinationTableName = TABLENAME;
for (int i = 0; i <= ExcelDatatable.Columns.Count - 1; i++)
{
string colname = ExcelDatatable.Columns[i].ColumnName.ToString();
bulkCopy.ColumnMappings.Add(ExcelDatatable.Columns[i].ColumnName.ToString(), colname);
}
bulkCopy.WriteToServer(ExcelDatatable);
}
con.Close();
}
catch (Exception ee)
{
}
}
}
}