public void ReadSheet <T>(string filePath, ReadSheetOptions <T> option) where T : class, new() { this.CheckExcelInfo(filePath); using (var stream = File.OpenRead(filePath)) { this.ReadSheet <T>(stream, option); } }
public void ReadSheet <T>(Stream stream, ReadSheetOptions <T> option) where T : class, new() { Inspector.NotNull(stream, "Excel文件流不能为空"); Inspector.NotNull(option, $"{nameof(ReadSheetOptions<T>)} can not be null"); //匹配SheetName var sheetName = ""; { var sheetNames = this.GetSheetNames(stream, false); Inspector.Validation(option.ReadWay == ReadWay.SheetIndex && option.SheetIndex > sheetNames.Count(), $"指定的SheetIndex {option.SheetIndex} 无效,实际只存在{sheetNames.Count()}个Sheet"); Inspector.Validation(option.ReadWay == ReadWay.SheetName && !sheetNames.Contains(option.SheetName), $"指定的SheetName {option.SheetName} 不存在"); sheetName = option.ReadWay switch { ReadWay.SheetIndex => sheetNames.ElementAt(option.SheetIndex - 1), ReadWay.SheetName => option.SheetName }; } //Excel中的表头列与实体类中字段映射(index:集合中元素的位置,cellRef:单元格的A1 B1中的A B这种) var fieldLoc = new List <(int index, string excelField, string cellRef, string classField, bool allowNull, PropertyInfo prop)>(); { var props = ReflectionHelper.NewInstance.GetSortedReadProps <T>().Select(t => t.prop).ToList(); for (int index = 0; index < props.Count(); index++) { var attribute = props[index].GetCustomAttribute <ExcelKitAttribute>(); fieldLoc.Add((index, attribute.Desc, "", attribute.Code ?? props[index].Name, attribute.AllowNull, props[index])); } } using (var sheetDoc = SpreadsheetDocument.Open(stream, false)) { WorkbookPart workbookPart = sheetDoc.WorkbookPart; //1.目标Sheet的Rid是否存在 string rId = workbookPart.Workbook.Sheets?.Cast <Sheet>()?.FirstOrDefault(t => t.Name.Value == sheetName)?.Id?.Value; Inspector.NotNullOrWhiteSpace(rId, $"不存在名为:{sheetName} 的Sheet"); SharedStringTablePart shareStringPart; if (workbookPart.GetPartsOfType <SharedStringTablePart>().Count() > 0) { shareStringPart = workbookPart.GetPartsOfType <SharedStringTablePart>().First(); } else { shareStringPart = workbookPart.AddNewPart <SharedStringTablePart>(); } string[] shareStringItemValues = shareStringPart.GetItemValues().ToArray(); //2.反转Sheet顺序 foreach (var workSheetPart in workbookPart.WorksheetParts?.Reverse()) { //是否是指定Sheet的Rid string partRelationshipId = workbookPart.GetIdOfPart(workSheetPart); if (partRelationshipId != rId) { continue; } //读取失败的原始数据信息 (Dictionary <string, object> odata, List <(string rowIndex, string columnName, string cellValue, string errorMsg)> failInfos)failRowData = (new Dictionary <string, object>(), new List <(string rowIndex, string columnName, string cellValue, string errorMsg)>()); //创建Reader OpenXmlReader reader = OpenXmlReader.Create(workSheetPart); //工具类实例 var reflection = ReflectionHelper.NewInstance; while (reader.Read()) { if (reader.ElementType == typeof(Worksheet)) { reader.ReadFirstChild(); } if (reader.ElementType == typeof(Row)) { var row = (Row)reader.LoadCurrentElement(); //3.读取表头列,匹配字段信息 if (row.RowIndex == option.HeadRow) { foreach (Cell cell in row.Elements <Cell>()) { if (cell.CellReference != null && cell.CellReference.HasValue) { //excel中的表头列字段 string outerCode = cell.GetValue(shareStringItemValues); if (fieldLoc.Exists(t => t.excelField == outerCode)) { var fieldInfo = fieldLoc.FirstOrDefault(t => t.excelField == outerCode); fieldInfo.cellRef = StringHelper.RemoveNumber(cell.CellReference); fieldLoc[fieldInfo.index] = fieldInfo; } } } //实体上定义了ExcelKit特性的字段未在Excel中匹配到 var unMatchedField = fieldLoc.Where(t => string.IsNullOrWhiteSpace(t.cellRef)); if (unMatchedField.Count() > 0) { var obj = unMatchedField.FirstOrDefault(); var msg = $"{typeof(T).Name}中的字段{obj.classField}特性上指定的Desc:{obj.excelField} 未在Excel列头中匹配到"; throw new ExcelKitException(msg); } continue; } if (row.RowIndex < option.DataStartRow) { continue; } if (option.DataEndRow.HasValue && row.RowIndex > option.DataEndRow) { break; } //读取到的每行数据 T model = new T(); //excel原始数据 failRowData.odata.Clear(); //失败信息 failRowData.failInfos.Clear(); //是否读取成功 var readSuc = true; //4. row.Elements<Cell>()获取出来的会自动跳过为空的单元格 foreach (Cell cell in row.Elements <Cell>()) { //4.1 跳过cell引用为空的 if (cell.CellReference == null || !cell.CellReference.HasValue) { continue; } //4.2 当前循环的cell列位置(不含数字) var loopCellRef = StringHelper.RemoveNumber(cell.CellReference); //不存在或匹配列信息不一致的跳过 var fieldInfo = fieldLoc.FirstOrDefault(t => t.cellRef.Equals(loopCellRef, StringComparison.OrdinalIgnoreCase)); if (fieldInfo == (0, null, null, null, false, null) || !loopCellRef.Equals(fieldInfo.cellRef, StringComparison.OrdinalIgnoreCase)) { continue; } //Excel中读取到的值 string value = cell.GetValue(shareStringItemValues); Inspector.Validation(!fieldInfo.allowNull && string.IsNullOrWhiteSpace(value), $"Excel中列 {fieldInfo.excelField} 为必填项"); try { failRowData.odata.Add(fieldInfo.excelField, value); reflection.SetValue(fieldInfo.prop, ref model, value, fieldInfo.allowNull); } catch (Exception ex) { readSuc = false; failRowData.failInfos.Add((row.RowIndex, fieldInfo.excelField, value?.ToString(), ex.Message)); } } //读取成功执行 if (readSuc) { option.SucData?.Invoke(model, row.RowIndex.Value); } else { option.FailData?.Invoke(failRowData.odata, failRowData.failInfos); } } } } sheetDoc.Close(); } } }