使用NPOI完成导出Excel文件

参考网址：http://blog.csdn.net/tiemufeng1122/article/details/6732588

能够实现点击按钮弹出下载框的功能，如图：

HTML代码：

 1  <button type="button" class="bk-margin-5 btn btn-success" onclick="printExcel()">Excel导出</button>
 2 
 3 <script type="text/javascript">
 4  function printExcel() {
 5             post("PrintExcel", null); //如果调用时有参数，则post("PrintExcel",{id:id});
 6         }
 7         function post(URL, PARAMS) {
 8             var temp = document.createElement("form");
 9             temp.action = URL;
10             temp.method = "post";
11             temp.style.display = "none";
12             for (var x in PARAMS) {
13                 var opt = document.createElement("textarea");
14                 opt.name = x;
15                 opt.value = PARAMS[x];  
16                 temp.appendChild(opt);
17             }
18             document.body.appendChild(temp);
19             temp.submit();
20             return temp;
21         }
22 </script>

后台代码：

  1  [HttpPost]//只接受post请求
  2         public void PrintExcel()
  3         {
  4             //接收参数 int id=Int32.Parse(Requert.Form["id"]);
  5             string trainShift = Session["trainshiftNum"].ToString(); 
  6             #region 转换成DataSet
  7             var query = from s in db.Students
  8                         where s.IsDelete == false && s.TrainShiftNum == trainShift
  9                         select new Stu
 10                         {
 11                             StudentDepartment = s.StudentDepartment,
 12                             StudentGender = s.StudentGender,
 13                             StuDentIdCard = s.StuDentIdCard,
 14                             StudentName = s.StudentName,
 15                             StudentNation = s.StudentNation,
 16                             StudentPosition = s.StudentPosition,
 17                             StudentUnit = s.StudentUnit,
 18                             TrainShiftNum = s.TrainShiftNum,
 19                             Remark = s.Remark
 20                         };
 21             DataTable dt = query.ToDataTable(rec => new object[] { query });//调用转换DataTable方法 22             #endregion
 23 
 24             HSSFWorkbook book = new HSSFWorkbook();
 25             MemoryStream ms = new MemoryStream();
 26             ISheet sheet = book.CreateSheet("sheet1");
 27 
 28             // 首列
 29             NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
 30             row.CreateCell(0).SetCellValue("序号");
 31             row.CreateCell(1).SetCellValue("所在单位");
 32             row.CreateCell(2).SetCellValue("所在部门");
 33             row.CreateCell(3).SetCellValue("姓名");
 34             row.CreateCell(4).SetCellValue("职务");
 35             row.CreateCell(5).SetCellValue("性别");
 36             row.CreateCell(6).SetCellValue("民族");
 37             row.CreateCell(7).SetCellValue("出生年月");
 38             row.CreateCell(8).SetCellValue("组");
 39             row.CreateCell(9).SetCellValue("备注");
 40             #region
 41             //// 第一列
 42             //NPOI.SS.UserModel.IRow row1 = sheet.CreateRow(1);
 43             //row1.CreateCell(0).SetCellValue("所在单位");
 44 
 45             //// 第二列
 46             //NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(2);
 47             //row2.CreateCell(0).SetCellValue("所在部门");
 48 
 49             //// 第三列
 50             //NPOI.SS.UserModel.IRow row3 = sheet.CreateRow(3);
 51             //row3.CreateCell(0).SetCellValue("姓名");
 52 
 53             //// 第四列
 54             //NPOI.SS.UserModel.IRow row4 = sheet.CreateRow(4);
 55             //row4.CreateCell(0).SetCellValue("职务");
 56 
 57             //// 第五列
 58             //NPOI.SS.UserModel.IRow row5 = sheet.CreateRow(5);
 59             //row5.CreateCell(0).SetCellValue("性别");
 60 
 61             //// 第六列
 62             //NPOI.SS.UserModel.IRow row6 = sheet.CreateRow(6);
 63             //row6.CreateCell(0).SetCellValue("民族");
 64 
 65             //// 第七列
 66             //NPOI.SS.UserModel.IRow row7 = sheet.CreateRow(7);
 67             //row7.CreateCell(0).SetCellValue("出生年月");
 68 
 69             //// 第八列
 70             //NPOI.SS.UserModel.IRow row8 = sheet.CreateRow(8);
 71             //row8.CreateCell(0).SetCellValue("组");
 72 
 73             //// 第九列
 74             //NPOI.SS.UserModel.IRow row9 = sheet.CreateRow(9);
 75             //row9.CreateCell(0).SetCellValue("备注");
 76             #endregion
 77             int rowIndex = 1;
 78 
 79             foreach (DataRow r in dt.Rows)
 80             {
 81                 NPOI.SS.UserModel.IRow dataRow = sheet.CreateRow(rowIndex);
 82                 string str = r["StuDentIdCard"].ToString().Substring(6, 8);
 83                 str = str.Substring(0, 4) + "-" + str.Substring(4, 2) + "-" + str.Substring(6, 2);
 84                 dataRow.CreateCell(0).SetCellValue(rowIndex.ToString());
 85                 dataRow.CreateCell(1).SetCellValue(r["StudentUnit"].ToString());
 86                 dataRow.CreateCell(2).SetCellValue(r["StudentDepartment"].ToString());
 87                 dataRow.CreateCell(3).SetCellValue(r["StudentName"].ToString());
 88                 dataRow.CreateCell(4).SetCellValue(r["StudentPosition"].ToString());
 89                 dataRow.CreateCell(5).SetCellValue(r["StudentGender"].ToString() == "1" ? "男" : "女");
 90                 dataRow.CreateCell(6).SetCellValue(r["StudentNation"].ToString());
 91                 dataRow.CreateCell(7).SetCellValue(str);
 92                 dataRow.CreateCell(8).SetCellValue(r["TrainShiftNum"].ToString());
 93                 dataRow.CreateCell(9).SetCellValue(r["Remark"].ToString());
 94                 rowIndex++;
 95             }
 96 
 97             // 写入到客户端  
 98             book.Write(ms);
 99             //Response.ClearContent();
100             //Response.Clear();
101             //Response.Buffer = true;
102             // 添加头信息，为"文件下载/另存为"对话框指定默认文件名   
103             Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", System.DateTime.Now.ToString("yyyymmddhhmmss")));
104             Response.Charset = "UTF-8";
105             Response.ContentEncoding = System.Text.Encoding.GetEncoding("UTF-8");
106             // 指定返回的是一个不能被客户端读取的流，必须被下载   
107             Response.ContentType = "application/ms-excel";
108             // 把文件流发送到客户端 
109             Response.BinaryWrite(ms.ToArray());
110             book = null;
111             ms.Close();
112             ms.Dispose();
113         }

转换DataTable的代码（在网上查到的完美转换法，注意的是怎么调用）：

 1  public static class ListToDataTable  //可以直接使用
 2     {
 3         public static DataTable ToDataTable<T>(this IEnumerable<T> varlist, CreateRowDelegate<T> fn)
 4         {
 5 
 6             DataTable dtReturn = new DataTable();
 7 
 8             // column names
 9 
10             PropertyInfo[] oProps = null;
11 
12             // Could add a check to verify that there is an element 0
13 
14             foreach (T rec in varlist)
15             {
16 
17                 // Use reflection to get property names, to create table, Only first time, others will follow
18 
19                 if (oProps == null)
20                 {
21 
22                     oProps = ((Type)rec.GetType()).GetProperties();
23 
24                     foreach (PropertyInfo pi in oProps)
25                     {
26 
27                         Type colType = pi.PropertyType; if ((colType.IsGenericType) && (colType.GetGenericTypeDefinition() == typeof(Nullable<>)))
28                         {
29 
30                             colType = colType.GetGenericArguments()[0];
31 
32                         }
33 
34                         dtReturn.Columns.Add(new DataColumn(pi.Name, colType));
35 
36                     }
37 
38                 }
39 
40                 DataRow dr = dtReturn.NewRow(); foreach (PropertyInfo pi in oProps)
41                 {
42 
43                     dr[pi.Name] = pi.GetValue(rec, null) == null ? DBNull.Value : pi.GetValue(rec, null);
44 
45                 }
46 
47                 dtReturn.Rows.Add(dr);
48 
49             }
50 
51             return (dtReturn);
52 
53         }
54 
55         public delegate object[] CreateRowDelegate<T>(T t);
56 
57     }

还有就是类的写法：

 1   /// <summary>
 2     /// 打印学员名单
 3     /// </summary>
 4     public class Stu
 5     {
 6         private string studentUnit;
 7 
 8         public string StudentUnit
 9         {
10             get { return studentUnit; }
11             set { studentUnit = value; }
12         }
13         private string studentDepartment;
14 
15         public string StudentDepartment
16         {
17             get { return studentDepartment; }
18             set { studentDepartment = value; }
19         }
20         private string studentName;
21 
22         public string StudentName
23         {
24             get { return studentName; }
25             set { studentName = value; }
26         }
27         private string studentPosition;
28 
29         public string StudentPosition
30         {
31             get { return studentPosition; }
32             set { studentPosition = value; }
33         }
34         private int studentGender;
35 
36         public int StudentGender
37         {
38             get { return studentGender; }
39             set { studentGender = value; }
40         }
41         private string studentNation;
42 
43         public string StudentNation
44         {
45             get { return studentNation; }
46             set { studentNation = value; }
47         }
48         private string stuDentIdCard;
49 
50         public string StuDentIdCard
51         {
52             get { return stuDentIdCard; }
53             set { stuDentIdCard = value; }
54         }
55         private string trainShiftNum;
56 
57         public string TrainShiftNum
58         {
59             get { return trainShiftNum; }
60             set { trainShiftNum = value; }
61         }
62         private string remark;
63 
64         public string Remark
65         {
66             get { return remark; }
67             set { remark = value; }
68         }
69     }

结果如图：