public void writeErrorToFile(ExcelWorksheet eSheet, int rownumber, Deca_Plan_KPI 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.Year; eSheet.Cells[rownumber, i++].Value = item.Month1; eSheet.Cells[rownumber, i++].Value = item.Month2; eSheet.Cells[rownumber, i++].Value = item.Month3; eSheet.Cells[rownumber, i++].Value = item.Month4; eSheet.Cells[rownumber, i++].Value = item.Month5; eSheet.Cells[rownumber, i++].Value = item.Month6; eSheet.Cells[rownumber, i++].Value = item.Month7; eSheet.Cells[rownumber, i++].Value = item.Month8; eSheet.Cells[rownumber, i++].Value = item.Month9; eSheet.Cells[rownumber, i++].Value = item.Month10; eSheet.Cells[rownumber, i++].Value = item.Month11; eSheet.Cells[rownumber, i++].Value = item.Month12; 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_PlanKPI.xlsx")); template.CopyTo(errorFileLocation); FileInfo _fileInfo = new FileInfo(errorFileLocation); var _excelPkg = new ExcelPackage(_fileInfo); ExcelWorksheet oSheet = excelPkg.Workbook.Worksheets["PlanKPI"]; ExcelWorksheet eSheet = _excelPkg.Workbook.Worksheets["PlanKPI"]; var data = WorksheetToDataTables(oSheet); double temp; var listData = data.AsEnumerable().Select(s => new Deca_Plan_KPI { Name = s["Name"] != null ? s["Name"].ToString() : "", Year = s["Year"] != null ? s["Year"].ToString() : "", Month1 = s["Month1"] == null ? 0 : double.TryParse(s["Month1"].ToString(), out temp) ? double.Parse(s["Month1"].ToString()) : 0, Month2 = s["Month2"] == null ? 0 : double.TryParse(s["Month2"].ToString(), out temp) ? double.Parse(s["Month2"].ToString()) : 0, Month3 = s["Month3"] == null ? 0 : double.TryParse(s["Month3"].ToString(), out temp) ? double.Parse(s["Month3"].ToString()) : 0, Month4 = s["Month4"] == null ? 0 : double.TryParse(s["Month4"].ToString(), out temp) ? double.Parse(s["Month4"].ToString()) : 0, Month5 = s["Month5"] == null ? 0 : double.TryParse(s["Month5"].ToString(), out temp) ? double.Parse(s["Month5"].ToString()) : 0, Month6 = s["Month6"] == null ? 0 : double.TryParse(s["Month6"].ToString(), out temp) ? double.Parse(s["Month6"].ToString()) : 0, Month7 = s["Month7"] == null ? 0 : double.TryParse(s["Month7"].ToString(), out temp) ? double.Parse(s["Month7"].ToString()) : 0, Month8 = s["Month8"] == null ? 0 : double.TryParse(s["Month8"].ToString(), out temp) ? double.Parse(s["Month8"].ToString()) : 0, Month9 = s["Month9"] == null ? 0 : double.TryParse(s["Month9"].ToString(), out temp) ? double.Parse(s["Month9"].ToString()) : 0, Month10 = s["Month10"] == null ? 0 : double.TryParse(s["Month10"].ToString(), out temp) ? double.Parse(s["Month10"].ToString()) : 0, Month11 = s["Month11"] == null ? 0 : double.TryParse(s["Month11"].ToString(), out temp) ? double.Parse(s["Month11"].ToString()) : 0, Month12 = s["Month12"] == null ? 0 : double.TryParse(s["Month12"].ToString(), out temp) ? double.Parse(s["Month12"].ToString()) : 0 }).ToList(); using (var dbConn = Helpers.OrmliteConnection.openConn()) { if (n == 1) { var required = listData.Where(s => String.IsNullOrEmpty(s.Name) || String.IsNullOrEmpty(s.Year)); 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_Plan_KPI>("Name={0} AND Year={1}", item.Name, item.Year); if (exist != null) { exist.Month1 = item.Month1; exist.Month2 = item.Month2; exist.Month3 = item.Month3; exist.Month4 = item.Month4; exist.Month5 = item.Month5; exist.Month6 = item.Month6; exist.Month7 = item.Month7; exist.Month8 = item.Month8; exist.Month9 = item.Month9; exist.Month10 = item.Month10; exist.Month11 = item.Month11; exist.Month12 = item.Month12; exist.UpdatedAt = DateTime.Now; exist.UpdatedBy = currentUser.UserName; dbConn.Update(exist); total++; } else { var newData = new Deca_Plan_KPI(); newData.Name = item.Name; newData.Year = item.Year; newData.Month1 = item.Month1; newData.Month2 = item.Month2; newData.Month3 = item.Month3; newData.Month4 = item.Month4; newData.Month5 = item.Month5; newData.Month6 = item.Month6; newData.Month7 = item.Month7; newData.Month8 = item.Month8; newData.Month9 = item.Month9; newData.Month10 = item.Month10; newData.Month11 = item.Month11; newData.Month12 = item.Month12; 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 })); }