感谢这篇文章,[Npoi]Npoi导入Excel, 转为Entity
原文章使用了CustomAttribute,本文改为直接用Propertie,写着方便-_-||
项目场景:
C# 使用 NPOI 读取 Excel 转为 Entity
用DataTable存储读取到的Excel数据,再走反射那套操作
解决方案:
public class Archive
{
public string ValueID { get; set; } = string.Empty;
public string ValueName { get; set; } = string.Empty;
}
static void Main(string[] args)
{
var Archives = new ExcelReader(Path.Combine(path, "Archive.xlsx")).ToList<Archive>();
Console.WriteLine(Archives.First().ValueID);
}
public class ExcelReader
{
public string FilePath { get; set; } = string.Empty;
public int SheetIndex { get; set; } = 0;
public string FileExt { get { return Path.GetExtension(FilePath).ToLower(); } }
private DataTable _dataTable;
/// <summary>
/// 初始化文件路径和Sheet序号
/// </summary>
/// <param name="filePath"></param>
/// <param name="sheetIndex"></param>
public ExcelReader(string filePath, int sheetIndex = 0)
{
FilePath = filePath;
SheetIndex = sheetIndex;
}
/// <summary>
/// 转为指定类型
/// </summary>
/// <typeparam name="T"> </typeparam>
/// <returns> </returns>
public List<T> ToList<T>()
{
if (_dataTable is null) ToDataTable();
var list = new List<T>();
var type = typeof(T);
var properties = type.GetProperties();
foreach (DataRow row in _dataTable.Rows)
{
var t = Activator.CreateInstance<T>();
foreach (var property in properties)
{
var prop_name = property.Name;
var value = row[prop_name];
if (value != DBNull.Value)
{
var d = Convert.ChangeType(value, property.PropertyType);
property.SetValue(t, d);
}
}
list.Add(t);
}
return list;
}
/// <summary>
/// Excel转为DataTable
/// </summary>
/// <returns> </returns>
private ExcelReader ToDataTable()
{
_dataTable = new DataTable();
using (var fs = new FileStream(FilePath, FileMode.Open, FileAccess.Read))
{
IWorkbook? workbook;
if (FileExt == ".xlsx") { workbook = new XSSFWorkbook(fs); } else if (FileExt == ".xls") { workbook = new HSSFWorkbook(fs); } else { workbook = null; }
if (workbook == null) { throw new Exception("Wrong File Type. Use .xlsx or .xls"); }
var sheet = workbook.GetSheetAt(SheetIndex);
if (sheet == null)
{
throw new Exception("Excel sheet not found");
}
var headerRow = sheet.GetRow(0);
var headerRowCount = headerRow.LastCellNum;
for (var i = headerRow.FirstCellNum; i < headerRowCount; i++)
{
var column = new DataColumn(headerRow.GetCell(i).StringCellValue);
_dataTable.Columns.Add(column);
}
for (var i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)
{
var row = sheet.GetRow(i);
var dataRow = _dataTable.NewRow();
for (var j = row.FirstCellNum; j < headerRowCount; j++)
{
if (row.GetCell(j) != null)
{
dataRow[j] = row.GetCell(j).ToString();
}
}
_dataTable.Rows.Add(dataRow);
}
}
return this;
}
}