/// <summary> ///下拉选项校验 /// </summary> /// <param name="e">校验参数</param> /// <returns>错误信息</returns> private static string SelectVerify(ImportVerifyParam e, object extra) { string result = ""; result = ExcelImportHelper.GetCellMsg(e.CellValue, e.ColName, 0, true); if (string.IsNullOrEmpty(result)) { var dict = extra as Dictionary<string, string>; if (dict != null) { if (!dict.ContainsKey(e.CellValue.ToString())) { result += e.ColName + "下拉选项" + e.CellValue + "不存在"; } } } return result; }
/// <summary> /// 校验数据是否正常 /// </summary> /// <param name="dt">数据集</param> /// <param name="outputStream">输出流</param> /// <param name="sheet">数据sheet</param> /// <param name="userInfo">用户信息</param> /// <param name="fileName">文件名称</param> /// <param name="DictColumnFields">英文字段名到中文列名映射关系</param> /// <returns>ImportResult</returns> public virtual ImportResult Verify(DataTable dt, ISheet sheet, Dictionary <string, object> extraInfo, UserInfo userInfo, string fileName, Dictionary <string, ImportVerify> DictColumnFields) { IWorkbook wb = sheet.Workbook; ImportResult result = new ImportResult(); string[] arrErrorMsg = null; string errorMsg = string.Empty; int columnCount = dt.Columns.Count; string columnName = string.Empty; ImportVerify objVerify = null; ImportVerifyParam objVerifyParam = new ImportVerifyParam { DTExcel = dt, CellValue = null, ColName = columnName, ColumnIndex = 0, RowIndex = 0 }; DataRow row = null; object objExtra = null; bool isCorrect = true; //错误数据行样式 var cellErrorStyle = NPOIHelper.GetErrorCellStyle(wb); ICell errorCell = null; IRow sheetRow = null; for (int i = 0, rLength = dt.Rows.Count; i < rLength; i++) { row = dt.Rows[i]; arrErrorMsg = new string[columnCount]; for (int j = 0; j < columnCount; j++) { columnName = dt.Columns[j].ColumnName; if (DictColumnFields.TryGetValue(columnName, out objVerify)) { if (objVerify.VerifyFunc != null) { objVerifyParam.CellValue = row[j]; objVerifyParam.ColumnIndex = j; objVerifyParam.RowIndex = i; objVerifyParam.ColName = objVerify.ColumnName; if (extraInfo != null) { extraInfo.TryGetValue(columnName, out objExtra); } arrErrorMsg[j] = objVerify.VerifyFunc(objVerifyParam, objExtra); } } } errorMsg = string.Join(",", arrErrorMsg.Where(e => !string.IsNullOrEmpty(e))); if (!string.IsNullOrEmpty(errorMsg)) { isCorrect = false; //设置错误信息 sheetRow = sheet.GetRow(StartRowIndex + 1 + i); errorCell = sheetRow.GetCell(columnCount); if (errorCell == null) { errorCell = sheetRow.CreateCell(columnCount); } errorCell.CellStyle = cellErrorStyle; errorCell.SetCellValue(errorMsg); } } //输出错误信息模版 if (!isCorrect) { sheetRow = sheet.GetRow(StartRowIndex); errorCell = sheetRow.GetCell(columnCount); if (errorCell == null) { errorCell = sheetRow.CreateCell(columnCount); } ICellStyle copyStyle = sheetRow.GetCell(columnCount - 1).CellStyle; ICellStyle style = NPOIHelper.GetErrorHeadCellStyle(wb); IFont font = style.GetFont(wb); IFont copyfont = copyStyle.GetFont(wb); font.FontHeight = copyfont.FontHeight; font.FontName = copyfont.FontName; style.FillForegroundColor = copyStyle.FillForegroundColor; style.BorderBottom = copyStyle.BorderBottom; style.BorderLeft = copyStyle.BorderLeft; style.BorderRight = copyStyle.BorderRight; style.BorderTop = copyStyle.BorderTop; errorCell.CellStyle = style; errorCell.SetCellValue("错误信息"); //自适应列宽度 sheet.AutoSizeColumn(columnCount); int width = sheet.GetColumnWidth(columnCount) + 2560; sheet.SetColumnWidth(columnCount, width > NPOIHelper.MAX_COLUMN_WIDTH ? NPOIHelper.MAX_COLUMN_WIDTH : width); result.Message = ExcelImportHelper.GetErrorExcel(wb, fileName); } else { result.IsSuccess = true; } return(result); }
/// <summary> ///Cron表达式校验 /// </summary> /// <param name="e">校验参数</param> /// <returns>错误信息</returns> private static string CronVerify(ImportVerifyParam e, object extra) { string result = ""; result = ExcelImportHelper.GetCellMsg(e.CellValue, e.ColName, 200, true, true); if (string.IsNullOrEmpty(result)) { if (!QuartzHelper.ValidExpression(e.CellValue.ToString())) { result += "Cron表达式格式不正确"; } } return result; }
/// <summary> /// 校验数据是否正常 /// </summary> /// <param name="dt">数据集</param> /// <param name="outputStream">输出流</param> /// <param name="sheet">数据sheet</param> /// <param name="userInfo">用户信息</param> /// <param name="fileName">文件名称</param> /// <param name="DictColumnFields">英文字段名到中文列名映射关系</param> /// <returns>ImportResult</returns> public virtual ImportResult Verify(DataTable dt, ISheet sheet, Dictionary<string, object> extraInfo, UserInfo userInfo, string fileName, Dictionary<string, ImportVerify> DictColumnFields) { IWorkbook wb = sheet.Workbook; ImportResult result = new ImportResult(); string[] arrErrorMsg = null; string errorMsg = string.Empty; int columnCount = dt.Columns.Count; string columnName = string.Empty; ImportVerify objVerify = null; ImportVerifyParam objVerifyParam = new ImportVerifyParam { DTExcel = dt, CellValue = null, ColName = columnName, ColumnIndex = 0, RowIndex = 0 }; DataRow row = null; object objExtra = null; bool isCorrect = true; //错误数据行样式 var cellErrorStyle = NPOIHelper.GetErrorCellStyle(wb); ICell errorCell = null; IRow sheetRow = null; for (int i = 0, rLength = dt.Rows.Count; i < rLength; i++) { row = dt.Rows[i]; arrErrorMsg = new string[columnCount]; for (int j = 0; j < columnCount; j++) { columnName = dt.Columns[j].ColumnName; if (DictColumnFields.TryGetValue(columnName, out objVerify)) { if (objVerify.VerifyFunc != null) { objVerifyParam.CellValue = row[j]; objVerifyParam.ColumnIndex = j; objVerifyParam.RowIndex = i; objVerifyParam.ColName = objVerify.ColumnName; if (extraInfo != null) { extraInfo.TryGetValue(columnName, out objExtra); } arrErrorMsg[j] = objVerify.VerifyFunc(objVerifyParam, objExtra); } } } errorMsg = string.Join(",", arrErrorMsg.Where(e => !string.IsNullOrEmpty(e))); if (!string.IsNullOrEmpty(errorMsg)) { isCorrect = false; //设置错误信息 sheetRow = sheet.GetRow(StartRowIndex + 1 + i); errorCell = sheetRow.GetCell(columnCount); if (errorCell == null) { errorCell = sheetRow.CreateCell(columnCount); } errorCell.CellStyle = cellErrorStyle; errorCell.SetCellValue(errorMsg); } } //输出错误信息模版 if (!isCorrect) { sheetRow = sheet.GetRow(StartRowIndex); errorCell = sheetRow.GetCell(columnCount); if (errorCell == null) { errorCell = sheetRow.CreateCell(columnCount); } ICellStyle copyStyle = sheetRow.GetCell(columnCount - 1).CellStyle; ICellStyle style = NPOIHelper.GetErrorHeadCellStyle(wb); IFont font = style.GetFont(wb); IFont copyfont = copyStyle.GetFont(wb); font.FontHeight = copyfont.FontHeight; font.FontName = copyfont.FontName; style.FillForegroundColor = copyStyle.FillForegroundColor; style.BorderBottom = copyStyle.BorderBottom; style.BorderLeft = copyStyle.BorderLeft; style.BorderRight = copyStyle.BorderRight; style.BorderTop = copyStyle.BorderTop; errorCell.CellStyle = style; errorCell.SetCellValue("错误信息"); //自适应列宽度 sheet.AutoSizeColumn(columnCount); int width = sheet.GetColumnWidth(columnCount) + 2560; sheet.SetColumnWidth(columnCount, width > NPOIHelper.MAX_COLUMN_WIDTH ? NPOIHelper.MAX_COLUMN_WIDTH : width); result.Message = ExcelImportHelper.GetErrorExcel(wb, fileName); } else { result.IsSuccess = true; } return result; }