.Net 6 Mvc NPOI导入导出

1. 首先先下Newget包

2.创建一个帮助类

using Npoi.Mapper;

namespace gongdannet6
{
    public class ExcelHelper
    {
        /// <summary>
        /// List转Excel
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="list">数据</param>
        /// <param name="sheetName">表名</param>
        /// <param name="overwrite">true,覆盖单元格，false追加内容(list和创建的excel或excel模板)</param>
        /// <param name="xlsx">true-xlsx，false-xls</param>
        /// <returns>返回文件</returns>
        public static MemoryStream ParseListToExcel<T>(List<T> list, string sheetName = "sheet1", bool overwrite = true, bool xlsx = true) where T : class
        {
            var mapper = new Mapper();
            MemoryStream ms = new MemoryStream();
            mapper.Save<T>(ms, list, sheetName, overwrite, xlsx);
            return ms;
        }
        /// <summary>
        /// Excel转为List
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="fileStream"></param>
        /// <param name="sheetname"></param>
        /// <returns></returns>
        public static List<T> ParseExcelToList<T>(Stream fileStream, string sheetname = "") where T : class
        {
            List<T> ModelList = new List<T>();
            var mapper = new Mapper(fileStream);
            List<RowInfo<T>> DataList = new List<RowInfo<T>>();
            if (!string.IsNullOrEmpty(sheetname))
            {
                DataList = mapper.Take<T>(sheetname).ToList();
            }
            else
            {
                DataList = mapper.Take<T>().ToList();
            }

            if (DataList != null && DataList.Count > 0)
            {
                foreach (var item in DataList)
                {
                    ModelList.Add(item.Value);
                }
            }
            return ModelList;
        }
    }
}

3.控制器层

#region NPOI导入
        /// <summary>
        /// NPOI导入
        /// </summary>
        /// <returns></returns>
        [HttpPost]
        public IActionResult Upload()
        {
            //用户List
            List<JF_fuli_Sendemail> users = new List<JF_fuli_Sendemail>();
            //获取到上传的文件
            var file = Request.Form.Files[0];
            if (file.FileName == null)
            {
                return Json(new { message = "请选择要导入的文件", code = 0 });
            }
            else
            {
                //文件转成流
                using (var fileStream = file.OpenReadStream())
                {
                    //excel转实体
                    users = ExcelHelper.ParseExcelToList<JF_fuli_Sendemail>(fileStream, "SheetJS");
                    foreach (var item in users)
                    {
                        _context.JF_fuli_Sendemails.Add(item);
                    }

                }
                if (_context.SaveChanges()==users.Count())
                {
                    return Json(new { message = "导入成功", code = 1 });
                }
                else
                {
                    return Json(new { message = "请重新核对导入的文件", code = 2 });
                }
                
            }

        }
        #endregion

        #region NPOI导出
        public IActionResult DownLoad()
        {
            //生成List
            List<JF_fuli_Sendemail> usrs = new List<JF_fuli_Sendemail>();
            var res = _context.JF_fuli_Sendemails.ToList();
            for (int i = 0; i < res.Count(); i++)
            {
                JF_fuli_Sendemail user = new JF_fuli_Sendemail()
                {
                    seId =res[i].seId,
                    emailtitle = res[i].emailtitle,
                    corpID = res[i].corpID,
                    fanganid = res[i].fanganid,
                    emID = res[i].emID,
                    toemail = res[i].toemail,
                    emailcontent = res[i].emailcontent,
                    fileurl = res[i].fileurl,
                    jssendtime = res[i].jssendtime,
                    relsendtime = res[i].relsendtime,
                    emailtype = res[i].emailtype,
                    sendstate = res[i].sendstate,
                    CreateTime = res[i].CreateTime,
                    IsDel = res[i].IsDel,
                    sendremark = res[i].sendremark,
                    sendphonemsg = res[i].sendphonemsg,
                    relmsgsendtime = res[i].relmsgsendtime,
                    
                };
                usrs.Add(user);
            }
            //List转为Excel文件
            var fileStream = ExcelHelper.ParseListToExcel(usrs);
            return File(fileStream.ToArray(), "application/vnd.ms-excel", "用户信息.xlsx");
        }
        #endregion

4.视图

<div>
    <a href="/Sendemail/DownLoad">导出excel文件</a>
    <form action="/Sendemail/Upload" method="post" enctype="multipart/form-data" >
  选择excel文件:  <input type="file" name="file" />
    <input type="submit" value="上传excel" />
</form>
</div>