ASP.NET使用NPOI类库导出Excel

2023年10月15日 在ASP.NET CORE中测试导出成功

        public ActionResult Index()
        {
            DataTable table = new DataTable();
            table.Columns.Add("科室名称");
            table.Columns.Add("备注");
           for (int i = 0; i < 2; i++)
            {
                System.Data.DataRow dr = table.NewRow();
                dr["科室名称"] = "名称"+i;
                dr["备注"] = "备注"+i;

                table.Rows.Add(dr);
            }
            System.IO.MemoryStream ms = Util.DataTableRenderToExcel.RenderDataTableToExcel(table) as System.IO.MemoryStream;
            string filePath = "科室列表.xls";
            new FileExtensionContentTypeProvider().TryGetContentType(filePath, out string contentType); 
            return File(ms.ToArray(), contentType, filePath) ;
        }

 

using JCT.Util;
using JCT.DAL;
using JCT.Model;
using Microsoft.AspNetCore.Cors;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using System.Collections.Generic;
using System.Collections;
using System.Text;
using NPOI.HPSF;
using Microsoft.AspNetCore.StaticFiles;

namespace JCT.Web.Controllers
{
    public class KeShiController : BaseController
    {
        DAL.KeshiDAL dal = new DAL.KeshiDAL();
        DAL.YishiDAL ysdal = new DAL.YishiDAL();
        DAL.MongoDBHelper mongodbhelper = new DAL.MongoDBHelper();

        public KeShiController(IRepository<Admin, int> adminRepository, IRepository<Systemlog, int> logRepository) : base(adminRepository, logRepository)
        {
        }

        public ActionResult Index()
        {
            return View();
        }

        /// <summary>
        /// 拼接条件
        /// </summary>
        /// <returns></returns>
        public string GetCond(string key, string start, string end, string cabh)
        {

            string cond = "1=1";
            /*  if (!string.IsNullOrEmpty(key))
              {
                  key = Tool.GetSafeSQL(key);
                  cond += $" and title like '%{key}%'";
              }
              if (!string.IsNullOrEmpty(start))
              {
                  DateTime d;
                  if (DateTime.TryParse(start, out d))
                  {
                      cond += $" and createdate>='{d.ToString("yyyy-MM-dd HH:mm:ss")}'";
                  }
              }
              if (!string.IsNullOrEmpty(end))
              {
                  DateTime d;
                  if (DateTime.TryParse(end, out d))
                  {
                      cond += $" and createdate<='{d.ToString("yyyy-MM-dd HH:mm:ss")}'";
                  }
              }
              if (!string.IsNullOrEmpty(cabh))
              {
                  cabh = Tool.GetSafeSQL(cabh);
                  cond += $" and cabh='{cabh}'";
              }*/
            return cond;
        }

        /// <summary>
        /// 取总记录数
        /// </summary>
        /// <returns></returns>
        public ActionResult GetTotalCount(string key, string start, string end, string cabh)
        {
            int totalcount = dal.CalcCount(GetCond(key, start, end, cabh));
            return Content(totalcount.ToString());
        }

        /// <summary>
        /// 取分页数据，返回 JSON
        /// </summary>
        /// <param name="pageindex"></param>
        /// <param name="pagesize"></param>
        /// <returns></returns>
        public ActionResult List(int pageindex, int pagesize, string key, string start, string end, string cabh)
        {
            List<Model.Keshi> list = dal.GetListArray("*", "id desc", pagesize, pageindex, GetCond(key, start, end, cabh));
            ArrayList arr = new ArrayList();
            foreach (var item in list)
            {
                int yscount = ysdal.CalcCount($"ksid={item.id}");
                arr.Add(new
                {
                    id = item.id,
                    createtime = item.createtime,
                    ksname = item.ksname,
                    remark = item.remark,
                    yscount = yscount,
                });
            }
            return Json(arr);
        }

        public ActionResult Add(int? id)
        {
            Model.Keshi n = new Model.Keshi();
            if (id != null)
            {
                n = dal.GetModel(id.Value);
            }
            return View(n);
        }

        
        [HttpPost]
        [ValidateAntiForgeryToken]
        public ActionResult Add(Model.Keshi m)
        {
            try
            {
                Model.Admin admin = base.GetLoginAdmin();
                if (admin == null)
                {
                    throw new Exception("请重新登录。");
                }
                string ip = HttpContext.UserHostAddress().ToString();
                if (m.id == 0)
                {
                    dal.Add(m);
                    mongodbhelper.AddSystemLog(new Model.Systemlog()
                    {
                        blid = 0,
                        createtime = DateTime.Now,
                        ip = ip,
                        opdesc = $"新增科室【{m.ksname}】",
                        opname = admin.username,
                        optype = (int)Model.EnumDefined.SystemLogOPType.网页端操作,
                        wzid = 0
                    });
                    return Json(new { code = 0, msg = "新增成功！" });
                }
                else
                {
                    dal.Update(m);
                    mongodbhelper.AddSystemLog(new Model.Systemlog()
                    {
                        blid = 0,
                        createtime = DateTime.Now,
                        ip = ip,
                        opdesc = $"编辑科室【{m.ksname}】",
                        opname = admin.username,
                        optype = (int)Model.EnumDefined.SystemLogOPType.网页端操作,
                        wzid = 0
                    });
                    return Json(new { code = 0, msg = "编辑成功！" });
                }
            }
            catch (Exception ex)
            {
                return Json(new { code = 1, msg = $"出错：{ex.Message}" });
            }
        }

        public ActionResult Delete(string ids)
        {
            try
            {
                string ip = HttpContext.UserHostAddress().ToString() ;
                Model.Admin admin = base.GetLoginAdmin();
                if (admin==null)
                {
                    throw new Exception("请重新登录");
                }
                int success = 0;
                string[] ss = ids.Split(',');
                foreach (var item in ss)
                {
                    int x;
                    if (int.TryParse(item, out x))
                    {
                        Model.Keshi ks = dal.GetModel(x);
                        if (ks==null)
                        {
                            continue;
                        }
                        dal.Delete(x);
                        mongodbhelper.AddSystemLog(new Model.Systemlog()
                        {
                            blid = 0,
                            createtime = DateTime.Now,
                            ip = ip,
                            opdesc = $"删除科室【{ks.ksname}】",
                            opname = admin.username,
                            optype = (int)Model.EnumDefined.SystemLogOPType.网页端操作,
                            wzid = 0
                        });
                        success++;
                    }
                }
                return Json(new { code = 0, msg = "成功删除" + success + "条记录！" });
            }
            catch (Exception ex)
            {
                return Json(new { code = 1, msg = $"出错：{ex.Message}" });
            }
        }


        #region 科室数据导出
 
        /// <summary>
        /// 管理员导出
        /// </summary>
        /// <param name="key"></param>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <param name="downloadFileName"></param>
        /// <returns></returns>
        public ActionResult ExportksDatas(string downloadFileName)
        {
            System.Data.DataTable table = new System.Data.DataTable();
            List<Model.Keshi> list = new List<Model.Keshi>();
            list = dal.GetListArrayks();

            table.Columns.Add("科室名称");
            table.Columns.Add("备注");





            if (list.Count > 0)
            {
                foreach (var a in list)
                {
                    System.Data.DataRow dr = table.NewRow();
                    dr["科室名称"] = a.ksname;
                    dr["备注"] = a.remark;



                    table.Rows.Add(dr);
                }
                
            }
            System.IO.MemoryStream ms = Util.DataTableRenderToExcel.RenderDataTableToExcel(table) as System.IO.MemoryStream;
            string filePath = "科室列表.xls";
            new FileExtensionContentTypeProvider().TryGetContentType(filePath, out string contentType);
            return File(ms.ToArray(), contentType, filePath);

        }

        #endregion

    }







}

 

以前的在ASP.NET ASPX中导出XLS的代码：

            DataSet ds = dal.GetList("*", "createdate", "desc", anp.PageSize, anp.CurrentPageIndex, GetCond());
            DataTable table = ds.Tables0;

            // 填充資料（由讀者自行撰寫）

            // 產生 Excel 資料流。
            MemoryStream ms = DataTableRenderToExcel.RenderDataTableToExcel(table) as MemoryStream;
            //判断是否是IE，是则编码文件名，防止中文乱码
            HttpBrowserCapabilities bc = Request.Browser;
            string browser = bc.Browser.ToString();
            string filename = browser.ToLower().Contains("ie") ? HttpUtility.UrlEncode(System.Text.UTF8Encoding.UTF8.GetBytes("学员信息表")) : "学员信息表";
            // 設定強制下載標頭。
            Response.AddHeader("Content-Disposition", string.Format("attachment; filename=" + filename + ".xls"));
            // 輸出檔案。
            Response.BinaryWrite(ms.ToArray());

            ms.Close();
            ms.Dispose();