MVC3 使用NPOI导出excel
NPOI的官方教程：http://tonyqus.sinaapp.com/tutorial
此次项目中使用的是NPOI的1.25版本，项目是MVC3，在这里只贴上controller部分的代码：
1 using NPOI.HSSF.UserModel; 2 using NPOI.SS.UserModel;
用户可以在网站上订票，有些景区不具备网络验票的条件，需要我们将订票用户以传真的方式发送给景区。故做此导出功能：分为导出单个景区和一次导出全部景区。
1 /// <summary> 2 /// //导出excel 3 /// </summary> 4 /// <param name="id">联盟ID</param> 5 /// <param name="param">游玩时间</param> 6 /// <returns></returns> 7 public ActionResult CreateExcel(string id,string param) 8 { 9 try 10 { 11 if (id == "全部") 12 { 13 string[] s = param.Split('-'); 14 DateTime dt = new DateTime(int.Parse(s[0]), int.Parse(s[1]), int.Parse(s[2])); 15 List<TSceneryLeagueExportFax> list = tsceneryLeagueBll.getFaxScenery(dt); 16 17 DataSet tempDS = new DataSet(); 18 foreach (TSceneryLeagueExportFax tslef in list) 19 { 20 if (tslef.SceneryBookNum == 0) 21 continue; 22 List<TSceneryLeagueExportFax> sceneryleague = tsceneryLeagueBll.getFaxSceneryDetail(tslef.LMID, dt); 23 DataTable dtabel = new DataTable(); 24 dtabel.TableName = sceneryleague[0].SceneryName; 25 dtabel.Columns.Add("预订人"); 26 //dtabel.Columns.Add("电话"); 27 dtabel.Columns.Add("验证码"); 28 dtabel.Columns.Add("票种"); 29 dtabel.Columns.Add("张数"); 30 dtabel.Columns.Add("单价"); 31 dtabel.Columns.Add("总价"); 32 dtabel.Columns.Add("游玩时间"); 33 34 foreach (TSceneryLeagueExportFax tlef in sceneryleague) 35 { 36 if (tlef.TicketsNum == 0) 37 continue; 38 IList TempList = new ArrayList(); 39 TempList.Add(tlef.TakeTicketPerson); 40 //TempList.Add(tlef.PhoneCode); 41 TempList.Add(tlef.VerifyCode); 42 TempList.Add(tlef.TicketName); 43 TempList.Add(tlef.TicketsNum); 44 TempList.Add(tlef.DiscountPrice); 45 TempList.Add(tlef.allMonery); 46 TempList.Add(tlef.DateOfVisit.ToString("yyyy-MM-dd")); 47 48 object[] itm = new object[7]; 49 //遍历ArrayList向object[]里放数据 50 for (int j = 0; j < TempList.Count; j++) 51 { 52 53 itm.SetValue(TempList[j], j); 54 } 55 //将object[]的内容放入DataTable 56 dtabel.LoadDataRow(itm, true); 57 58 } 59 tempDS.Tables.Add(dtabel); 60 } 61 return File(ExportExecel(tempDS), "application/vnd.ms-excel", "全部景区联盟传真" + param + ".xls"); 62 } 63 else 64 { 65 string[] s = param.Split('-'); 66 DateTime dt = new DateTime(int.Parse(s[0]), int.Parse(s[1]), int.Parse(s[2])); 67 List<TSceneryLeagueExportFax> list = tsceneryLeagueBll.getFaxSceneryDetail(int.Parse(id), dt); 68 TSceneryLeague tsigle = tsceneryLeagueBll.GetModel(int.Parse(id));//用于获取景区名称，在导出文件时显示 69 DataSet tempDS = new DataSet(); 70 if (list.Count > 0) 71 { 72 DataTable dtabel = new DataTable(); 73 dtabel.TableName = list[0].SceneryName; 74 dtabel.Columns.Add("预订人"); 75 //dtabel.Columns.Add("电话"); 76 dtabel.Columns.Add("验证码"); 77 dtabel.Columns.Add("票种"); 78 dtabel.Columns.Add("张数"); 79 dtabel.Columns.Add("单价"); 80 dtabel.Columns.Add("总价"); 81 dtabel.Columns.Add("游玩时间"); 82 83 foreach (TSceneryLeagueExportFax tlef in list) 84 { 85 if (tlef.TicketsNum == 0) 86 continue; 87 IList TempList = new ArrayList(); 88 TempList.Add(tlef.TakeTicketPerson); 89 //TempList.Add(tlef.PhoneCode); 90 TempList.Add(tlef.VerifyCode); 91 TempList.Add(tlef.TicketName); 92 TempList.Add(tlef.TicketsNum); 93 TempList.Add(tlef.DiscountPrice); 94 TempList.Add(tlef.allMonery); 95 TempList.Add(tlef.DateOfVisit.ToString("yyyy-MM-dd")); 96 97 object[] itm = new object[7]; 98 //遍历ArrayList向object[]里放数据 99 for (int j = 0; j < TempList.Count; j++) 100 { 101 102 itm.SetValue(TempList[j], j); 103 } 104 //将object[]的内容放入DataTable 105 dtabel.LoadDataRow(itm, true); 106 107 } 108 tempDS.Tables.Add(dtabel); 109 110 } 111 112 113 114 return File(ExportExecel(tempDS), "application/vnd.ms-excel", tsigle.SceneryName+param+".xls"); 115 } 116 } 117 catch (Exception ex) 118 { 119 120 throw ex; 121 } 122 }
在上边的方法中调用如下的一个方法，此方法可以略做修改用于其他功能的导出
1 public byte[] ExportExecel(DataSet tempDs) 2 { 3 4 HSSFWorkbook book = new HSSFWorkbook(); 5 6 //普通单元格样式 7 ICellStyle cellstyle = book.CreateCellStyle(); 8 cellstyle.Alignment = HorizontalAlignment.CENTER; //水平居中 9 cellstyle.VerticalAlignment = VerticalAlignment.CENTER;//垂直居中 10 cellstyle.WrapText = true; //自动换行 11 IFont cellfont = book.CreateFont(); 12 cellfont.FontHeightInPoints = 11; //11号字体 13 cellstyle.SetFont(cellfont); 14 15 //头部单元格样式 16 ICellStyle cellheadstyle = book.CreateCellStyle(); 17 cellheadstyle.Alignment = HorizontalAlignment.CENTER; 18 cellheadstyle.VerticalAlignment = VerticalAlignment.CENTER; 19 IFont cellheadfont = book.CreateFont(); 20 cellheadfont.FontHeightInPoints = 11; 21 cellheadfont.Boldweight =(short)FontBoldWeight.BOLD; //字体加粗 22 cellheadstyle.SetFont(cellheadfont); 23 24 25 //标题单元格样式 26 ICellStyle titlestyle = book.CreateCellStyle(); 27 titlestyle.Alignment = HorizontalAlignment.CENTER; 28 titlestyle.VerticalAlignment = VerticalAlignment.CENTER; 29 IFont titlefont = book.CreateFont(); 30 titlefont.FontHeightInPoints = 14; 31 titlefont.Boldweight = (short)FontBoldWeight.BOLD; //字体加粗 32 titlestyle.SetFont(titlefont); 33 34 35 int count = tempDs.Tables.Count; 36 for (int i = 0; i < count; i++) 37 { 38 DataTable dt = tempDs.Tables[i]; 39 ISheet sheet = book.CreateSheet(dt.TableName); 40 sheet.IsPrintGridlines = true; //打印时显示网格线 41 sheet.DisplayGridlines = true;//查看时显示网格线 42 //sheet.DefaultRowHeightInPoints = 25; 这种默认设置不起作用 43 sheet.SetColumnWidth(0, 20 * 256);//预订人宽度 44 sheet.SetColumnWidth(2, 25 * 256);//票种宽度 45 sheet.SetColumnWidth(6, 13 * 256);//游玩时间宽度 46 47 int rowIndex = 3; 48 int colIndex = 0; 49 50 51 52 IRow row = sheet.CreateRow(0); 53 row.HeightInPoints = 30; 54 ICell title = row.CreateCell(0); 55 string titlevalue = dt.TableName + dt.Rows[0]["游玩时间"]+"订票详情"; 56 title.SetCellValue(titlevalue); 57 title.CellStyle = titlestyle; 58 sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0,0,0,6)); 59 60 row = sheet.CreateRow(1); 61 row.HeightInPoints = 25; 62 ICell subtitle = row.CreateCell(0); 63 string subtitlevalue = "来自：南北游旅行网 电话：0311-83052118 传真：0311-83058268 移动客服：18633827863"; 64 subtitle.SetCellValue(subtitlevalue); 65 subtitle.CellStyle = cellstyle; 66 sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 1, 0, 6)); 67 68 69 70 //设置表头 71 row = sheet.CreateRow(2); 72 row.HeightInPoints = 25; 73 foreach (DataColumn col in dt.Columns) 74 { 75 76 ICell cell = row.CreateCell(colIndex); 77 cell.SetCellValue(col.ColumnName); 78 cell.CellStyle = cellheadstyle; 79 80 colIndex++; 81 } 82 83 //导入数据行 84 foreach (DataRow rows in dt.Rows) 85 { 86 87 colIndex = 0; 88 row = sheet.CreateRow(rowIndex); 89 string TicketType = rows["票种"].ToString(); 90 if (TicketType.Length <= 12) 91 { 92 row.HeightInPoints = 25; 93 } 94 else 95 { 96 row.HeightInPoints = 50; 97 } 98 foreach (DataColumn col in dt.Columns) 99 { 100 ICell cell = row.CreateCell(colIndex); 101 cell.SetCellValue(rows[col.ColumnName].ToString()); 102 cell.CellStyle = cellstyle; 103 colIndex++; 104 } 105 rowIndex++; 106 } 107 108 } 109 110 111 // 写入到客户端 112 System.IO.MemoryStream ms = new System.IO.MemoryStream(); 113 book.Write(ms); 114 byte[] b = ms.ToArray(); 115 book = null; 116 ms.Close(); 117 ms.Dispose(); 118 return b; 119 }
截图：
