使用文件流将数据从数据库导出到Excel

/*本案例主要是利用第三方的插件NOPI(点此下载)进行对Excel的操作*/

string strCon = "Data Source=.;Initial Catalog=SettingsProvider;Integrated Security=True"; 

            using (FileStream fs = new FileStream("my.xls", FileMode.Create))//一个文件流
            {
                HSSFWorkbook workbook = new HSSFWorkbook();//创建一个Excel工作簿
                using (workbook)
                {
                    Sheet sheet = workbook.CreateSheet("sheet1");//创建一个工作表
                    using (sheet)
                    {
                        using (IDbConnection con = new SqlConnection(strCon))
                        {
                            #region 
                            using (con)
                            {
                                using (IDbCommand cmd = con.CreateCommand())
                                {
                                    string sql = "select * from tab";
                                    cmd.CommandText = sql;
                                    con.Open();
                                    using (IDataReader reader = cmd.ExecuteReader())
                                    {
                                        //从数据库读文件,并添加到工作表
                                        #region while 
                                        int i = 0;
                                        while (reader.Read())
                                        {

                                           //创建行对象
                                            Row row = sheet.CreateRow(i);

                                            //创建单元格
                                            Cell c1 = row.CreateCell(0);

                                            //设置单元格格式
                                            c1.SetCellType(CellType.NUMERIC);
                                            Cell c2 = row.CreateCell(1);
                                            c2.SetCellType(CellType.STRING);
                                            Cell c3 = row.CreateCell(2);
                                            c3.SetCellType(CellType.STRING); 

                                            int id = reader.GetInt32(0);
                                            string name = reader.GetString(1);
                                            string value = reader.GetString(2);
                                            c1.SetCellValue(id);
                                            c2.SetCellValue(name);
                                            c3.SetCellValue(value);
                                            i++; 

                                        }
                                        #endregion 

                                            //写到工作表
                                            workbook.Write(stream);
                                            MessageBox.Show("成功");
                                    }
                                }
                            }
                            #endregion 
                       }
                    }
                }
            }