系统中经常用需要读取Excel文件中的数据，导入到数据库，下面是一个示例。

vs：2022

框架：abpvnext 3.1

把Excel中的数据导入到数据库

/// <summary>
/// 导入
/// </summary>
/// <param name="formFile"></param>
/// <returns></returns>
[HttpPost("DefectiveReasonImport")]
public async Task<IActionResult> DefectiveReasonImportAsync(IFormFile formFile)
{
    string resultMsg = string.Empty;
    var result = false;
    int j = 0;//记录行数

    try
    {
        if (formFile.Length > 0)
        {
            var stopWatch = new Stopwatch();
            stopWatch.Start();
            #region 防呆验证
            //将excel表格中的数据转化为dataTable数据
            var getDataTable = NpoiExcelHelper.helper.ExcelToDataTable(formFile.OpenReadStream(), Path.GetExtension(formFile.FileName), out result, out resultMsg);
            if (getDataTable.Rows.Count <= 0) throw new Exception($"Excel表中无数据可导入");
            var columnData = getDataTable.AsEnumerable()
                .Select(row => row.Field<string>("编码"))
                .ToList()
                ;
            for (int i = 0; i < getDataTable.Rows.Count; i++)
            {
                if (getDataTable.Rows[i][0].ToString() == "") throw new Exception($"第[{i + 2}]行，编码不可为空");
                if (getDataTable.Rows[i][1].ToString() == "") throw new Exception($"第[{i + 2}]行，不良原因不可为空");
                var query = _defectiveReasonRepository.Where(d => d.Code == getDataTable.Rows[i][0].ToString());
                if (query.Any())
                    throw new Exception($"第[{i + 2}]行，表中已存在编码[{getDataTable.Rows[i][0].ToString()}]");
            }
            //var codeColumnNullOrEmpty = columnData.Where(d => string.IsNullOrEmpty(d));
            //if (codeColumnNullOrEmpty.Any()) throw new Exception($"编码不可为空");
            var codeColumnGroupList = columnData.GroupBy(d => d)
                .Where(d => d.Count() > 1)
                .Select(d => d.Key)
                .ToList()
                ;
            if (codeColumnGroupList != null && codeColumnGroupList.Count > 0) throw new Exception($"编码存在重复[{codeColumnGroupList.JoinAsString(",")}]");
            //var nameColumnNullOrEmpty = columnData.Where(d => string.IsNullOrEmpty(d));
            //if (nameColumnNullOrEmpty.Any()) throw new Exception($"不良原因不可为空");
            #endregion

            #region 向数据库新增数据
            for (int i = 0; i < getDataTable.Rows.Count; i++)
            {
                j = i;
                var defectiveReasonCreateDto = new DefectiveReasonCreateUpdateDto
                {
                    Code = getDataTable.Rows[i][0].ToString(),
                    Name = getDataTable.Rows[i][1].ToString(),
                    Status = (getDataTable.Rows[i][2] != null && !string.IsNullOrEmpty(getDataTable.Rows[i][2].ToString())) ? Convert.ToBoolean(getDataTable.Rows[i][2]) : true
                };
                var defectiveReason = ObjectMapper.Map<DefectiveReasonCreateUpdateDto, DefectiveReason>(defectiveReasonCreateDto);
                defectiveReason.ChangeCreateUser(DateTime.Now, CurrentUser.Id.ToString());

                await _defectiveReasonRepository.InsertAsync(defectiveReason);
            }
            #endregion
            stopWatch.Stop();

            resultMsg = $"Excel导入成功,耗费总时长{stopWatch.Elapsed.TotalSeconds}秒，总共导入{getDataTable.Rows.Count}条数据";
        }
        else
        {
            resultMsg = "Excel表中无数据可导入！";
        }
    }
    catch (Exception e)
    {
        await CurrentUnitOfWork.RollbackAsync();
        result = false;
        resultMsg = $"{e.Message}";
    }

    return Ok(new { status = result ? 0 : 1, message = resultMsg });
}

Excel帮助类

using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Text;

namespace Imagine.Mes.MesBase.Application.CommonTool
{
    /// <summary>
    /// Excel帮助类
    /// </summary>
    public class NpoiExcelHelper
    {
        private static NpoiExcelHelper _excelImportHelper;

        public static NpoiExcelHelper helper
        {
            get => _excelImportHelper ?? (_excelImportHelper = new NpoiExcelHelper());
            set => _excelImportHelper = value;
        }

        /// <summary>
        /// 读取excel表格中的数据,将Excel文件流转化为dataTable数据源  
        /// 默认第一行为标题 
        /// </summary>
        /// <param name="stream">excel文档文件流</param>
        /// <param name="fileType">文档格式</param>
        /// <param name="isSuccess">是否转化成功</param>
        /// <param name="resultMsg">转换结果消息</param>
        /// <returns></returns>
        public DataTable ExcelToDataTable(Stream stream, string fileType, out bool isSuccess, out string resultMsg)
        {
            isSuccess = false;
            resultMsg = "Excel文件流成功转化为DataTable数据源";
            var excelToDataTable = new DataTable();

            try
            {
                //Workbook对象代表一个工作簿,首先定义一个Excel工作薄
                IWorkbook workbook;

                //XSSFWorkbook 适用XLSX格式，HSSFWorkbook 适用XLS格式
                #region 判断Excel版本
                switch (fileType)
                {
                    //.XLSX是07版(或者07以上的)的Office Excel
                    case ".xlsx":
                        workbook = new XSSFWorkbook(stream);
                        break;
                    //.XLS是03版的Office Excel
                    case ".xls":
                        workbook = new HSSFWorkbook(stream);
                        break;
                    default:
                        throw new Exception("Excel文档格式有误");
                }
                #endregion

                var sheet = workbook.GetSheetAt(0);
                var rows = sheet.GetRowEnumerator();

                var headerRow = sheet.GetRow(0);
                int cellCount = headerRow.LastCellNum;//最后一行列数（即为总列数）

                //获取第一行标题列数据源,转换为dataTable数据源的表格标题名称
                for (var j = 0; j < cellCount; j++)
                {
                    var cell = headerRow.GetCell(j);
                    excelToDataTable.Columns.Add(cell.ToString());
                }

                //获取Excel表格中除标题以为的所有数据源，转化为dataTable中的表格数据源
                for (var i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
                {
                    var dataRow = excelToDataTable.NewRow();

                    var row = sheet.GetRow(i);

                    if (row == null) continue; //没有数据的行默认是null　

                    for (int j = row.FirstCellNum; j < cellCount; j++)
                    {
                        if (row.GetCell(j) != null)//单元格内容非空验证
                        {
                            #region NPOI获取Excel单元格中不同类型的数据
                            //获取指定的单元格信息
                            var cell = row.GetCell(j);
                            switch (cell.CellType)
                            {
                                //首先在NPOI中数字和日期都属于Numeric类型
                                //通过NPOI中自带的DateUtil.IsCellDateFormatted判断是否为时间日期类型
                                case CellType.Numeric when DateUtil.IsCellDateFormatted(cell):
                                    dataRow[j] = cell.DateCellValue;
                                    break;
                                case CellType.Numeric:
                                    //其他数字类型
                                    dataRow[j] = cell.NumericCellValue;
                                    break;
                                //空数据类型
                                case CellType.Blank:
                                    dataRow[j] = "";
                                    break;
                                //公式类型
                                case CellType.Formula:
                                    {
                                        HSSFFormulaEvaluator eva = new HSSFFormulaEvaluator(workbook);
                                        dataRow[j] = eva.Evaluate(cell).StringValue;
                                        break;
                                    }
                                //布尔类型
                                case CellType.Boolean:
                                    dataRow[j] = row.GetCell(j).BooleanCellValue;
                                    break;
                                ////错误
                                //case CellType.Error:
                                //    dataRow[j] = HSSFErrorConstants.GetText(row.GetCell(j).ErrorCellValue);
                                //    break;
                                //    //其他类型都按字符串类型来处理（未知类型CellType.Unknown，字符串类型CellType.String）
                                default:
                                    dataRow[j] = cell.StringCellValue;
                                    break;
                            }
                            #endregion
                        }
                    }
                    excelToDataTable.Rows.Add(dataRow);
                }

                isSuccess = true;
            }
            catch (Exception e)
            {
                resultMsg = e.Message;
            }

            return excelToDataTable;
        }
    }
}