[HttpPost]
public ActionResult ExcelView(HttpPostedFileBase postedFile)
{
try
{
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, new object[] { null, null, null, "dbo.tblStores" });
string ALLRISStores = dtExcelSchema.Columns[0].ColumnName.ToString();
//String[] excelSheets = new String[dt.Rows.Count];
connExcel.Close();
//Read Data from First Sheet.
connExcel.Open();
//string query = string.Format("Select * from [{0}]", excelSheets[0]);
cmdExcel.CommandText = "SELECT * From [ALL RIS Stores$]";
odaExcel.SelectCommand = cmdExcel;
odaExcel.Fill(dt);
connExcel.Close();
}
}
}
// Insert records into database table.
NECCOI_DBEntities1 entities = new NECCOI_DBEntities1();
if (entities.tblStores.Count() == 0)
{
//ViewBag.Message("Data Does not Exist,So Uploading Data");
Log.Error("Data Does not Exist, So Uploading Data");
foreach (DataRow row in dt.Rows)
{
//Div Mkt Store IP TID Corp Fran flag Address City County State Zip Hours tmz MO? Gas? Gas integrated
// Gas Vendor Reg Citgo TID Voice Store Closed Date RIS Installation
entities.tblStores.Add(new tblStore
{
Div = Convert.ToString(row["Div"]),
Mkt = Convert.ToString(row["Mkt"]),
Store = Convert.ToString(row["Store"]),
IP = Convert.ToString(row["IP"]),
TID = Convert.ToString(row["TID"]),
Corp_Fran_flag = Convert.ToString(row["Corp Fran flag"]),
Address = Convert.ToString(row["Address"]),
City = Convert.ToString(row["City"]),
County = Convert.ToString(row["County"]),
State = Convert.ToString(row["State"]),
Zip = Convert.ToString(row["Zip"]),
Hours = Convert.ToString(row["Hours"]),
tmz = Convert.ToString(row["tmz"]),
MO_ = Convert.ToString(row["MO?"]),
Gas_ = Convert.ToString(row["Gas?"]),
Gas_intergrated = Convert.ToString(row["Gas intergrated"]),
Gas_Vendor = Convert.ToString(row["Gas Vendor"]),
Reg = Convert.ToInt32(row["Reg"].ToString()),
Citgo_TID = Convert.ToString(row["Citgo TID"]),
Voice = Convert.ToString(row["Voice"]),
Store_Closed_Date = row["Store Closed Date"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(row["Store Closed Date"]),
RIS_Installation = row["RIS Installation"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(row["RIS Installation"]),
Planned_Open_date = row["Planned Open date"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(row["Planned Open date"])
//Store_Closed_Date = (DateTime?)(!string.IsNullOrEmpty("Store Closed Date") ? row["Store Closed Date"] : (object)DBNull.Value),
//RIS_Installation = (DateTime?)(!string.IsNullOrEmpty("RIS Installation") ? row["RIS Installation"] : (object)DBNull.Value),
//Planned_Open_date = (DateTime?)(!string.IsNullOrEmpty("Planned_Open_date") ? row["Planned_Open_date"] : (object)DBNull.Value)
// Store_Closed_Date= String.IsNullOrWhiteSpace("Store Closed Date") ? (object)DBNull.Value: (object)Store_Closed_Date,
//Store_Closed_Date = row["Store Closed Date"] = null ? null : Convert.ToDateTime(row["Store Closed Date"])
//RIS_Installation = row["RIS Installation"] = null ? null : Convert.ToDateTime(row["RIS Installation"]),
//Planned_Open_date = row["Planned_Open_date"] =null ? null : Convert.ToDateTime(row["Planned_Open_date"])
//Store_Closed_Date = null,
//RIS_Installation = null,
//Planned_Open_date = null
});
ViewBag.Result = "File Imported Successfully";
Log.Error(ViewBag.Result);
}
entities.SaveChanges();
}
else
{
//ViewBag.Message("Data Already Exist,So Deleting the Existing data");
Log.Error("Data Already Exists,So Deleting the Existing data and Uploading New Data");
entities.tblStores.RemoveRange(entities.tblStores);
entities.SaveChanges();
//NECCOI_DBEntities1 entities = new NECCOI_DBEntities1();
//Log.Error(entities);
//Log.Error(entities.tblStores);
foreach (DataRow row in dt.Rows)
{
//Div Mkt Store IP TID Corp Fran flag Address City County State Zip Hours tmz MO? Gas? Gas integrated
// Gas Vendor Reg Citgo TID Voice Store Closed Date RIS Installation
entities.tblStores.Add(new tblStore
{
Div = Convert.ToString(row["Div"]),
Mkt = Convert.ToString(row["Mkt"]),
Store = Convert.ToString(row["Store"]),
IP = Convert.ToString(row["IP"]),
TID = Convert.ToString(row["TID"]),
Corp_Fran_flag = Convert.ToString(row["Corp Fran flag"]),
Address = Convert.ToString(row["Address"]),
City = Convert.ToString(row["City"]),
County = Convert.ToString(row["County"]),
State = Convert.ToString(row["State"]),
Zip = Convert.ToString(row["Zip"]),
Hours = Convert.ToString(row["Hours"]),
tmz = Convert.ToString(row["tmz"]),
MO_ = Convert.ToString(row["MO?"]),
Gas_ = Convert.ToString(row["Gas?"]),
Gas_intergrated = Convert.ToString(row["Gas intergrated"]),
Gas_Vendor = Convert.ToString(row["Gas Vendor"]),
Reg = Convert.ToInt32(row["Reg"].ToString()),
Citgo_TID = Convert.ToString(row["Citgo TID"]),
Voice = Convert.ToString(row["Voice"]),
Store_Closed_Date = row["Store Closed Date"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(row["Store Closed Date"]),
RIS_Installation = row["RIS Installation"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(row["RIS Installation"]),
Planned_Open_date = row["Planned Open date"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(row["Planned Open date"])
//Store_Closed_Date = (DateTime?)(!string.IsNullOrEmpty("Store Closed Date") ? row["Store Closed Date"] : (object)DBNull.Value),
//RIS_Installation = (DateTime?)(!string.IsNullOrEmpty("RIS Installation") ? row["RIS Installation"] : (object)DBNull.Value),
//Planned_Open_date = (DateTime?)(!string.IsNullOrEmpty("Planned_Open_date") ? row["Planned_Open_date"] : (object)DBNull.Value)
// Store_Closed_Date= String.IsNullOrWhiteSpace("Store Closed Date") ? (object)DBNull.Value: (object)Store_Closed_Date,
//Store_Closed_Date = row["Store Closed Date"] = null ? null : Convert.ToDateTime(row["Store Closed Date"])
//RIS_Installation = row["RIS Installation"] = null ? null : Convert.ToDateTime(row["RIS Installation"]),
//Planned_Open_date = row["Planned_Open_date"] =null ? null : Convert.ToDateTime(row["Planned_Open_date"])
//Store_Closed_Date = null,
//RIS_Installation = null,
//Planned_Open_date = null
});
ViewBag.Result = "File Imported Successfully";
Log.Error(ViewBag.Result);
}
entities.SaveChanges();
}
}
return View();
}
catch (Exception ex)
{
Log.Error(ex);
throw;
}
}
}
Is it possible to import Excel file with dynamic sheet name into SQL Server table using EF in ASP.NET MVC?
Viewed 33
0 Answers
Related