NPOI导出Excel(ASP .NET MVC)

　　NPOI导出Excel非常方便，可以自定义导出的Excel样式，接下来我将对自己做过的一个任务的代码进行详细分析。

景区APP管理平台，监控管理的功能是显示某个景区在某个时间段内用某个打印机打印门票的数量，这个任务是将使用情况打印成Excel表格。

　　2.相关代码如下：

view页面调用方法：

1 <div class="col-md-1 col-sm-1 clearfix " style="text-align: center; overflow: hidden">2 　　<input type="submit" value="Excel" class="btn bg-purple" formaction="@Url.Action("ListToExcel")"/>3 </div>

Controller代码

 1 　　　　　/// <summary> 2     /// 导出总打印数据到Excel表 3     /// 郑鑫2016年5月12日添加 4     /// </summary> 5     /// <param name="printer">条件</param> 6     public void ListToExcel(MonitorPrinterViewModel printer) 7     { 8       //获取Datatable表格 9       var printList = _monitorPrinterServices.GetListToExcel(printer, (int)AuditEnum.SortTypeEnum.SceneryName); 10       //如果无数据，则跳出提示 11       if (printList == null || printList.Rows.Count == 0) 12       { 13         Response.Write("<script>parent.Messenger.options = {extraClasses: 'messenger-fixed messenger-theme-future messenger-on-bottom messenger-on-right' };" + 14         "parent.Messenger().post({message: '暂无数据，无法导出！', type: 'error',showCloseButton: true});" + 15         "location='" + Url.Action("Index") + "'+'?sceneryName=" + printer.SceneryName + "'+'&printerName=" + printer.PrinterName + "'+'&startTime=" + printer.StartTime + "'+'&endTime=" + printer.EndTime + "'+'&PageIndex=1';</script>"); 16         return; 17       } 18  19       #region 新建表格 20       //获取景区名，为空则返回“所有景区” 21       string printName = string.Empty; 22       if (!string.IsNullOrEmpty(printer.SceneryName)) 23       { 24         printName = "" + printer.SceneryName + ""; 25       } 26       if (string.IsNullOrEmpty(printer.SceneryName)) 27       { 28         printName = "所有景区"; 29       } 30       HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); 31       ISheet sheet = book.CreateSheet(""+printName+""); 32       //第一行 33       NPOI.SS.UserModel.IRow row = sheet.CreateRow(0); 34       row.CreateCell(0).SetCellValue("" + printName + ""); 35       //空列，以便后面合并单元格 36       row.CreateCell(1).SetCellValue(""); 37       row.CreateCell(2).SetCellValue(""); 38       row.CreateCell(3).SetCellValue(""); 39       //第二行，获取时间段 40       DateTime startDate = printList.Rows[0]["StartTime"].PackDateTime(); 41       DateTime endDate = printList.Rows[0]["EndTime"].PackDateTime(); 42       for (int i = 0; i < printList.Rows.Count; i++) 43       { 44         DateTime dtStart = printList.Rows[i]["StartTime"].PackDateTime(); 45         DateTime dtEnd = printList.Rows[i]["EndTime"].PackDateTime(); 46         if (startDate > dtStart) 47         { 48           startDate = dtStart; 49         } 50         if (endDate < dtEnd) 51         { 52           endDate = dtEnd; 53         } 54       } 55       NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(1); 56       row2.CreateCell(0).SetCellValue("" + startDate.ToString("yyyy-MM-dd") + "" + "~" + "" + endDate.ToString("yyyy-MM-dd") + "" + "打印详情"); 57       row2.CreateCell(1).SetCellValue(""); 58       row2.CreateCell(2).SetCellValue(""); 59       row2.CreateCell(3).SetCellValue(""); 60  61       //数据列名 62       NPOI.SS.UserModel.IRow row3 = sheet.CreateRow(2); 63       row3.CreateCell(0).SetCellValue("所属景区"); 64       row3.CreateCell(1).SetCellValue("蓝牙打印机名称"); 65       row3.CreateCell(2).SetCellValue("使用时段"); 66       row3.CreateCell(3).SetCellValue("打印数量"); 67       int count = 0; 68       //将Datatable数据加入表格 69       for (int i = 0; i < printList.Rows.Count; i++) 70       { 71         count += printList.Rows[i]["TotalTimes"].PackInt(); 72         NPOI.SS.UserModel.IRow rows = sheet.CreateRow(i + 3); 73         rows.CreateCell(0).SetCellValue("" + printList.Rows[i]["MPSceneryName"].PackString() + ""); 74         rows.CreateCell(1).SetCellValue("" + printList.Rows[i]["MPPrinterName"].PackString() + ""); 75         rows.CreateCell(2).SetCellValue("" + printList.Rows[i]["StartTime"].PackDateTime().ToString("yyyy-MM-dd") + "" + "~" + "" + printList.Rows[i]["EndTime"].PackDateTime().ToString("yyyy-MM-dd") + ""); 76         rows.CreateCell(3).SetCellValue("" + printList.Rows[i]["TotalTimes"].PackInt() + ""); 77         //加边框 78         rows.Cells[0].CellStyle = loopStyle(book); 79         rows.Cells[1].CellStyle = loopStyle(book); 80         rows.Cells[2].CellStyle = loopStyle(book); 81         rows.Cells[3].CellStyle = loopStyle(book); 82         //合并相同景区列 83         if (i > 0) 84         { 85           if (printList.Rows[i]["MPSceneryName"].PackString() == printList.Rows[i - 1]["MPSceneryName"].PackString()) 86           { 87             sheet.AddMergedRegion(new CellRangeAddress(i+2, i+3, 0, 0)); 88           } 89         } 90       } 91       //最后一行，统计总打印次数 92       NPOI.SS.UserModel.IRow rowLast = sheet.CreateRow(printList.Rows.Count + 3); 93       rowLast.CreateCell(0).SetCellValue("总打印次数"); 94       rowLast.CreateCell(1).SetCellValue(""); 95       rowLast.CreateCell(2).SetCellValue(""); 96       rowLast.CreateCell(3).SetCellValue("" + count + ""); 97       //设置样式 98       for (int i = 0; i < 4; i++) 99       {100         row.Cells[i].CellStyle = titleStyle(book);101         row2.Cells[i].CellStyle = titleStyle(book);102         row3.Cells[i].CellStyle = titleStyle(book);103         row3.Cells[i].CellStyle.FillForegroundColor = HSSFColor.LightTurquoise.Index;104         rowLast.Cells[i].CellStyle = titleStyle(book);105         sheet.SetColumnWidth(i, 35 * 256);106       }107       108       //合并单元格109       sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 3));110       sheet.AddMergedRegion(new CellRangeAddress(1, 1, 0, 3));111       sheet.AddMergedRegion(new CellRangeAddress(printList.Rows.Count + 3, printList.Rows.Count + 3, 0, 2));112       #endregion113 114       // 写入到客户端 115       System.IO.MemoryStream ms = new System.IO.MemoryStream();116       book.Write(ms);117       Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}-{1}导出.xls", printName, DateTime.Now.ToString("yyyy-MM-dd")));118       Response.BinaryWrite(ms.ToArray());119       book = null;120       ms.Close();121       ms.Dispose();122     }

引用的Excel样式代码

 1     /// <summary> 2     /// Excel标题样式，居中，加粗，背景颜色，边框 3     /// </summary> 4     /// <param name="book"></param> 5     /// <returns></returns> 6     public ICellStyle titleStyle(HSSFWorkbook book) 7     { 8       ICellStyle style = book.CreateCellStyle(); 9       style.Alignment = HorizontalAlignment.Center;10       IFont font = book.CreateFont();11       font.Boldweight = short.MaxValue;12       style.SetFont(font);13       style.BorderBottom = BorderStyle.Thin;14       style.BorderLeft = BorderStyle.Thin;15       style.BorderRight = BorderStyle.Thin;16       style.BorderTop = BorderStyle.Thin;17       style.FillForegroundColor = 42;18       style.FillPattern = FillPattern.SolidForeground;19       return style;20     }21 22     /// <summary>23     /// Excel表格数据内容，加边框24     /// </summary>25     /// <param name="book"></param>26     /// <returns></returns>27     public ICellStyle loopStyle(HSSFWorkbook book)28     {29       ICellStyle style = book.CreateCellStyle();30       style.BorderBottom = BorderStyle.Thin;31       style.BorderLeft = BorderStyle.Thin;32       style.BorderRight = BorderStyle.Thin;33       style.BorderTop = BorderStyle.Thin;34       style.Alignment = HorizontalAlignment.Center;35       style.Alignment = HorizontalAlignment.Center;36       return style;37     }

　　3.生成的Excel效果图