如何数据库表数据导出到excel中

1.首先须要有一个NPOI

 

2.接下来上代码

  1 private void button1_Click(object sender, EventArgs e)
  2 {
  3 //1.通过Ado.net读取数据
  4 string sql = "SELECT * FROM ASRS_F1";
  5 using (SqlDataReader reader = SqlHelper.ExecuteReader(sql, CommandType.Text))
  6 {
  7 //如果读到数据
  8 if (reader.HasRows)
  9 {
 10 IWorkbook wk = new HSSFWorkbook();
 11 ISheet sheet = wk.CreateSheet("ASRS_F1");
 12 
 13 #region 创建第一行，设置列名
 14 //--------------------------------------------------
 15 //创建第一行，第一行表示列名
 16 IRow rowHead = sheet.CreateRow(0);
 17 //循环查询出的每一列
 18 for (int col = 0; col < reader.FieldCount; col++)
 19 {
 20 rowHead.CreateCell(col).SetCellValue(reader.GetName(col));
 21 }
 22 //--------------------------------------------------
 23 #endregion
 24 
 25 int rindex = 1;
 26 //下面是创建数据行
 27 while (reader.Read())
 28 {
 29 //ID, Position, AName, ACode, AState, ABatch, ADateTime, BName, BCode, BState, BBatch, BDateTime, IsMoving, IsType
 30 IRow currentRow = sheet.CreateRow(rindex);
 31 rindex++;
 32 int Id = reader.GetInt32(0);
 33 string Position = reader.GetString(1);
 34 string AName = reader.GetString(2);
 35 string ACode = reader.GetString(3);
 36 string AState = reader.GetString(4);
 37 string ABatch =reader.IsDBNull(5)?null: reader.GetString(5);
 38 DateTime? lockDate = reader.IsDBNull(6) ? null : (DateTime?)reader.GetDateTime(6);
 39 string BName = reader.GetString(7);
 40 string BCode = reader.GetString(8);
 41 string BState = reader.GetString(9);
 42 string BBatch = reader.IsDBNull(10)?null:reader.GetString(10);
 43 DateTime? lockDates = reader.IsDBNull(11) ? null : (DateTime?)reader.GetDateTime(11);
 44 string IsMoving = reader.GetString(12);
 45 string IsType = reader.GetString(13);
 46 
 47 currentRow.CreateCell(0).SetCellValue(Id);
 48 currentRow.CreateCell(1).SetCellValue(Position);
 49 currentRow.CreateCell(2).SetCellValue(AName);
 50 currentRow.CreateCell(3).SetCellValue(ACode);
 51 currentRow.CreateCell(4).SetCellValue(AState);
 52 currentRow.CreateCell(5).SetCellValue(ABatch);
 53 if (lockDate == null)
 54 {
 55 //如果是null值，那么就像excel写入一个单元格，这个单元格的类型就是Blank
 56 currentRow.CreateCell(6).SetCellType(CellType.BLANK);
 57 }
 58 else
 59 {
 60 
 61 //创建一个单元格
 62 ICell cellLockDate = currentRow.CreateCell(6);
 63 
 64 //创建一个单元格样式
 65 ICellStyle cellStyle = wk.CreateCellStyle();
 66 cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("m/d/yy h:mm");
 67 //设置当前单元格应用cellStyle样式
 68 cellLockDate.CellStyle = cellStyle;
 69 
 70 
 71 cellLockDate.SetCellValue((DateTime)lockDate);
 72 }
 73 currentRow.CreateCell(7).SetCellValue(BName);
 74 currentRow.CreateCell(8).SetCellValue(BCode);
 75 currentRow.CreateCell(9).SetCellValue(BState);
 76 currentRow.CreateCell(10).SetCellValue(BBatch);
 77 
 78 if (lockDates == null)
 79 {
 80 //如果是null值，那么就像excel写入一个单元格，这个单元格的类型就是Blank
 81 currentRow.CreateCell(11).SetCellType(CellType.BLANK);
 82 }
 83 else
 84 {
 85 
 86 //创建一个单元格
 87 ICell cellLockDate = currentRow.CreateCell(11);
 88 
 89 //创建一个单元格样式
 90 ICellStyle cellStyle = wk.CreateCellStyle();
 91 cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("m/d/yy h:mm");
 92 //设置当前单元格应用cellStyle样式
 93 cellLockDate.CellStyle = cellStyle;
 94 
 95 
 96 cellLockDate.SetCellValue((DateTime)lockDates);
 97 }
 98 currentRow.CreateCell(12).SetCellValue(IsMoving);
 99 currentRow.CreateCell(13).SetCellValue(IsType);
100 }
101 
102 //写入
103 using (FileStream fsWrite = File.OpenWrite("半成品库存表.xls"))
104 {
105 wk.Write(fsWrite);
106 }
107 label1.Text = "写入成功！" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
108 }
109 else
110 {
111 label1.Text = "没有查询到任何数据";
112 }
113 }
114 
115 }

 

posted on 2015-09-11 16:47  学无止境_上海  阅读(1153)  评论(0)  编辑  收藏  举报

导航