本事例分为nopi(安装DotNetCore.NPOI)下载和EPPlus(EPPlus.Core.dll)下载，其中npoi下载演示的是根据执行的模板进行数据下载
npoi帮助类NpoiExcelUtility
using NPOI.HSSF.UserModel; using NPOI.SS.UserModel; using System; using System.Collections.Generic; using System.Data; using System.IO; using System.Linq; using System.Threading.Tasks; namespace ASPNETCoreExcel { public class NpoiExcelUtility { private string _xlsPath = string.Empty; private HSSFWorkbook _workBook = null; /// <summary> /// 构造函数 /// </summary> /// <param name="xlsPath">xls保存路径</param> /// <param name="TempletFileName">xls模板路径</param> public NpoiExcelUtility(string xlsPath, string TempletFileName) { _xlsPath = this.CheckFilePath(xlsPath); FileStream file = new FileStream(TempletFileName, FileMode.Open, FileAccess.Read); _workBook = new HSSFWorkbook(file); } /// <summary> /// 将DataTable保存到sheet里 /// </summary> /// <param name="dt"></param> /// <param name="sheet"></param> private void DataTableToExcel(DataTable dt, ISheet sheet) { ICellStyle style = _workBook.CreateCellStyle(); style.Alignment = HorizontalAlignment.Left; style.VerticalAlignment = VerticalAlignment.Center; ICellStyle colStyle = _workBook.CreateCellStyle(); colStyle.Alignment = HorizontalAlignment.Left; colStyle.VerticalAlignment = VerticalAlignment.Center; IFont font = _workBook.CreateFont(); font.Color = NPOI.HSSF.Util.HSSFColor.LightBlue.Index; colStyle.SetFont(font); //列名 //IRow row = sheet.CreateRow(0); //for (int i = 0; i < dt.Columns.Count; i++) //{ // sheet.SetDefaultColumnStyle(i, style); // ICell cell = row.CreateCell(i); // cell.SetCellValue(dt.Columns[i].ToString()); // cell.CellStyle = colStyle; //} //内容 var headerRow = (HSSFRow)sheet.GetRow(0); for (int i = 1; i <= dt.Rows.Count; i++) { IRow row = sheet.CreateRow(i + 1); row.Height = 50 * 20; ICell numcell = row.CreateCell(0); numcell.SetCellValue(i); for (int j = 0; j < dt.Columns.Count; j++) { object obj = dt.Rows[i - 1][j]; if (obj != null) { string ColumnName = dt.Columns[j].ToString(); var _Column = headerRow.Cells.Find(t => !string.IsNullOrEmpty(t.StringCellValue) && t.ToString().ToLower() == ColumnName.ToLower()); //ICell cell = row.CreateCell(j + 1); if (_Column != null) { ICell cell = row.CreateCell(_Column.ColumnIndex); if (obj is double || obj is float || obj is int || obj is long || obj is decimal) { cell.SetCellValue(Convert.ToDouble(obj)); } else if (obj is bool) { cell.SetCellValue((bool)obj); } else { cell.SetCellValue(obj.ToString()); } } } } } } /// <summary> /// 保存Excel /// </summary> public void SaveExcel() { FileStream file = new FileStream(_xlsPath, FileMode.Create); _workBook.Write(file); file.Close(); } /// <summary> /// 创建Sheet /// </summary> /// <param name="sheetName">sheet名称</param> /// <param name="tbl">DataTable数据表，当行数大于65536时，自动分割成几个sheet，sheet名称为sheetName_i</param> public void CreatExcelSheet(string sheetName, DataTable tbl) { string sName = this.CheckSheetName(sheetName); int rowMax = 65535; int intNum = tbl.Rows.Count / rowMax; int remainder = tbl.Rows.Count % rowMax; for (int i = 0; i < intNum; i++) { DataTable subTbl = tbl.Clone(); for (int j = 0; j < 65535; j++) { int rowIndex = i * rowMax + j; subTbl.Rows.Add(tbl.Rows[rowIndex].ItemArray); } string subSheet = sName + "_" + (i + 1); //ISheet sheet = _workBook.CreateSheet(subSheet); ISheet sheet = _workBook.GetSheetAt(0); this.DataTableToExcel(subTbl, sheet); } if (remainder > 0) { DataTable subTbl = tbl.Clone(); for (int j = 0; j < remainder; j++) { int rowIndex = intNum * rowMax + j; subTbl.Rows.Add(tbl.Rows[rowIndex].ItemArray); } string subSheet = sName + "_" + (intNum + 1); if (intNum < 1) { subSheet = sName; } //ISheet sheet = _workBook.CreateSheet(subSheet); ISheet sheet = _workBook.GetSheetAt(0); this.DataTableToExcel(subTbl, sheet); } } /// <summary> /// 检查sheet名称是否合法，并去掉不合法字符 /// </summary> /// <param name="sheetName"></param> private string CheckSheetName(string sheetName) { string rlt = sheetName; string[] illegalChars = { "*", "?", """, @"", "/" }; for (int i = 0; i < illegalChars.Length; i++) { rlt = rlt.Replace(illegalChars[i], ""); } return rlt; } /// <summary> /// 检查xls路径是否合法，并去掉不合法字符 /// </summary> /// <param name="filePath"></param> private string CheckFilePath(string filePath) { string dir = Path.GetDirectoryName(filePath); string fileName = Path.GetFileNameWithoutExtension(filePath); string ext = Path.GetExtension(filePath); string[] illegalChars = { ":", "*", "?", """, "<", ">", "|", @"", "/" }; for (int i = 0; i < illegalChars.Length; i++) { fileName = fileName.Replace(illegalChars[i], ""); } string rlt = Path.Combine(dir, fileName + ext); return rlt; } } }
调用
using System; using System.Collections.Generic; using System.Diagnostics; using System.Linq; using System.Threading.Tasks; using Microsoft.AspNetCore.Mvc; using ASPNETCoreExcel.Models; using Microsoft.AspNetCore.Hosting; using System.IO; using OfficeOpenXml; using System.Data; namespace ASPNETCoreExcel.Controllers { public class HomeController : Controller { private IHostingEnvironment _hostingEnvironment; public HomeController(IHostingEnvironment hostingEnvironment) { _hostingEnvironment = hostingEnvironment; } public IActionResult Index() { return View(); } public IActionResult About() { ViewData["Message"] = "Your application description page."; return View(); } public IActionResult Contact() { ViewData["Message"] = "Your contact page."; return View(); } public IActionResult Error() { return View(new ErrorViewModel { RequestId = Activity.Current?.Id ?? HttpContext.TraceIdentifier }); } /// <summary> /// 不根据指定模板进行导出excel下载(非npoi实现) /// </summary> /// <returns></returns> public IActionResult Export2() { string sWebRootFolder = _hostingEnvironment.WebRootPath; string sFileName = "部落.xlsx"; FileInfo file = new FileInfo(Path.Combine(sWebRootFolder, sFileName)); file.Delete(); using (ExcelPackage package = new ExcelPackage(file)) { // 添加worksheet ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("部落"); //添加头 worksheet.Cells[1, 1].Value = "ID"; worksheet.Cells[1, 2].Value = "Name"; worksheet.Cells[1, 3].Value = "Url"; //添加值 worksheet.Cells["A2"].Value = 1000; worksheet.Cells["B2"].Value = "For丨丶"; worksheet.Cells["C2"].Value = "https://buluo.qq.com/p/barindex.html?bid=310072"; worksheet.Cells["A3"].Value = 1001; worksheet.Cells["B3"].Value = "For丨丶Tomorrow"; worksheet.Cells["C3"].Value = "https://buluo.qq.com/p/barindex.html?bid=310072"; worksheet.Cells["C3"].Style.Font.Bold = true; package.Save(); } return File(sFileName, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", sFileName); } /// <summary> /// 根据指定模板进行导出excel下载(npoi实现) /// </summary> public IActionResult Export() { var TempletFilePath = @"D:谷歌下载ASPNETCore操作ExcelASPNETCoreExcelTemplate";//模板地址 var ExportFilePath = "D:";//导出后存放的地址 string TempletFileName = string.Format("{0}\调查表.xls", TempletFilePath); string ExportFileName = string.Format("{0}\调查表_{1}.xls", ExportFilePath, DateTime.Now.ToString("yyyy年MM月dd日hh时mm分ss秒")); var _NpoiExcelUtility = new NpoiExcelUtility(ExportFileName, TempletFileName); #region 测试数据 //创建DataTable DataTable dt = new DataTable("NewDt"); //创建自增长的ID列 DataColumn dc = dt.Columns.Add("id", Type.GetType("System.Int32")); dc.AutoIncrement = true; //自动增加 dc.AutoIncrementSeed = 1; //起始为1 dc.AutoIncrementStep = 1; //步长为1 dc.AllowDBNull = false; //非空 //创建其它列表 dt.Columns.Add(new DataColumn("Name", Type.GetType("System.String"))); //创建数据 DataRow dr = dt.NewRow(); dr["Name"] = "张三"; dt.Rows.Add(dr); dr = dt.NewRow(); dr["Name"] = "李四"; dt.Rows.Add(dr); dr = dt.NewRow(); dr["Name"] = "王五"; dt.Rows.Add(dr); #endregion _NpoiExcelUtility.CreatExcelSheet("调查表", dt); _NpoiExcelUtility.SaveExcel(); var stream = System.IO.File.OpenRead(ExportFileName);//excel表转换成流 return File(stream, "application/vnd.android.package-archive", Path.GetFileName(ExportFileName));//进行浏览器下载 } } }
前端界面
@{ Layout = null; } <!DOCTYPE html> <html> <head> <meta name="viewport" content="width=device-width" /> <title>Xlsx</title> </head> <body> <h2>ASP.NET Core 导出Excel xlsx 文件</h2> <a asp-action="Export">导出Excel</a> </body> </html>