public void ReadRows(string filePath, ReadRowsOptions option)
{
this.CheckExcelInfo(filePath);
using (var stream = File.OpenRead(filePath))
{
this.ReadRows(stream, option);
}
}
public void ReadRows(Stream stream, ReadRowsOptions option)
{
Inspector.NotNull(stream, "Excel文件流不能为空");
Inspector.NotNull(option, $"{nameof(ReadRowsOptions)}不能为null");
if (option.ReadWay == ReadWay.SheetIndex)
{
Inspector.MoreThanOrEqual(option.SheetIndex, 1, "Sheet索引至少从1开始");
}
if (option.ReadWay == ReadWay.SheetName)
{
Inspector.NotNullOrWhiteSpace(option.SheetName, "Sheet名称不能为空");
}
//匹配SheetName
var sheetName = "";
{
var sheetNames = this.GetSheetNames(stream, false);
Inspector.Validation(option.ReadWay == ReadWay.SheetIndex && option.SheetIndex > sheetNames.Count(), $"指定的Sheet索引 {option.SheetIndex} 无效，实际只存在{sheetNames.Count()}个Sheet");
Inspector.Validation(option.ReadWay == ReadWay.SheetName && !sheetNames.Contains(option.SheetName), $"指定的Sheet名称 {option.SheetName} 不存在");
sheetName = option.ReadWay switch { ReadWay.SheetIndex => sheetNames.ElementAt(option.SheetIndex - 1), ReadWay.SheetName => option.SheetName };
}
//包含返回空单元格校验
Inspector.Validation(option.ReadEmptyCell && (option.ColumnHeaders == null || option.ColumnHeaders.Count() == 0), $"读取要包含空格列时，必须指定读取的列头信息，{nameof(option.ColumnHeaders)}不能为空");
if (option.ReadEmptyCell)
{
foreach (var cellRef in option.ColumnHeaders)
{
Inspector.Validation(!_excelColumn.Contains(cellRef), $"{nameof(option.ColumnHeaders)}中 {cellRef} Excel列头无效");
}
}
using (var sheetDoc = SpreadsheetDocument.Open(stream, false))
{
WorkbookPart workbookPart = sheetDoc.WorkbookPart;
SharedStringTablePart shareStringPart;
if (workbookPart.GetPartsOfType <SharedStringTablePart>().Count() > 0)
{
shareStringPart = workbookPart.GetPartsOfType <SharedStringTablePart>().First();
}
else
{
shareStringPart = workbookPart.AddNewPart <SharedStringTablePart>();
}
string[] shareStringItemValues = shareStringPart.GetItemValues().ToArray();
//目标Sheet的Rid
string rId = workbookPart.Workbook.Sheets?.Cast <Sheet>()?.FirstOrDefault(t => t.Name.Value == sheetName)?.Id?.Value;
Inspector.NotNullOrWhiteSpace(rId, $"不存在名为：{sheetName} 的Sheet");
//读取多个Sheet
foreach (var worksheetPart in workbookPart.WorksheetParts?.Reverse())
{
//是否是指定Sheet的Rid
string partRelationshipId = workbookPart.GetIdOfPart(worksheetPart);
if (partRelationshipId != rId)
{
continue;
}
OpenXmlReader reader = OpenXmlReader.Create(worksheetPart);
//数据行
var list = new List <(string cellRef, string value)>();
while (reader.Read())
{
if (reader.ElementType == typeof(Worksheet))
{
reader.ReadFirstChild();
}
if (reader.ElementType == typeof(Row))
{
var row = (Row)reader.LoadCurrentElement();
if (row.RowIndex < option.DataStartRow)
{
continue;
}
if (option.DataEndRow.HasValue && row.RowIndex > option.DataEndRow)
{
break;
}
list.Clear();
foreach (Cell cell in row.Elements <Cell>())
{
if (cell.CellReference == null || !cell.CellReference.HasValue)
{
continue;
}
//无数字的列头名（A1、B2、C1中的A、B、C）
var loopCellRef = StringHelper.RemoveNumber(cell.CellReference);
if (option.ReadEmptyCell && !option.ColumnHeaders.Contains(loopCellRef))
{
continue;
}
string value = cell.GetValue(shareStringItemValues);
list.Add((loopCellRef, value));
}
//包含空单元格，实际却没有则补齐数据
if (option.ReadEmptyCell)
{
var willAdd = option.ColumnHeaders.Except(list.Select(t => t.cellRef));
list.AddRange(willAdd.Select(item => (item, "")));
list = list.OrderBy(t => t.cellRef).ToList();
}
option.RowData?.Invoke(list.Select(t => t.value).ToList());
}
}
}
sheetDoc.Close();
}
if (option.IsDisposeStream)
{
stream.Dispose();
}
}