public ActionResult ImportExcel(ImportExcelViewModel model)
{
Logger.Info("CheckingController ImportExcel(model)");
try
{
if (ModelState.IsValid)
{
//Get UserName
//string userName = User.Identity.Name;
string userName = User.Identity.Name;
List <VOYAGE> lstVoyage = _oraVoyageService.GetVoyATD();
if (lstVoyage.Count > 0)
{
//Get data for dropdownlist of voyages
ViewBag.VesselId = new SelectList(lstVoyage, "Id", "Code");
ViewBag.VesselName = lstVoyage.Select(x => new List <object>()
{
x.ID, x.NAME
});
}
var voyage = _oraVoyageRepository.GetById(model.VesselId);
//Import Function of FileExcelService
//TestUserId will be re-code after Login function
int[] state = _fileExcelService.Import(voyage.CODE, userName, model.CustomerId, model.FileImport);
//result of import
switch (state[0])
{
case (int)ImportedFileState.ContentBlank:
ModelState.AddModelError("", Resource_FileExcelUpload.ErrorContentBlank);
break;
case (int)ImportedFileState.Done:
return(RedirectToAction("GetInfoRegis", new { excelId = state[1] }));
case (int)ImportedFileState.Existed:
ModelState.AddModelError("", Resource_FileExcelUpload.ErrorExisted);
break;
case (int)ImportedFileState.NotEnoughParams:
ModelState.AddModelError("", Resource_FileExcelUpload.ErrorNotEnoughParams);
break;
case (int)ImportedFileState.NotFinished:
ModelState.AddModelError("", Resource_FileExcelUpload.ErrorNotFinished);
break;
case (int)ImportedFileState.WrongExtension:
ModelState.AddModelError("", Resource_FileExcelUpload.ErrorWrongExtension);
break;
case (int)ImportedFileState.ContentSizeWrong:
ModelState.AddModelError("", Resource_FileExcelUpload.ErrorContentSizeWrong);
break;
}
}
else
{
ModelState.AddModelError("", Resource_FileExcelUpload.ErrorNotEnoughParams);
}
}
catch (Exception e)
{
ModelState.AddModelError("", Resource_FileExcelUpload.ErrorServer);
Logger.Error("CheckingController ImportExcel(model) Error: ", e);
}
return(View(model));
}
public JsonResult ImportContacts()
{
try
{
if (Request.Files.Count > 0)
{
try
{
HttpFileCollectionBase files = Request.Files;
for (int i = 0; i < files.Count; i++)
{
HttpPostedFileBase file = files[i];
string fileName = file.FileName;
string fileContentType = file.ContentType;
byte[] fileBytes = new byte[file.ContentLength];
var data = file.InputStream.Read(fileBytes, 0, Convert.ToInt32(file.ContentLength));
//List<lead> leadsList = new List<lead>();
List <ImportExcelViewModel> lstImportExcel = new List <ImportExcelViewModel>();
List <ImportExcelViewModel> errorLstImportExcel = new List <ImportExcelViewModel>();
using (var package = new ExcelPackage(file.InputStream))
{
var currentSheet = package.Workbook.Worksheets;
var workSheet = currentSheet.First();
var noOfCol = workSheet.Dimension.End.Column;
var noOfRow = workSheet.Dimension.End.Row;
//first row is titles
for (int rowIterator = 2; rowIterator <= noOfRow; rowIterator++)
{
/********************************Validtae*************************/
var importExcelRow = new ImportExcelViewModel
{
email = workSheet.Cells[rowIterator, 3].Value.ToString()
};
lstImportExcel.Add(importExcelRow);
/*****************************validate*****************************************/
}
lstImportExcel = ValidateImport(lstImportExcel); //validate the rows with the system values
if (lstImportExcel != null)
{
foreach (var importExcelRow in lstImportExcel)
{
if (importExcelRow.IsVallid == true)
{
//save in database
}
else
{
//save records of error that are not saved
var errorImportExcelRow = new ImportExcelViewModel
{
email = importExcelRow.email,
RowNb = importExcelRow.RowNb
};
errorLstImportExcel.Add(errorImportExcelRow);
}
}
}
else
{
//something wrong in validation
return(Json(false));
}
}
//export to excel the error emails
ExcelPackage excel = new ExcelPackage();
var myworkSheet = excel.Workbook.Worksheets.Add("Sheet1");
//Header of table
//
myworkSheet.Row(1).Height = 20;
myworkSheet.Row(1).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
myworkSheet.Row(1).Style.Font.Bold = true;
myworkSheet.Cells[1, 1].Value = "Row Number";
myworkSheet.Cells[1, 2].Value = "Email";
//Body of table
//
int recordIndex2 = 2;
foreach (var student in errorLstImportExcel)
{
myworkSheet.Cells[recordIndex2, 1].Value = student.RowNb.ToString();
myworkSheet.Cells[recordIndex2, 2].Value = student.email.ToString();
recordIndex2++;
}
myworkSheet.Column(1).AutoFit();
myworkSheet.Column(2).AutoFit();
var fileBytes2 = excel.GetAsByteArray();
Response.Clear();
Response.AppendHeader("Content-Length", fileBytes2.Length.ToString());
Response.AppendHeader("Content-Disposition",
String.Format("attachment; filename=\"{0}\"; size={1}; creation-date={2}; modification-date={2}; read-date={2}"
, "temp.xlsx"
, fileBytes2.Length
, DateTime.Now.ToString("R"))
);
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.BinaryWrite(fileBytes2);
Response.End();
return(Json(false));
}
return(Json(false));
}
catch (Exception e)
{
return(Json(false));
}
}
return(Json(false));
}
catch (Exception e)
{
return(Json(false));
}
}
