数据库数据输出到excel

 1              SqlConnection conn = new SqlConnection("Data Source=.;Initial Catalog=ArchiveSys;Integrated Security=True");
 2             //SQLHelper sqlhelper = new SQLHelper();
 3             string sql = "select * from StuFeedback";
 4             using (SqlCommand cmd = new SqlCommand(sql, conn))
 5             {
 6                 if (conn.State==ConnectionState.Closed)
 7                 {
 8                     conn.Open();
 9                 }
10                 using (SqlDataReader read = cmd.ExecuteReader())
11                 {
12                     if (read.HasRows)
13                     {
14                         IWorkbook wk = new HSSFWorkbook();//创建workbook
15                         ISheet sheet = wk.CreateSheet("stufeedback");//创建工作表
16                         int rowIndex = 0;
17                         #region 循环创建行与列
18                         while (read.Read())
19                         {
20                             int id = read.GetInt32(0);
21                             int? stuid = read.IsDBNull(1) ? null : (int?)read.GetInt32(1);//int?为可控值类型
22                             string subtime = read.IsDBNull(2) ? "NULL" : read.GetString(2);
23                             int? appstate = read.IsDBNull(3) ? null : (int?)read.GetInt32(3);
24                             string stuname = read.IsDBNull(4) ? "NULL" : read.GetString(4);
25                             //创建行
26                             IRow row = sheet.CreateRow(rowIndex);
27                             //创建单元格
28                             row.CreateCell(0).SetCellValue(id);
29                             #region 若为空值，创建空单元格
30                             if (stuid == null)
31                             {
32                                 row.CreateCell(1, CellType.BLANK);
33                             }
34                             else
35                             {
36                                 row.CreateCell(1).SetCellValue((int)stuid);
37                             }
38                             #endregion
39                             row.CreateCell(2).SetCellValue(subtime);
40                             #region 对于空值处理
41                             if (appstate == null)
42                             {
43                                 row.CreateCell(3, CellType.BLANK);
44                             }
45                             else
46                             {
47                                 row.CreateCell(3).SetCellValue((int)appstate);
48                             }
49                             #endregion
50                             row.CreateCell(4).SetCellValue(stuname);
51                             rowIndex++;
52                         }
53                         #endregion
54                         using (FileStream fs = File.OpenWrite(@"D:\学籍管理\stufeedback.xls"))
55                         {
56                             wk.Write(fs);
57                             Response.Write("<script>alert(‘成功！‘)</script>");
58                         }
59                     }
60                     else
61                     {
62                         Response.Write("<script>alert(‘数据为空！‘)</script>");
63                     }
64                 }
65             }