.NET中MVC使用NPOI快速的导出excel表

朴灿烈づ我的快乐病毒、 2022-05-19 20:57 201阅读 0赞

效果图：

![70][]

@*前台代码*@
    @{
        ViewBag.Title = "Index";
    }
    
    <a href="~/home/toexcel">导出Excel表</a>

//后台代码
    
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Web;
    using System.Web.Mvc;
    using NPOI.HSSF.UserModel;
    using NPOI.SS.UserModel;
    using System.IO;
    
    namespace MvcApplication1.Controllers
    {
        public class HomeController : Controller
        {
            //
            // GET: /Home/
    
            public ActionResult Index()
            {
                return View();
            }
    
            /// <summary>
            /// 表头的样式
            /// </summary>
            /// <param name="workbook"></param>
            /// <param name="sheet"></param>
            public void CreateHeadStyle(HSSFWorkbook workbook, HSSFSheet sheet)
            {
                //创建行
                HSSFRow row = sheet.CreateRow(0) as HSSFRow;
                //创建列
                var cell0 = row.CreateCell(0);
                var cell1 = row.CreateCell(1);
                var cell2 = row.CreateCell(2);
                cell0.SetCellValue("营业部");
                cell1.SetCellValue("当日预收");
                cell2.SetCellValue("昨日预收");
                //创建列的样式
                HSSFCellStyle cstyle = workbook.CreateCellStyle() as HSSFCellStyle;
                //设置垂直居中
                cstyle.VerticalAlignment = VerticalAlignment.CENTER;
                //设置水平居中
                cstyle.Alignment = HorizontalAlignment.CENTER;
                //合并单元格
                sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 2, 0, 0));
                sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 2, 1, 1));
                sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 2, 2, 2));
    
                cell0.CellStyle = cstyle;
                cell1.CellStyle = cstyle;
                cell2.CellStyle = cstyle;
    
                var cell3 = row.CreateCell(3);
                cell3.SetCellValue("8月关键经营指标");
    
                cstyle = workbook.CreateCellStyle() as HSSFCellStyle;
                cstyle.VerticalAlignment = VerticalAlignment.CENTER;
                cstyle.Alignment = HorizontalAlignment.CENTER;
                sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 3, 6));
    
                cell3.CellStyle = cstyle;
    
                //---------------------创建行------------------------
                HSSFRow row1 = sheet.CreateRow(1) as HSSFRow;
                var cell4 = row1.CreateCell(3);
                var cell5 = row1.CreateCell(4);
                cell4.SetCellValue("在职人数");
                cell5.SetCellValue("新增人数");
    
                cstyle = workbook.CreateCellStyle() as HSSFCellStyle;
                cstyle.VerticalAlignment = VerticalAlignment.CENTER;
                cstyle.Alignment = HorizontalAlignment.CENTER;
                sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 2, 3, 3));
                sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 2, 4, 4));
    
                cell4.CellStyle = cstyle;
                cell5.CellStyle = cstyle;
    
                var cell6 = row1.CreateCell(5);
                cell6.SetCellValue("以实际销售为准");
    
                cstyle = workbook.CreateCellStyle() as HSSFCellStyle;
                cstyle.VerticalAlignment = VerticalAlignment.CENTER;
                cstyle.Alignment = HorizontalAlignment.CENTER;
                sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 1, 5, 6));
    
                cell6.CellStyle = cstyle;
                //---------------------创建行------------------------
                HSSFRow row2 = sheet.CreateRow(2) as HSSFRow;
    
                var cell7 = row2.CreateCell(5);
                var cell8 = row2.CreateCell(6);
                cell7.SetCellValue("出单人力");
                cell8.SetCellValue("出单率");
    
                cstyle = workbook.CreateCellStyle() as HSSFCellStyle;
                cstyle.VerticalAlignment = VerticalAlignment.CENTER;
                cstyle.Alignment = HorizontalAlignment.CENTER;
                sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(2, 2, 5, 5));
                sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(2, 2, 6, 6));
    
                cell7.CellStyle = cstyle;
                cell8.CellStyle = cstyle;
            }
    
            /// <summary>
            /// 导出excel表
            /// </summary>
            public void ToExcel()
            {
                //创建一个Excel表
                HSSFWorkbook workbook = new HSSFWorkbook();
    
                HSSFSheet sheet = workbook.CreateSheet("2017年8月经营指标") as HSSFSheet;
    
                CreateHeadStyle(workbook, sheet);
    
                Response.AddHeader("Content-Disposition", "attachment;filename=2017年8月经营指标报表.xls");
    
                EntityDBEntities1 en = new EntityDBEntities1();
                //查询数据库
                List<employ> elist = en.employ.ToList();
                //遍历集合
                for (int i = 0; i < elist.Count; i++)
                {
                    //创建行
                    HSSFRow row = sheet.CreateRow(i+3) as HSSFRow;
                    //创建列
                    row.CreateCell(0).SetCellValue(elist[i].department);
                    row.CreateCell(1).SetCellValue(elist[i].todaynum.ToString());
                    row.CreateCell(2).SetCellValue(elist[i].yestoday.ToString());
                    row.CreateCell(3).SetCellValue(elist[i].peoplenum.ToString());
                    row.CreateCell(4).SetCellValue(elist[i].addpeoplenum.ToString());
                    row.CreateCell(5).SetCellValue(elist[i].people.ToString());
                    row.CreateCell(6).SetCellValue(elist[i].frank.ToString());
    
                }
    
                //内存流
                MemoryStream ms = new MemoryStream();
    
                workbook.Write(ms);
    
                Response.BinaryWrite(ms.ToArray());
            }
    
        }
    }

[70]: /images/20220520/cfe6703e43214dc391aa0bbe6624e47d.png