Hi NerakSeven,
Check this example. Now please take its reference and correct your code.
After getting all the record from all the sheets you can pass the DataTable to WriteToServer method for writing the data in Database.
Controller
public class HomeController : Controller
{
// GET: /Home/
public ActionResult Index()
{
return View();
}
[HttpPost]
public ActionResult Index(HttpPostedFileBase postedFile)
{
string filePath = string.Empty;
if (postedFile != null)
{
string path = Server.MapPath("~/Uploads/");
if (!Directory.Exists(path))
{
Directory.CreateDirectory(path);
}
filePath = path + Path.GetFileName(postedFile.FileName);
string extension = Path.GetExtension(postedFile.FileName);
postedFile.SaveAs(filePath);
string conString = string.Empty;
switch (extension)
{
case ".xls": //Excel 97-03.
conString = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
break;
case ".xlsx": //Excel 07 and above.
conString = ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString;
break;
}
DataTable dtMerge = new DataTable();
conString = string.Format(conString, filePath);
using (OleDbConnection connExcel = new OleDbConnection(conString))
{
using (OleDbCommand cmdExcel = new OleDbCommand())
{
using (OleDbDataAdapter odaExcel = new OleDbDataAdapter())
{
cmdExcel.Connection = connExcel;
connExcel.Open();
DataTable dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
connExcel.Close();
for (int i = 0; i < dtExcelSchema.Rows.Count; i++)
{
string sheetName = dtExcelSchema.Rows[i]["TABLE_NAME"].ToString();
//Read Data from current Sheet.
connExcel.Open();
cmdExcel.CommandText = "SELECT * From [" + sheetName + "]";
odaExcel.SelectCommand = cmdExcel;
DataTable dt = new DataTable();
odaExcel.Fill(dt);
if (dt.Rows.Count > 0)
{
dtMerge.Merge(dt);
}
connExcel.Close();
}
}
}
}
conString = ConfigurationManager.ConnectionStrings["Constring"].ConnectionString;
using (SqlConnection con = new SqlConnection(conString))
{
using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
{
//Set the database table name.
sqlBulkCopy.DestinationTableName = "dbo.Table_1";
//[OPTIONAL]: Map the Excel columns with that of the database table
sqlBulkCopy.ColumnMappings.Add("Id", "Id");
sqlBulkCopy.ColumnMappings.Add("Name", "Name");
sqlBulkCopy.ColumnMappings.Add("Country", "Country");
con.Open();
sqlBulkCopy.WriteToServer(dtMerge);
con.Close();
}
}
}
return View();
}
}
View
<%@ Page Language="C#" Inherits="System.Web.Mvc.ViewPage<dynamic>" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Index</title>
</head>
<body>
<div>
<% using (Html.BeginForm("Index", "Home", FormMethod.Post, new { enctype = "multipart/form-data" }))
{%>
<input type="file" name="postedFile" />
<input type="submit" value="Import" />
<% } %>
</div>
</body>
</html>
Screenshot
Scheet1
Sheet2
DataTable records having all the records from all the sheets
