C# 之NPOI 操作Excel

NuGet 安装 NPOI

命名空间

using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;  // 对于 .xlsx 格式
using NPOI.HSSF.UserModel;  // 对于 .xls 格式
using Model.Common;  

添加相关方法

[HttpPost]
public IActionResult Upload(IFormFile file)
{
    ApiResult backMgs = new ApiResult();

    if (file == null || file.Length == 0)
    {
        return Content("请选择一个文件上传。");
    }



    //获取文件路径
    var filePath = Path.Combine(Directory.GetCurrentDirectory(), "wwwroot", "uploads", file.FileName);
    //获取文件后缀名
    string sExt = Path.GetExtension(filePath).ToLower();

    // 确保上传的文件夹存在  
    var fileInfo = new FileInfo(filePath);
    fileInfo.Directory.Create();

    // 保存文件  
    using (var stream = new FileStream(filePath, FileMode.Create))
    {
        file.CopyTo(stream);
    }

    //成功数据列表
    var tList = new List<stuList>();
    //失败数据列表
    var eList = new List<stuList>();


    using (var stream = file.OpenReadStream())
    {

        //根据文件流创建excel数据结构
        IWorkbook workbook = null;
        //区分文件类型
        if (sExt == ".xlsx")
        {
            workbook = new XSSFWorkbook(stream); // .XLSX格式
        }
        else if (sExt == ".xls")
        {
            workbook = new HSSFWorkbook(stream); // .XLS格式
        }

        // 获取第一个Sheet页
        ISheet sheet = workbook.GetSheetAt(0);

        //获取每行数据
        //foreach (IRow row in sheet)
        //{
        //    //循环每行数据
        //    foreach (ICell cell in row)
        //    {
        //        var _cell = cell.ToString();
        //    }
        //    Console.WriteLine();
        //}


        // 读取表头
        IRow headerRow = sheet.GetRow(0);
        for (int i = 0; i < headerRow.LastCellNum; i++)
        {
            ICell cell = headerRow.GetCell(i);
        }

        //获取所有数据
        var allStu = _stuList.AllStuList();

        //循环一共多少行
        for (int i = 1; i <= sheet.LastRowNum; i++)
        {
            //解析每一行数据
            var currentRow = sheet.GetRow(i);
            if (currentRow != null)
            {
                List<string> list = new List<string>();
                //每行多少列
                for (int cell = 0; cell <= currentRow.LastCellNum; cell++)
                {
                    if (null == cell) continue;


                    var data = currentRow.GetCell(cell)?.ToString();
                    list.Add(data);

                }
                //避免id重复，使用时间戳
                //DateTimeOffset now = DateTimeOffset.UtcNow;
                //long timestamp = now.ToUnixTimeSeconds();


                var stuList = new stuList();
                //stuList.id = Convert.ToInt32(timestamp) + i;
                stuList.number = Convert.ToInt32(list[0]);
                stuList.stuName = list[1]?.ToString();
                stuList.stuGrade = list[2]?.ToString();
                stuList.createTime = DateTime.Now;
                stuList.school = list[3]?.ToString();
                stuList.ranking = list[4]?.ToString();
                stuList.sysNumber = Utility.uniqueNumber();

                //数据库中，有相同编号的不添加
                var _id = allStu.Where(a => a.number == Convert.ToInt32(list[0])).FirstOrDefault();
                //当前传入的列表中，有相同的编号，也不添加
                var curId = tList.Where(a => a.number == Convert.ToInt32(list[0])).FirstOrDefault();

                if (_id != null || curId != null)
                {
                    eList.Add(stuList);
                }
                else
                {
                    tList.Add(stuList);
                }


            }
        }

        _stuList.AddStuList(tList);
    }

    var _json = eList.Select(a =>
    {
        return new
        {
            a.number,
            a.stuName,
            a.stuGrade,
            createTime = Utility.FormatCreateTime(a.createTime),
            a.school,
            a.ranking
        };
    });
    backMgs.Code = 0;
    backMgs.Data = _json;
    backMgs.Msg = $"上传成功,一共{tList.Count}条,失败条数{eList.Count}";

    return Ok(backMgs);
}