public void writeErrorToFile(ExcelWorksheet eSheet, int rownumber, Deca_Vietin_Error_Code item, string error)
{
using (var dbConn = Helpers.OrmliteConnection.openConn())
{
int i = 1;
eSheet.Cells[rownumber, i++].Value = item.Code;
eSheet.Cells[rownumber, i++].Value = item.Description;
eSheet.Cells[rownumber, i++].Value = item.CreatedAt;
eSheet.Cells[rownumber, i++].Value = item.CreatedBy;
eSheet.Cells[rownumber, i++].Value = item.UpdatedAt;
eSheet.Cells[rownumber, i++].Value = item.UpdatedBy;
eSheet.Cells[rownumber, i++].Value = error;
}
}
public ActionResult ImportFromExcel()
{
try
{
if (Request.Files["FileUpload"] != null && Request.Files["fileUpload"].ContentLength > 0)
{
string fileExtension =
System.IO.Path.GetExtension(Request.Files["fileUpload"].FileName);
if (fileExtension == ".xls" || fileExtension == ".xlsx")
{
string fileLocation = string.Format("{0}/{1}", Server.MapPath("~/Excel"), "[" + currentUser.UserName + "-" + DateTime.Now.ToString("yyyyMMddHHmmss") + "]" + Request.Files["fileUpload"].FileName);
string errorFileLocation = string.Format("{0}/{1}", Server.MapPath("~/Excel"), "[" + currentUser.UserName + "-" + DateTime.Now.ToString("yyyyMMddHHmmss") + "-Error]" + Request.Files["fileUpload"].FileName);
if (System.IO.File.Exists(fileLocation))
{
System.IO.File.Delete(fileLocation);
}
Request.Files["fileUpload"].SaveAs(fileLocation);
//Request.Files["fileUpload"].SaveAs(errorFileLocation);
var rownumber = 2;
var total = 0;
int n = 1;
FileInfo fileInfo = new FileInfo(fileLocation);
var excelPkg = new ExcelPackage(fileInfo);
FileInfo template = new FileInfo(Server.MapPath(@"~\ExportExcelFile\Deca_Vietin_ErrorCode.xlsx"));
template.CopyTo(errorFileLocation);
FileInfo _fileInfo = new FileInfo(errorFileLocation);
var _excelPkg = new ExcelPackage(_fileInfo);
ExcelWorksheet oSheet = excelPkg.Workbook.Worksheets["VieitinErrorCode"];
ExcelWorksheet eSheet = _excelPkg.Workbook.Worksheets["VieitinErrorCode"];
var data = WorksheetToDataTables(oSheet);
int temp;
var listData = data.AsEnumerable().Select(s => new Deca_Vietin_Error_Code
{
Code = s["Code"] != null ? s["Code"].ToString() : "",
Description = s["Description"] != null ? s["Description"].ToString() : ""
}).ToList();
using (var dbConn = Helpers.OrmliteConnection.openConn())
{
if (n == 1)
{
var required = listData.Where(s => String.IsNullOrEmpty(s.Code) ||
String.IsNullOrEmpty(s.Description));
foreach (var item in required)
{
writeErrorToFile(eSheet, rownumber, item, "Vui lòng nhập đầy đủ thông tin bắt buộc");
rownumber++;
}
listData = listData.Except(required).ToList();
if (listData.Count() > 0)
{
n = 1;
}
else
{
n = 0;
}
}
foreach (var item in listData)
{
try
{
var exist = dbConn.SingleOrDefault <Deca_Vietin_Error_Code>("Code={0}", item.Code);
if (exist != null)
{
exist.Description = item.Description;
exist.UpdatedAt = DateTime.Now;
exist.UpdatedBy = currentUser.UserName;
dbConn.Update(exist);
total++;
}
else
{
var newData = new Deca_Vietin_Error_Code();
newData.Code = item.Code;
newData.Description = item.Description;
newData.CreatedAt = DateTime.Now;
newData.CreatedBy = currentUser.UserName;
dbConn.Insert(newData);
total++;
}
}
catch (Exception e)
{
writeErrorToFile(eSheet, rownumber, item, e.Message);
rownumber++;
}
}
}
_excelPkg.Save();
return(Json(new { success = true, total = total, totalError = rownumber - 2, link = errorFileLocation }));
}
}
}
catch (Exception e)
{
return(Json(new { success = false, error = "errors:" + e.Message }));
}
return(Json(new { success = true }));
}
