[Npoi]Npoi导入Excel, 转为Entity

Npoi导入Excel

Npoi导入Excel其实只要读成DataTable就可以随意操作了, 比如转为Entity…

By: 胖纸不争
NetCore群: 743336452

核心代码:

public class ExcelImport
{
    public string FilePath { get; set; }
    public string SheetName { get; set; }

    private DataTable _dataTable;

    /// <summary>
    /// 初始化文件路径和Sheet名称
    /// </summary>
    /// <param name="filePath">  </param>
    /// <param name="sheetName"> </param>
    public ExcelImport(string filePath, string sheetName = "Sheet1")
    {
        FilePath = filePath;
        SheetName = sheetName;
    }

    /// <summary>
    /// 转为指定类型
    /// </summary>
    /// <typeparam name="T"> </typeparam>
    /// <returns> </returns>
    public List<T> ToList<T>() where T : IExcelStruct
    {
        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.GetCustomAttribute<ExcelColumnAttribute>().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 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 <= 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;
    }
}

封装了一个库, 用来快速导入: Powers.NpoiExcel

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<User>();

_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