public void ReadSheet(string filePath, ReadSheetDicOptions option)
{
this.CheckExcelInfo(filePath);
using (var stream = File.OpenRead(filePath))
{
this.ReadSheet(stream, option);
}
}
public void ReadSheet(Stream stream, ReadSheetDicOptions option)
{
Inspector.NotNull(stream, "Excel文件流不能为空");
Inspector.NotNull(option, $"{nameof(ReadSheetDicOptions)} can not be null");
Inspector.Validation(option.ExcelFields == null || option.ExcelFields.Length == 0 || option.ExcelFields.Count(t => string.IsNullOrWhiteSpace(t.field)) > 0, "Excel中的列头信息不能为空或存在为空的列名");
//匹配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, ColumnType columnType, bool allowNull, string cellRef)>();
{
for (int index = 0; index < option.ExcelFields.Count(); index++)
{
Inspector.Validation(fieldLoc.Exists(t => t.excelField == option.ExcelFields[index].field?.Trim()), "指定读取的 ExcelFields 中存在相同的列名");
fieldLoc.Add((index, option.ExcelFields[index].field, option.ExcelFields[index].type, option.ExcelFields[index].allowNull, ""));
}
}
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 excelField = cell.GetValue(shareStringItemValues);
if (fieldLoc.Exists(t => t.excelField == excelField))
{
var fieldInfo = fieldLoc.FirstOrDefault(t => t.excelField == excelField);
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 unmatchFields = string.Join("、", unMatchedField.Select(t => t.excelField));
var msg = $"指定的ExcelFields中的字段【{unmatchFields}】不存在于Excel中";
throw new ExcelKitException(msg);
}
continue;
}
if (row.RowIndex < option.DataStartRow)
{
continue;
}
if (option.DataEndRow.HasValue && row.RowIndex > option.DataEndRow)
{
break;
}
//读取到的每行数据
var rowData = new Dictionary <string, object>();
//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, 0, false, null) || !loopCellRef.Equals(fieldInfo.cellRef, StringComparison.OrdinalIgnoreCase))
{
continue;
}
//Excel中读取到的值
string readVal = null;
try
{
readVal = cell.GetValue(shareStringItemValues);
Inspector.Validation(!fieldInfo.allowNull && string.IsNullOrWhiteSpace(readVal), $"Excel中列 {fieldInfo.excelField} 为必填项");
object value = ColumnTypeMapping.Convert(readVal, fieldInfo.columnType, fieldInfo.allowNull);
rowData.Add(fieldInfo.excelField, value);
}
catch (Exception ex)
{
readSuc = false;
failRowData.failInfos.Add((row.RowIndex, fieldInfo.excelField, readVal?.ToString(), ex.Message));
}
}
//5.单元格为空缺失的key补齐（这样做key的顺序和原始的不一致了，有需求时可以使用header上面的cellRef排序解决，为了读取速度此处暂不做）
var lackKeys = fieldLoc.Select(t => t.excelField).Except(rowData.Keys);
foreach (var lackKey in lackKeys)
{
rowData.TryAdd(lackKey, null);
}
//读取成功执行
if (readSuc)
{
option.SucData?.Invoke(rowData, row.RowIndex.Value);
}
else
{
option.FailData?.Invoke(failRowData.odata, failRowData.failInfos);
}
}
}
}
sheetDoc.Close();
}
}