public void writeErrorToFile(ExcelWorksheet eSheet, int rownumber, CRM_FAQ_Topic item, string error) { using (var dbConn = Helpers.OrmliteConnection.openConn()) { int i = 1; eSheet.Cells[rownumber, i++].Value = item.Name; 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_Topic.xlsx")); template.CopyTo(errorFileLocation); FileInfo _fileInfo = new FileInfo(errorFileLocation); var _excelPkg = new ExcelPackage(_fileInfo); ExcelWorksheet oSheet = excelPkg.Workbook.Worksheets["FAQTopic"]; ExcelWorksheet eSheet = _excelPkg.Workbook.Worksheets["FAQTopic"]; var data = WorksheetToDataTables(oSheet); var listData = data.AsEnumerable().Select(s => new CRM_FAQ_Topic { Name = s["Name"] != null ? s["Name"].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.Name) || 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 newData = new CRM_FAQ_Topic(); newData.Name = item.Name; 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 })); }