NPOL导出Excel文件

1、在管理NuGet程序包中搜索 NPOL并下载

 

    引用文件添加NPOL.dll引用

 

 

2、以DataTable类型为参数传入

        /// <summary>
        /// 导出
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        public string TableToExcel(DataTable dt)
        {
            string urlPath = string.Empty;
            try
            {
                //下载文件名称
                string fileName = "导出" + "-" + DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xlsx"; // 文件名称
                urlPath = "upload/download/" + fileName; // 文件下载的URL地址，供给前台下载
                string filePath = HttpContext.Current.Server.MapPath("\\" + urlPath); // 文件路径
                // 1.检测是否存在文件夹，若不存在就建立个文件夹
                string directoryName = Path.GetDirectoryName(filePath);
                if (!Directory.Exists(directoryName))
                {
                    Directory.CreateDirectory(directoryName);
                }
                IWorkbook workbook;
                string fileExt = Path.GetExtension(fileName).ToLower();
                if (fileExt == ".xlsx") { workbook = new XSSFWorkbook(); } else if (fileExt == ".xls") { workbook = new HSSFWorkbook(); } else { workbook = null; }
                if (workbook == null) { return "-0"; }
                //sheet名称
                ISheet sheet = workbook.CreateSheet("导出信息");
                //表头  
                IRow row = sheet.CreateRow(0);
                row.CreateCell(0).SetCellValue("column1");
                row.CreateCell(1).SetCellValue("column2");
                row.CreateCell(2).SetCellValue("column3");
                row.CreateCell(3).SetCellValue("column4");
                row.CreateCell(4).SetCellValue("column5");
                //数据  
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    IRow row1 = sheet.CreateRow(i + 1);
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        ICell cell = row1.CreateCell(j);
                        cell.SetCellValue(dt.Rows[i][j].ToString() == "" ? "-" : dt.Rows[i][j].ToString());
                    }
                }
                // 4.生成文件
                FileStream filestr = new FileStream(filePath, FileMode.Create);
                workbook.Write(filestr);
                filestr.Close();
                // 5.返回下载路径
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return urlPath;
        }

 3、Controller中调用TableToExcel(),给前台返回下载路径

 

var Result= TableToExcel.Instace.ExcelStudentCollect(dt);

return Json(Result);