NPOI的官方教程：

此次项目中使用的是NPOI的1.25版本，项目是MVC3，在这里只贴上controller部分的代码：

1 using NPOI.HSSF.UserModel;2 using NPOI.SS.UserModel;

用户可以在网站上订票，有些景区不具备网络验票的条件，需要我们将订票用户以传真的方式发送给景区。故做此导出功能：分为导出单个景区和一次导出全部景区。

1  /// 
  2         /// //导出excel  3         /// 
  4         /// 联盟ID  5         /// 游玩时间  6         /// 
       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
     
       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
      
        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
       
         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[]的内容放入DataTable105                             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         }

截图：