public JsonResult Get(MonthReportSearchParam param, int pageIndex = 1, int pageSize = 20) { try { var list = service.GetSaleManageList(param); return(Json(ResultUtil.List <MonthReportSaleManageViewDto>(list))); } catch (Exception ex) { Log.Error(ex.Message, ex); return(Json(ResultUtil.Fail())); } }
public IActionResult Import(int year, int month, IFormFile excelFile) { ViewBag.ReturnUrl = $"/SaleManage/Index?year={year}&month={month}"; ViewBag.ImportUrl = $"/SaleManage/Import?year={year}&month={month}"; // 基础验证 if (excelFile == null || Path.GetExtension(excelFile.FileName) != ".xlsx") { return(Content("不受支持的文件")); } // 获取原始数据 MonthReportSearchParam param = new MonthReportSearchParam(); param.Month = month; param.Year = year; MonthReportService service = new MonthReportService(); var source = service.GetSaleManageList(param); if (source.Count() == 0) { return(Content("当前日期原数据为空")); } // 解析Excel中的记录 string sWebRootFolder = _hostingEnvironment.WebRootPath; string folderName = Path.Combine(sWebRootFolder, "reports"); if (!Directory.Exists(folderName)) { Directory.CreateDirectory(folderName); } FileInfo file = new FileInfo(Path.Combine(folderName, excelFile.FileName)); try { using (FileStream fs = new FileStream(file.ToString(), FileMode.Create)) { excelFile.CopyTo(fs); fs.Flush(); } using (ExcelPackage package = new ExcelPackage(file)) { StringBuilder sb = new StringBuilder(); ExcelWorksheet worksheet = package.Workbook.Worksheets[1]; int rowCount = worksheet.Dimension.Rows; int ColCount = worksheet.Dimension.Columns; if (ColCount != 19) { return(Content("不合法的数据列")); } List <ImportResultViewModel> result = new List <ImportResultViewModel>(); // 获取转换后的数据列表 List <MonthReportEntity> content = new List <MonthReportEntity>(); for (int row = 2; row <= rowCount; row++) { Convert(worksheet, row); } // 内部方法,单条处理数据 void Convert(ExcelWorksheet sheet, int idx) { try { var entity = new MonthReportEntity(); int id = 0; decimal fj = 0; decimal hc = 0; decimal bs = 0; decimal zjc = 0; decimal dsf = 0; decimal ywzdf = 0; decimal zsf = 0; decimal zsjyjl = 0; decimal zbfy = 0; decimal ccbt = 0; decimal txf = 0; decimal qtfy = 0; int y = 0; int m = 0; int.TryParse(sheet.Cells[idx, 2].Value.ToString(), out y); int.TryParse(sheet.Cells[idx, 3].Value.ToString(), out m); int.TryParse(sheet.Cells[idx, 1].Value.ToString(), out id); decimal.TryParse(sheet.Cells[idx, 8].Value.ToString(), out fj); decimal.TryParse(sheet.Cells[idx, 9].Value.ToString(), out hc); decimal.TryParse(sheet.Cells[idx, 10].Value.ToString(), out bs); decimal.TryParse(sheet.Cells[idx, 11].Value.ToString(), out zjc); decimal.TryParse(sheet.Cells[idx, 12].Value.ToString(), out dsf); decimal.TryParse(sheet.Cells[idx, 13].Value.ToString(), out ywzdf); decimal.TryParse(sheet.Cells[idx, 14].Value.ToString(), out zsf); decimal.TryParse(sheet.Cells[idx, 15].Value.ToString(), out zsjyjl); decimal.TryParse(sheet.Cells[idx, 16].Value.ToString(), out zbfy); decimal.TryParse(sheet.Cells[idx, 17].Value.ToString(), out ccbt); decimal.TryParse(sheet.Cells[idx, 18].Value.ToString(), out txf); decimal.TryParse(sheet.Cells[idx, 19].Value.ToString(), out qtfy); entity.Id = id; entity.FJ = fj; entity.HC = hc; entity.BS = bs; entity.ZJC = zjc; entity.DSF = dsf; entity.YWZDF = ywzdf; entity.ZSF = zsf; entity.ZSJYJL = zsjyjl; entity.ZBFY = zbfy; entity.CCBT = ccbt; entity.TXF = txf; entity.QTFY = qtfy; entity.Year = y; entity.Month = m; var deptName = sheet.Cells[idx, 4].Value.ToString(); var salerName = sheet.Cells[idx, 6].Value.ToString(); var r = new ImportResultViewModel { Id = id, DeptName = deptName, SalerName = salerName, Result = "清洗失败", Remark = "源数据中不存在此记录" }; var s = source.Where(mr => mr.Id == id).SingleOrDefault(); if (s == null) { r.Result = "数据清洗失败"; r.Remark = "元数据中不存在此记录"; } else if (s.Year != year || s.Month != month) { r.Result = "数据清洗失败"; r.Remark = "年度和月度与源数据不符"; } else if (s.SalerName.Trim() != salerName.Trim() || s.DeptName.Trim() != deptName.Trim()) { r.Result = "数据清洗失败"; r.Remark = "部门和业务员不匹配"; } else { var submitResult = service.SaleManageSubmit(entity, AppUser); if (submitResult.Code == 200) { r.Result = "更新成功"; r.Remark = ""; } else { r.Result = "数据更新失败"; r.Remark = submitResult.Message; } } result.Add(r); } catch (Exception ex) { result.Add(new ImportResultViewModel { Id = 0, DeptName = "", SalerName = "", Result = "系统错误", Remark = $"第{idx}条记录发生错误:{ex.Message}" }); } } return(View("Result", result)); } } catch (Exception ex) { return(Content(ex.Message)); } }