Npoi导入Excel其实只要读成DataTable就可以随意操作了, 比如转为Entity…
By: 胖纸不争
NetCore🐧群: 743336452
核心代码:
public class ExcelImport
{
public string FilePath { get; set; }
public string SheetName { get; set; }
private DataTable _dataTable;
///
/// 初始化文件路径和Sheet名称
///
///
///
public ExcelImport(string filePath, string sheetName = "Sheet1")
{
FilePath = filePath;
SheetName = sheetName;
}
///
/// 转为指定类型
///
///
///
public List ToList() where T : IExcelStruct
{
if (_dataTable is null) ToDataTable();
var list = new List();
var type = typeof(T);
var properties = type.GetProperties();
foreach (DataRow row in _dataTable.Rows)
{
var t = Activator.CreateInstance();
foreach (var property in properties)
{
var prop_name = property.GetCustomAttribute().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;
}
///
/// Excel转为DataTable
///
///
private ExcelImport ToDataTable()
{
_dataTable = new DataTable();
using (var fs = new FileStream(FilePath, FileMode.Open, FileAccess.Read))
{
var workbook = WorkbookFactory.Create(fs);
var sheet = workbook.GetSheet(SheetName);
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);
}
var rowCount = sheet.LastRowNum;
for (var i = sheet.FirstRowNum + 1; i
Powers.NpoiExcel
Now Support: Import Excel to Entity
1: Create your entity
public class User : IExcelStruct
{
[ExcelColumn(Name = "姓名")]
public string Name { get; set; }
[ExcelColumn(Name = "年龄")]
public int Age { get; set; }
[ExcelColumn(Name = "性别")]
public string Gender { get; set; }
[ExcelColumn(Name = "生日")]
public DateTime Born { get; set; }
}
2: How to use
var path = Environment.CurrentDirectory + "/files/test.xlsx";
var data = new ExcelImport(path).ToList();
_testOutputHelper.WriteLine("姓名\t年龄\t性别\t生日");
foreach (var item in data)
{
_testOutputHelper.WriteLine($"{item.Name}\t{item.Age}\t{item.Gender}\t{item.Born}");
}
3: Result or run xUnit.Test
Powers.NpioExcel.TestProject.ExcelTests.Test1
持续时间: 244 毫秒
标准输出:
姓名 年龄 性别 生日
张三 20 男 2022/5/1 0:00:00
李四 21 女 2022/5/2 0:00:00
王五 22 男 2022/5/3 0:00:00
赵六 23 女 2022/5/4 0:00:00
田七 24 男 2022/5/5 0:00:00
老八 25 女 2022/5/6 0:00:00
小汉堡 26 男 2022/5/7 0:00:00
奥里给 27 女 2022/5/8 0:00:00
嘿嘿嘿 28 男 2022/5/9 0:00:00
Original: https://www.cnblogs.com/donpangpang/p/16315605.html
Author: 胖纸不争
Title: [Npoi]Npoi导入Excel, 转为Entity
原创文章受到原创版权保护。转载请注明出处：https://www.johngo689.com/590953/
转载文章受原作者版权保护。转载请注明原作者出处！