public ExcelModule() : base("Excel") { //任务列表 Post["/GridExport"] = r => { string excelParam = Request.Form["excelParam"]; if (string.IsNullOrEmpty(excelParam)) { return ""; } ExcelInfo info = JsonConvert.DeserializeObject<ExcelInfo>(excelParam); if (string.IsNullOrEmpty(info.FileName)) { info.FileName = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + ".xls"; } else { if (!info.FileName.EndsWith(".xls")) { info.FileName = info.FileName + ".xls"; } } MemoryStream ms = info.ExportExeclStream(); byte[] msbyte = ms.GetBuffer(); ms.Dispose(); ms = null; return new Response() { Contents = stream => { stream.Write(msbyte, 0, msbyte.Length); }, ContentType = "application/msexcel", StatusCode = HttpStatusCode.OK, Headers = new Dictionary<string, string> { { "Content-Disposition", string.Format("attachment;filename={0}", System.Web.HttpUtility.UrlPathEncode(info.FileName)) }, {"Content-Length", msbyte.Length.ToString()} } }; }; /// <summary> /// 导出Excel模版 /// </summary> /// <param name="type">业务类型</param> /// <param name="FunctionCode">对应功能模块Code</param> /// <returns></returns> Get["/DownLoadTemplate"] = r => { if (AllImports == null) { AllImports = MefConfig.ResolveMany<ExcelImport>(); } string strType = Request.Query["type"]; ExcelImportType type = EnumHelper.StringToEnum<ExcelImportType>(strType); var handler = AllImports.FirstOrDefault(e => e.Type == type); if (handler == null) { throw new Exception("未找到“" + type.ToString() + "”相应处理模块"); } string path = ExcelImporMapper.GetTemplatePath(type); if (File.Exists(path)) { try { string FileName = Path.GetFileName(path); return new Response() { Contents = stream => { handler.GetExportTemplate(path, stream); }, ContentType = MimeHelper.GetMineType(path), StatusCode = HttpStatusCode.OK, Headers = new Dictionary<string, string> { { "Content-Disposition", string.Format("attachment;filename={0}", System.Web.HttpUtility.UrlPathEncode(FileName)) } } }; } catch (Exception ex) { throw ex; } } else { throw new Exception("未找到“" + type.ToString() + "”对应模版文件"); } }; /// <summary> /// 导出Excel模版 /// </summary> /// <returns></returns> Post["/ImportTemplate"] = r => { ImportResult result = new ImportResult(); try { if (AllImports == null) { AllImports = MefConfig.ResolveMany<ExcelImport>(); } string ywType = Request.Query["type"]; if (string.IsNullOrEmpty(ywType)) { throw new ArgumentNullException("ywType"); } //业务类型 ExcelImportType type = EnumHelper.StringToEnum<ExcelImportType>(ywType); //文件 HttpFile file = Request.Files.First(); var handler = AllImports.FirstOrDefault(e => e.Type == type); if (handler == null) { throw new Exception("未找到“" + type.ToString() + "”相应处理模块"); } result = handler.ImportTemplate(file.Value, file.Name, null); if (result.IsSuccess) { //是否获取详细数据,决定后台是否返回 result.ExtraInfo string ReturnDetailData = Request.Query["ReturnDetailData"]; if (string.IsNullOrEmpty(ReturnDetailData) || ReturnDetailData != "1") { result.ExtraInfo = null; } } else { //设置错误模版http路径 result.Message = Request.Url.SiteBase + result.Message; } } catch (Exception ex) { result.IsSuccess = false; result.Message = ex.Message; LogHelper.WriteLog("Excel导入异常", ex); } return Response.AsJson(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> ///返回对应的导出模版数据 /// </summary> /// <param name="ins">导入文件流</param> /// <param name="fileName">文件名</param> /// <param name="userInfo">用户信息</param> /// <returns>ImportResult</returns> public virtual ImportResult ImportTemplate(Stream ins, string fileName, UserInfo userInfo) { if (DictFields == null) { throw new ArgumentNullException("Excel字段映射及校验缓存字典DictFields空异常"); } //1.读取数据 ISheet datasheet = null; DataTable dt = GetDataFromExcel(ins, out datasheet); //2.校验列是否正确 //相同列数 int equalCount = (from p in GetColumnList(dt) join q in DictFields.Keys on p equals q select p).Count(); if (equalCount < DictFields.Keys.Count) { throw new Exception(string.Format("模版列和规定的不一致,正确的列为({0})", string.Join(",", DictFields.Keys))); } //2.改变列名为英文字段名 ImportVerify objVerify = null; List <string> columns = new List <string>(); List <string> removeColumns = new List <string>(); foreach (DataColumn dc in dt.Columns) { if (DictFields.TryGetValue(dc.ColumnName, out objVerify)) { if (objVerify != null) { dc.ColumnName = objVerify.FieldName; columns.Add(objVerify.FieldName); continue; } } removeColumns.Add(dc.ColumnName); } //3.删除无效列 foreach (string remove in removeColumns) { dt.Columns.Remove(remove); } //4.获取校验所需额外参数 Dictionary <string, object> extraInfo = GetExtraInfo(columns, dt); // 英文字段名到中文列名映射关系 Dictionary <string, ImportVerify> DictColumnFields = DictFields.Values.ToDictionary(e => e.FieldName, e => e); //5.开始校验 ImportResult result = Verify(dt, datasheet, extraInfo, userInfo, fileName, DictColumnFields); if (result.IsSuccess) { //校验完成后进行数据类型转换 ImportVerify iv = null; Type columnType = null; DataTable dtNew = dt.Clone(); foreach (DataColumn dc in dtNew.Columns) { if (DictColumnFields != null && DictColumnFields.TryGetValue(dc.ColumnName, out iv)) { if (iv.DataType != null) { columnType = iv.DataType; } else { columnType = dc.DataType; } } else { columnType = typeof(string); } dc.DataType = columnType; } //复制数据到克隆的datatable里 try { foreach (DataRow dr in dt.Rows) { dtNew.ImportRow(dr); } } catch { } //3.保存数据 result.ExtraInfo = SaveImportData(dtNew, extraInfo, userInfo); result.Message = string.Format("成功导入{0}条数据", dtNew.Rows.Count); } 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; }