public ActionResult ImportExcelFile(HttpPostedFileBase file, FormCollection collection)
{
CCIRepository repository = CCIRepository.CreateRepository();
if (Request.Files["FileUpload"].ContentLength > 0)
{
string fileExtension = System.IO.Path.GetExtension(Request.Files["FileUpload"].FileName);
if (fileExtension == ".xls" || fileExtension == ".xlsx")
{
string filename = Path.GetFileName(Request.Files["FileUpload"].FileName);
string fileLocation = string.Format("{0}{1}", Server.MapPath("/App_Data/ExcelFiles/"), filename);
if (System.IO.File.Exists(fileLocation))
{
System.IO.File.Delete(fileLocation);
}
Request.Files["FileUpload"].SaveAs(fileLocation);
string excelConnectionString = string.Empty;
if (fileExtension == ".xls")
{
excelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
}
else if (fileExtension == ".xlsx")
{
excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
}
OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
excelConnection.Open();
DataTable dt = new DataTable();
dt = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
if (dt == null)
{
return(null);
}
String[] excelsheets = new String[dt.Rows.Count];
int t = 0;
foreach (DataRow row in dt.Rows)
{
excelsheets[t] = row["TABLE_NAME"].ToString();
t++;
}
OleDbConnection excelConnection1 = new OleDbConnection(excelConnectionString);
DataSet ds = new DataSet();
string query = string.Format("Select * from [{0}]", excelsheets[0]);
using (OleDbDataAdapter dataAdapter = new OleDbDataAdapter(query, excelConnection1))
{
dataAdapter.Fill(ds);
}
Transport _model = new Transport();
_model.UploadedDate = DateTime.Now;
_model.DateOfCreation = Convert.ToDateTime(collection["DateOfCreation"].ToString());
var temp = repository.InsertTransImportDate(_model);
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
_model.PresentDate = Convert.ToDateTime(ds.Tables[0].Rows[i]["Date"]);
_model.Name = Convert.ToString(ds.Tables[0].Rows[i]["Name"]);
_model.Place = Convert.ToString(ds.Tables[0].Rows[i]["Place"]);
_model.Driver = Convert.ToString(ds.Tables[0].Rows[i]["Driver"]);
_model.VehicleNumber = Convert.ToInt32(ds.Tables[0].Rows[i]["Vehiclenumber"]);
_model.Amount = Convert.ToInt32(ds.Tables[0].Rows[i]["Amount"]);
_model.CostCentre = Convert.ToInt32(ds.Tables[0].Rows[i]["Cost centre"]);
_model.ImportDateId = temp;
repository.SaveTransData(_model);
}
}
else
{
ModelState.AddModelError("", "Please Select a excel file");
return(null);
}
}
return(RedirectToAction("Success", "Transport"));
}