ASP.NETCore -----导出Excel文件并下载

今天药忘吃喽~ 2023-06-05 11:16 6阅读 0赞

本事例分为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操作Excel\ASPNETCoreExcel\Template";//模板地址
                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>

[代码下载][Link 1]

转载于:https://www.cnblogs.com/macT/p/11611349.html

[Link 1]: https://download.csdn.net/download/qq_37977607/11830142