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 }