// excel所有单元格数据验证 public UploadExcelFileResult ValidateExcel() { var result = new UploadExcelFileResult(); result.Success = true; _rowCount = new Dictionary <int, int>(); Stream fileStream = new FileStream(_filePath, FileMode.Open); int edition = this.GetExcelEdition(_filePath); if (edition != 0) { IWorkbook workbook = this.CreateWorkBook(edition, fileStream); int sheetCount = _list.Find(e => e.HeaderRegular != null).HeaderRegular["sheetCount"]; for (int i = 0; i < sheetCount; i++) { ISheet sheet = workbook.GetSheetAt(i); Dictionary <int, string> dict = this.GetExcelHeaders(sheet, ref result, _list); if (result.Success) { _rowCount.Add(i, sheet.LastRowNum); result = this.CheckExcelDatasEnableNull(sheet, _list, dict, _rowCount[i]); } else { break; } } } else { result.Success = false; result.Message = "文件类型错误!"; } fileStream.Close(); return(result); }
// 解析excel数据到DTO public List <TableDTO> Import <TableDTO>() { var uploadExcelFileResult = new UploadExcelFileResult(); var resultList = new List <TableDTO>(); Stream fileStream = new FileStream(_filePath, FileMode.Open); int edition = this.GetExcelEdition(_filePath); IWorkbook workbook = this.CreateWorkBook(edition, fileStream); int sheetCount = _list.Find(e => e.HeaderRegular != null).HeaderRegular["sheetCount"]; ValidateExcel(); for (int i = 0; i < sheetCount; i++) { ISheet sheet = workbook.GetSheetAt(i); string sheetName = sheet.SheetName; Dictionary <int, string> dict = this.GetExcelHeaders(sheet, ref uploadExcelFileResult, _list); var sheetLists = this.GetExcelDatas <TableDTO>(sheet, sheetName, _list, dict, _rowCount[i]); resultList.AddRange(sheetLists); } fileStream.Close(); return(resultList); }
/// <summary> /// 解析并检查EXCEL表头数据 /// </summary> /// <param name="sheet"></param> /// <param name="uploadExcelFileResult"></param> /// <param name="list"></param> /// <returns></returns> public Dictionary <int, string> GetExcelHeaders(ISheet sheet, ref UploadExcelFileResult uploadExcelFileResult, List <Regular> list) { int firstHeaderRowIndex = list.Find(e => e.HeaderRegular != null).HeaderRegular["firstHeaderRow"]; int lastHeaderRowIndex = list.Find(e => e.HeaderRegular != null).HeaderRegular["lastHeaderRow"]; var dict = new Dictionary <int, string>(); try { // 循环获得表头 for (int i = firstHeaderRowIndex - 1; i < lastHeaderRowIndex; i++) { IRow headerRow = sheet.GetRow(i); int cellCount = headerRow.LastCellNum; for (int j = headerRow.FirstCellNum; j < cellCount; j++) { if (!string.IsNullOrEmpty(headerRow.GetCell(j).StringCellValue.Trim())) { // 根据 键-值 是否已存在做不同处理 //TODO 代码待重构!!! try { string oldValue = dict[j]; dict.Remove(j); dict.Add(j, oldValue + headerRow.GetCell(j).StringCellValue.Trim()); } catch (Exception) { dict.Add(j, headerRow.GetCell(j).StringCellValue.Trim()); } } } } // 遍历表头字典,消除空格 for (int i = 0; i < dict.Count; i++) { var value = dict[i]; this.ReplaceSpace(ref value); dict[i] = value; } // 检查表头模板是否被修改 for (int count = 0; count < dict.Count; count++) { Regular header = list.Find(h => h.HeaderText == dict[count]); if (header == null) { uploadExcelFileResult.Success = false; uploadExcelFileResult.Message = "读取EXCEL表头模板时发生错误,可能造成原因是:EXCEL模板被修改!请下载最新EXCEL模板!"; } } } catch (Exception e) { uploadExcelFileResult.Success = false; uploadExcelFileResult.Message = "读取EXCEL表头模板时发生错误,可能造成原因是:EXCEL模板被修改!请下载最新EXCEL模板!"; } return(dict); }
/// <summary> /// 检查excel数据 /// </summary> /// <param name="sheet">excel工作表</param> /// <param name="list">规则集</param> /// <param name="dict">表头</param> /// <param name="rowCount">总数据行数</param> /// <returns>检查结果</returns> public UploadExcelFileResult CheckExcelDatasEnableNull(ISheet sheet, List <Regular> list, Dictionary <int, string> dict, int rowCount) { var result = new UploadExcelFileResult(); result.Success = true; // 记录单个sheet所有错误信息 var sheetErrors = new List <ExcelFileErrorPosition>(); // 表头结束行 int lastHeaderRowIndex = list.Find(e => e.HeaderRegular != null).HeaderRegular["lastHeaderRow"]; // 循环行数据 for (int i = lastHeaderRowIndex; i <= rowCount; i++) { // 标注该行是否出错 bool isrowfalse = false; // 记录该行数据临时对象 var rowDatas = new List <string>(); // 记录该行错误列 var rowErrorCell = new List <int>(); // 记录该行错误列具体错误信息 var rowErrorMessages = new List <string>(); // 记录该行空值数 int nullcount = 0; IRow dataRow = sheet.GetRow(i); int cellCount = dict.Count; // 循环列数据 for (int j = dataRow.FirstCellNum; j < cellCount; j++) { string value = ""; Regular header = list.Find(h => h.HeaderText == dict[j]); //value = dataRow.GetCell(j).ToString(); switch (dataRow.GetCell(j).CellType) { case CellType.Formula: value = dataRow.GetCell(j).StringCellValue.ToString(); break; default: value = dataRow.GetCell(j).ToString(); break; } // 记录可能出错数据 rowDatas.Add(value); // 检查空值 if (!this.CheckNull(value, ref nullcount)) { // 检查类型 if (!this.CheckDataType(header.DataType, value)) { isrowfalse = true; result.Success = false; // 记录该行错误信息 rowErrorCell.Add(j + 1); rowErrorMessages.Add("读取EXCEL数据时发生数据格式错误,请检查该行该列数据格式!"); } else { if (header.DataType == "System.string" || header.DataType == "System.String") { this.ReplaceSpace(ref value); } } } } // 报错处理(空行不报错) if (isrowfalse && nullcount < cellCount) { sheetErrors.Add(new ExcelFileErrorPosition { RowContent = rowDatas, RowIndex = i + 1, CellIndex = rowErrorCell, ErrorMessage = rowErrorMessages }); } } result.ExcelFileErrorPositions = sheetErrors; return(result); }