C#.net Core MVC 利用NPOI生成excel報表

對于服務器沒有裝office的情況下，我們知道可以用 NPOI 或 EPPlus，下面我們看下使用NPOI如何導出EXCEL資料。
當然，首先NUGET安裝 NPOI.
一、HTML

<div id="SearchReport" data-content="List" style="width:98%">   
    <div class="row">
        <div class="col-12 col-md-12">
            <div class="card card-solid">
                <div class="card-body">
                    <table id="reportlist" class="table table-bordered table-striped" style="height:90%;width:99%;">
                        <tbody>
                            <tr>
                                <td data-toggle="modal" data-target="#divReportDemo">報表示例1-NPOI</td>
                                <td width="50%" data-toggle="modal"></td>
                            </tr>
                        </tbody>
                    </table>
                </div>
            </div>
        </div>
    </div>
    <!-- Search Modals -->

    <div class="modal fade" id="divReportDemo" role="dialog" aria-labelledby="">
        <div class="modal-dialog" role="document">
            <div class="modal-content">
                <div class="modal-header">
                    <h4 class="modal-title">報表示例-NPOI</h4>
                    <button type="button" class="close" data-dismiss="modal" aria-label="Close"><span aria-hidden="true">&times;</span></button>

                </div>
                <div class="modal-body">
                    <div class="row">
                        <div class="col-sm-12 col-md-12">
                            <div class="form-group">
                                <label class="col-form-label">部門</label>
                               
                            </div>
                        </div>
                    </div>                   
                    <div class="row">
                        <div class="col-sm-12 col-md-12">
                            <div class="form-group">
                                <label class="col-form-label">日期 由 <span class="text-danger">*</span></label>
                                <input type="text" class="form-control" id="txtDisplayDateFr" value="2020-01-01" placeholder="YYYY-MM-DD">
                            </div>
                        </div>
                    </div>
                    <div class="row">
                        <div class="col-sm-12 col-md-12">
                            <div class="form-group">
                                <label class="col-form-label">日期 至 <span class="text-danger">*</span></label>
                                <input type="text" class="form-control" id="txtDisplayDateTo" value="2020-12-31" placeholder="YYYY-MM-DD">
                            </div>
                        </div>
                    </div>
                </div>
                <div class="modal-footer">
                    <button type="button" class="btn btn-default" data-dismiss="modal" aria-label="Close">關閉</button>
                    <button type="button" class="btn btn-default" aria-label="Print" id="btnNPOI">列印Excel</button>
                </div>
            </div>
        </div>
    </div>
</div>
<!--Loading div-->
<div class="modal" tabindex="100" id="divLoading" backdrop="static" keyboard="false" role="dialog" aria-hidden="true">
    <div class="modal-dialog modal-dialog-centered justify-content-center" role="document">
        <span class="fa fa-spinner fa-spin fa-3x text-info"></span><span class="text-info" style="margin-left:5px;font-size:larger;font-weight:bold">列印中...</span>
    </div>
</div>
<!--//.Loading div-->   
<script>
        $(function () {
            $("#btnNPOI").on("click", function (e) {
                exportToExcelNPOI();
            });
        });
        const sleep = (delay) => new Promise((resolve) => setTimeout(resolve, delay));//sleep function
        async function exportToExcelNPOI() {            
                const _displayDateFr = $("#txtDisplayDateFr").val();
                const _displayDateTo = $("#txtDisplayDateTo").val();
                let pattern = /^\d{4}-[0-1]{1}\d{1}-\d{2}$/;
            if (!pattern.test(_displayDateFr) || !pattern.test(_displayDateTo)) {
                    alert("請按格式輸入查詢日期");
                    return false;
            }
            if (_displayDateFr > _displayDateTo) {
                alert("查詢開始日期不可大于終止日期。");
                return false;
            }
            if (Math.abs(_displayDateFr.split("-")[0] - _displayDateTo.split("-")[0]) > 3) {
                alert("查詢年份不可超過三年！");
                return false;
            }
            await sleep(1000);//sleep for show loading           
            $("#divLoading").modal("show");//show loaing div           
            let arrData = { "deptName": $("#selDeptName").val(), "displayDateFr": _displayDateFr, "displayDateTo": _displayDateTo, "excelSheetName": "NoticeList" };
            let jsonStr = JSON.stringify(arrData);
            document.location.href = '@Html.Raw(Url.Action("ExportToExcelNPOI", "Report", new { searchCriteriaStr = "jsonstr" }))'.replace("jsonstr", jsonStr);
            await sleep(3000);//sleep for show loading
            $("#divLoading").modal("hide");            
        }
</script>

二、后臺 CONTROLLER CODE

 #region NPOI导出到Excel 
        //NPOI导出到Excel
        public ActionResult ExportToExcelNPOI(string searchCriteriaStr) //( ReportSearchModelNPOI searchCriteria)
        {
            var searchCriteria = JsonConvert.DeserializeObject<ReportSearchModelNPOI>(searchCriteriaStr);
            try
            {
                var notices = _iSample_NoticeRep.GetAllNotice()?.Where(x => (string.IsNullOrWhiteSpace(searchCriteria.deptName) ? true : (x.noticeDept == searchCriteria.deptName))
                                       && x.displayDate >= searchCriteria.displayDateFr && x.displayDate <= searchCriteria.displayDateTo).OrderBy(x=>x.displayDate).ToList();

                //创建Excel文件的对象 工作簿(调用NPOI文件)
                //注意：NPOI.XSSF.UserModel	XSSFWorkbook	.xlsx
                //NPOI.HSSF.UserModel HSSFWorkbook	.xls
                HSSFWorkbook excelBook = new HSSFWorkbook();//創建一個工作簿               
                ICellStyle style1 = excelBook.CreateCellStyle();//声明style1对象，设置Excel表格的样式
                ICellStyle style2 = excelBook.CreateCellStyle();
                ICellStyle style3 = excelBook.CreateCellStyle();
                IFont font = excelBook.CreateFont();
                font.IsBold = true;
                style1.SetFont(font);
                style1.Alignment = HorizontalAlignment.Center;//两端自动对齐（自动换行）
                style1.VerticalAlignment = VerticalAlignment.Center;
                style2.Alignment = HorizontalAlignment.Justify;
                style2.VerticalAlignment = VerticalAlignment.Center;
                style3.Alignment = HorizontalAlignment.Justify;
                style3.VerticalAlignment = VerticalAlignment.Center;

                //创建Excel工作表 
                ISheet sheet1 = excelBook.CreateSheet(searchCriteria.excelSheetName);//""--工作表名稱
                //設置column 的寬度
                sheet1.SetColumnWidth(0, 20 * 256);
                sheet1.SetColumnWidth(1, 30 * 256);
                sheet1.SetColumnWidth(2, 60 * 256);
                sheet1.SetColumnWidth(3, 20 * 256);
                sheet1.SetColumnWidth(4, 20 * 256);
                sheet1.SetColumnWidth(5, 20 * 256);
                IRow row1 = sheet1.CreateRow(0);  //给Sheet添加第一行的头部标题
                string[] dataColumns = { "部門", "事由", "內容", "通告日期", "日期由", "日期至" };
                //给标题的每一个单元格赋值
                Enumerable.Range(0, dataColumns.Length).ToList().ForEach(x => //x from 0 to length-1 =5
                    {
                        row1.CreateCell(x).SetCellValue(dataColumns[x]);
                        row1.GetCell(x).CellStyle = style1;//初始化设置样式
                    });

                //添加数据行：将表格数据逐步写入sheet1各个行中（也就是给每一个单元格赋值）
                if (notices.Count > 0)
                {
                    Enumerable.Range(0, notices.Count).ToList().ForEach(x =>
                       {
                           IRow rowTemp = sheet1.CreateRow(x + 1);
                           //rowTemp.Height = 62 * 20;
                           rowTemp.CreateCell(0).SetCellValue(notices[x].noticeDept);
                           rowTemp.CreateCell(1).SetCellValue(notices[x].title);
                           rowTemp.CreateCell(2).SetCellValue(notices[x].content);
                           rowTemp.CreateCell(3).SetCellValue(notices[x].displayDate.ToString("yyyy-MM-dd"));
                           rowTemp.CreateCell(4).SetCellValue(notices[x].fromDate.ToString("yyyy-MM-dd"));
                           rowTemp.CreateCell(5).SetCellValue(notices[x].toDate.ToString("yyyy-MM-dd"));
                           rowTemp.GetCell(0).CellStyle = style2;
                           rowTemp.GetCell(1).CellStyle = style2;
                           rowTemp.GetCell(2).CellStyle = style2;
                           rowTemp.GetCell(3).CellStyle = style2;
                           rowTemp.GetCell(4).CellStyle = style2;
                           rowTemp.GetCell(5).CellStyle = style2;
                       });
                }

                //输出的文件名称
                string fileName = searchCriteria.excelSheetName + DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss-ffff") + ".xls";
                //把Excel转为流，输出
                //创建文件流
                System.IO.MemoryStream bookStream = new System.IO.MemoryStream();
                //将工作薄写入文件流
                excelBook.Write(bookStream);

                //输出之前调用Seek（偏移量，游标位置) 把0位置指定为开始位置
                bookStream.Seek(0, System.IO.SeekOrigin.Begin);
                //Stream对象,文件类型,文件名称
                return File(bookStream, "application/vnd.ms-excel", fileName);
               
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

 public class ReportSearchModelNPOI
    {
      public  string deptName { get; set; }
      public   DateTime displayDateFr { get; set; }
      public  DateTime displayDateTo { get; set; }
      public string excelSheetName { get; set; }
    }