/// <summary> /// 拓展方法,生成EXECL /// </summary> /// <param name="info">EXECL相关信息</param> /// <param name="token">用户认证令牌</param> /// <returns>Execl路径</returns> public static MemoryStream ExportExeclStream(this ExcelInfo info) { //1.获取列表对应数据 DataTable dt = GetGirdData(info); //2.创建Execl文档 return(NPOIHelper.Export(dt, info.ColumnInfoList)); }
/// <summary> ///从上传文件流中读取数据 保存为datatable /// </summary> /// <param name="ins">输入流</param> /// <param name="datasheet">数据得sheet表格</param> /// <returns>数据</returns> public virtual DataTable GetDataFromExcel(Stream ins, out ISheet datasheet) { return(NPOIHelper.GetDataFromExcel(ins, out datasheet)); }
/// <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); }