I have about 20 files , all with 20 or more sheets. I need to import specific data from all sheets in tables (as the same columns as the table). My code only import one sheet. How to import multiple sheets? and How Import specific cells from Excel?.I have read many questions but none do what I need and the most are old Database SQL SERver I hope ur help me. Im newbie.
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 dt = 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;
//Get the name of First Sheet.
connExcel.Open();
DataTable dtExcelSchema;
dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string sheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
connExcel.Close();
//Read Data from First Sheet.
connExcel.Open();
cmdExcel.CommandText = "SELECT * From [" + sheetName + "]";
odaExcel.SelectCommand = cmdExcel;
odaExcel.Fill(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("Rut", "Rut");
sqlBulkCopy.ColumnMappings.Add("Nombres", "Nombres");
sqlBulkCopy.ColumnMappings.Add("Malla", "Malla");
con.Open();
sqlBulkCopy.WriteToServer(dt);
con.Close();
}
}
}
return View();
}
}