protected ExcelDataHeader GetHeader(ISheet sheet, int headerIndex)
{
ExcelDataHeader excelDataHeader = new ExcelDataHeader();
IRow row = sheet.GetRow(headerIndex);
for (int col = 0; col < row.PhysicalNumberOfCells; col++)
{
excelDataHeader.DataCols.Add(new DataCol
{
ColIndex = col,
ColName = row.GetCell(col).GetCellValue()
});
}
return(excelDataHeader);
}
/// <summary>
/// 获取ExcelDataHeader
/// </summary>
/// <param name="sheet"></param>
/// <param name="headerIndex"></param>
/// <returns></returns>
private ExcelDataHeader GetExcelDataHeader(ISheet sheet, int headerIndex)
{
ExcelDataHeader excelDataHeader = new ExcelDataHeader();
IRow row = sheet.GetRow(headerIndex);
for (int i = 0; i < row.PhysicalNumberOfCells; i++)
{
excelDataHeader.DataCols.Add(
new DataCol
{
ColIndex = i,
ColName = row.GetCell(i).GetCellValue()
}
);
}
return(excelDataHeader);
}
/// <summary>
/// 将IRow转换为ExcelDataRow
/// </summary>
/// <typeparam name="TExcelTemplate"></typeparam>
/// <param name="row"></param>
/// <param name="excelDataHeader"></param>
/// <returns></returns>
private ExcelDataRow ConvertToExcelDataRow <TExcelTemplate>(IRow row, ExcelDataHeader excelDataHeader)
where TExcelTemplate : class, new()
{
var propertys = typeof(TExcelTemplate).GetProperties();
ExcelDataRow excelDataRow = new ExcelDataRow
{
RowIndex = row.RowNum
};
propertys.ToList().ForEach(p =>
{
if (p.IsDefined(typeof(ColNameAttribute), true))
{
ColNameAttribute colNameAttr = p.GetCustomAttribute <ColNameAttribute>();
var dataCol = excelDataHeader.DataCols.FirstOrDefault(dc => dc.ColName == colNameAttr.ColName);
excelDataRow.excelDataColList.Add(new ExcelDataCol
{
ColIndex = dataCol.ColIndex,
ColName = dataCol.ColName,
RowIndex = row.RowNum,
CellString = row.GetCell(dataCol.ColIndex).GetCellValue(),
PropertyName = p?.Name
});
}
else
{
excelDataRow.excelDataColList.Add(new ExcelDataCol
{
ColIndex = 0,
ColName = "",
RowIndex = row.RowNum,
CellString = "",
PropertyName = p?.Name
});
}
});
return(excelDataRow);
}
public ExcelData Convert <TExcelTemplate>(Stream stream, int sheetIndex = 0, int headerIndex = 0, int rowIndex = 1) where TExcelTemplate : class, new()
{
ExcelDataHeader excelDataHeader = new ExcelDataHeader();
List <ExcelDataRow> excelDataRows = new List <ExcelDataRow>();
IWorkbook workbook = WorkbookFactory.Create(stream);
ISheet sheet = workbook.GetSheetAt(sheetIndex);
excelDataHeader = this.GetHeader(sheet, headerIndex);
for (int i = rowIndex; i < sheet.LastRowNum; i++)
{
IRow row = sheet.GetRow(i);
if (row == null || !row.Cells.Any(c => !string.IsNullOrWhiteSpace(c.GetCellValue())))
{
continue;
}
excelDataRows.Add(this.ConvertToExcelDataRow <TExcelTemplate>(row, excelDataHeader));
}
return(new ExcelData
{
ExcelDataHeader = excelDataHeader,
ExcelDataRows = excelDataRows
});
}
/// <summary>
/// 将Sheet转换为ExcelData
/// </summary>
/// <typeparam name="TExcelTemplate">Excel导入的业务类</typeparam>
/// <param name="fileUrl">文件的绝对路径</param>
/// <param name="sheetIndex">Sheet索引 默认0</param>
/// <param name="headerIndex">数据Header索引 默认0</param>
/// <param name="rowIndex">数据Row索引 默认1</param>
/// <returns></returns>
public ExcelData Convert <TExcelTemplate>(Stream stream, int sheetIndex = 0, int headerIndex = 0, int rowIndex = 1)
where TExcelTemplate : class, new()
{
IWorkbook workbook = this.GetWorkBook(stream);
ISheet sheet = this.GetSheet(workbook, sheetIndex);
ExcelDataHeader dataHeader = this.GetExcelDataHeader(sheet, headerIndex);
List <ExcelDataRow> excelDataRows = new List <ExcelDataRow>();
for (int i = rowIndex; i < sheet.PhysicalNumberOfRows; i++)
{
IRow row = sheet.GetRow(i);
if (row == null || !row.Cells.Any(c => !string.IsNullOrWhiteSpace(c.GetCellValue())))
{
continue;
}
excelDataRows.Add(this.ConvertToExcelDataRow <TExcelTemplate>(row, dataHeader));
}
return(new ExcelData
{
ExcelDataHeader = dataHeader,
ExcelDataRows = excelDataRows
});
}
/// <summary>
/// 将IRow转换为ExcelDataRow
/// </summary>
/// <typeparam name="TSource"></typeparam>
/// <param name="row"></param>
/// <param name="excelDataHeader"></param>
/// <returns></returns>
public ExcelDataRow ConvertToExcelDataRow <TSource>(IRow row, ExcelDataHeader excelDataHeader)
where TSource : class, new()
{
ExcelDataRow excelDataRow = new ExcelDataRow
{
RowIndex = row.RowNum
};
var propertys = typeof(TSource).GetProperties();
for (int i = 0; i < excelDataHeader.DataCols.Count; i++)
{
DataCol dataCol = excelDataHeader.DataCols.FirstOrDefault(dc => dc.ColIndex == i);
PropertyInfo matchProperty = propertys.FirstOrDefault(p => p.GetCustomAttribute <ColNameAttribute>().ColName == dataCol?.ColName);
excelDataRow.excelDataColList.Add(new ExcelDataCol
{
ColIndex = i,
ColName = dataCol.ColName,
RowIndex = row.RowNum,
CellString = row.GetCell(i).GetCellValue(),
PropertyName = matchProperty?.Name
});
}
return(excelDataRow);
}
