1 [HttpPost]//只接受post请求2 public voidPrintExcel()3 {4 //接收参数 int id=Int32.Parse(Requert.Form["id"]);
5 string trainShift = Session["trainshiftNum"].ToString();6 #region 转换成DataSet
7 var query = from s indb.Students8 where s.IsDelete == false && s.TrainShiftNum ==trainShift9 select newStu10 {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.Remark20 };21 DataTable dt = query.ToDataTable(rec => new object[] { query });//调用转换DataTable方法22 #endregion
23
24 HSSFWorkbook book = newHSSFWorkbook();25 MemoryStream ms = newMemoryStream();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 indt.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 }