将数据库中的数据导出到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("表中无数据");
                }
            }
        }

 

转载于:https://www.cnblogs.com/xiaoxu-230/p/3316076.html