public ActionResult AllowanceImport(PayrollAllowanceDetailDTO Record, HttpPostedFileBase file)
{
Record.AllowanceMasterList = _PayrollAllowanceMasterService.GetAllowanceMasterList();
DropDownListViewmodelcs ddlvm = DropDownlist();
Record.OfficeList = ddlvm.OfficeList;
try
{
string result;
result = file.FileName;
DataSet ds = new DataSet();
DataTable dts = new DataTable();
string fileExtension =
System.IO.Path.GetExtension(result);
if (fileExtension == ".xls" || fileExtension == ".xlsx")
{
string fileLocation = Server.MapPath("~/Content/AllowanceImport/") + result;
if (!Directory.Exists(Server.MapPath("~/Content/AllowanceImport/")))
{
Directory.CreateDirectory(Server.MapPath("~/Content/AllowanceImport/"));
}
if (System.IO.File.Exists(fileLocation))
{
System.IO.File.Delete(fileLocation);
}
file.SaveAs(Path.Combine(Server.MapPath("~/Content/AllowanceImport/"), result));
string excelConnectionString = string.Empty;
excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation +
";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
//connection String for xls file format.
if (fileExtension == ".xls")
{
excelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileLocation +
";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
}
//connection String for xlsx file format.
else if (fileExtension == ".xlsx")
{
excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation +
";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
}
//Create Connection to Excel work book and add oledb namespace
OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
excelConnection.Open();
DataTable dt = new DataTable();
dt = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
if (dt == null)
{
TempData["Danger"] = "Corrupt file. Please check the file and try again";
return(RedirectToAction("Index"));
}
String[] excelSheets = new String[dt.Rows.Count];
int t = 0;
//excel data saves in temp file here.
foreach (DataRow row in dt.Rows)
{
excelSheets[t] = row["TABLE_NAME"].ToString();
t++;
}
OleDbConnection excelConnection1 = new OleDbConnection(excelConnectionString);
string query = string.Format("Select * from [{0}]", excelSheets[0]);
using (OleDbDataAdapter dataAdapter = new OleDbDataAdapter(query, excelConnection1))
{
dataAdapter.Fill(ds);
dataAdapter.Fill(dts);
}
excelConnection.Close();
}
List <PayrollAllowanceDetailDTO> modelrecord = new List <PayrollAllowanceDetailDTO>();
foreach (DataRow d in dts.Rows)
{
PayrollAllowanceDetailDTO single = new PayrollAllowanceDetailDTO()
{
AllowanceMasterId = Record.AllowanceMasterId,
EmployeeCode = Convert.ToInt32(d["EmployeeCode"].ToString()),
PercentageAmount = d["PercentageAmount"].ToString(),
Value = Convert.ToDecimal(d["Value"].ToString()),
};
single.EmployeeName = _EmployeeService.GetEmployeeByID(single.EmployeeCode).Name;
modelrecord.Add(single);
PayrollAllowanceDetail domainrecord = PayrollAllowanceDetailRequestFormatter.ConvertRespondentInfoFromDTO(single);
PayrollAllowanceDetail pastrecord = _unitOfWork.PayrollAllowanceDetailRepository.Get(x => x.AllowanceMasterId == single.AllowanceMasterId && x.EmployeeCode == single.EmployeeCode).FirstOrDefault();
if (pastrecord != null)
{
_unitOfWork.PayrollAllowanceDetailRepository.Delete(pastrecord);
}
_unitOfWork.PayrollAllowanceDetailRepository.Create(domainrecord);
}
ViewBag.AllowanceImportedList = modelrecord;
TempData["Success"] = "Allowance Data imported successfully";
}
catch (Exception ex)
{
TempData["Danger"] = ex.Message;
}
return(View(Record));
}
public void InsertIntoPayrollAllowanceDetail(PayrollAllowanceDetailDTO Record)
{
PayrollAllowanceDetail Data = PayrollAllowanceDetailRequestFormatter.ConvertRespondentInfoFromDTO(Record);
_unitOfWork.PayrollAllowanceDetailRepository.Create(Data);
}