将数据库中的数据导出到Excel
private void button1_Click(object sender, EventArgs e) { string sql = "select * from Employee"; using(SqlDataReader reader = DBHelper.ExecuteDataReader (sql,CommandType.Text)) { if (reader.HasRows) { //如果有读到数据 则创建一个Excel工作薄Workbook IWorkbook wk = new HSSFWorkbook(); //创建一个工作表Sheet ISheet employeeSheet = wk.CreateSheet("EmployeeMsg"); int rowIndex = 0; //用来维护行的索引 while (reader.Read()) { #region 循环一次就从数据库中获取一行数据 int id = reader.GetInt32(0); string name = reader.IsDBNull(1) ? null : reader.GetString(1); string gender = reader.IsDBNull(2) ? null : reader.GetString(2); int? age = reader.IsDBNull(3) ? null : (int?)reader.GetInt32(3); decimal? salary = reader.IsDBNull(4) ? null : (decimal?)reader.GetDecimal(4); string tel = reader.IsDBNull(5) ? null : reader.GetString(5); #endregion // 每循环一次 为Sheet中添加一行 IRow row = employeeSheet.CreateRow(rowIndex); // 为行添加单元格 row.CreateCell(0).SetCellValue(id); row.CreateCell(1).SetCellValue(name); row.CreateCell(2).SetCellValue(gender); // 报错 row.CreateCell(3).SetCellValue(age); 得做空值处理 if (age == null) { row.CreateCell(3, CellType.BLANK); // 如果值为null 则创建一个空单元格 } else { row.CreateCell(3).SetCellValue((int)age); } // row.CreateCell(4).SetCellValue(salary); if (salary == null) { row.CreateCell(4, CellType.BLANK); } else { row.CreateCell(4, CellType.NUMERIC).SetCellValue((double)salary); } if (tel == null) { row.CreateCell(5, CellType.BLANK); } else { row.CreateCell(5).SetCellValue(tel); } rowIndex++; } // 创建文件流 想Excel中写入数据 using (FileStream fs = File.OpenWrite("employee.xls")) { wk.Write(fs); MessageBox.Show("写入成功"); } } else { MessageBox.Show("表中无数据"); } } }