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 }