namespace NPOI操作Excel

 {

     public class ExcelHelper

     {

         /// <summary>

         /// DataTable转成Excel，返回一个文件流

         /// </summary>

         /// <param name="dataTable"></param>

         /// <returns></returns>

         public static Stream DatatableToExcel(DataTable dataTable)

         {

             IWorkbook wk = new HSSFWorkbook();

             MemoryStream ms = new MemoryStream();

             ISheet sheet = wk.CreateSheet();

             IRow headerRow = sheet.CreateRow();

             //处理标题部分

             foreach (DataColumn column in dataTable.Columns)

             {

                 headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);

             }

             //处理table中的数据

             int rowIndex = ;

             foreach (DataRow row in dataTable.Rows)

             {

                 IRow dataRow = sheet.CreateRow(rowIndex);

                 foreach (DataColumn column in dataTable.Columns)

                 {

                     //column.Ordinal得到从0开始的列的位置，column.ColumnName的到列的名称

                     dataRow.CreateCell(column.Ordinal).SetCellValue(row[column.ColumnName].ToString());

                 }

                 rowIndex++;

             }

             wk.Write(ms);

             ms.Flush();

             ms.Position = ;

             sheet = null;

             headerRow = null;

             return ms;

         }

         /// <summary>

         /// 读取Excel文件,转换成DataTable

         /// </summary>

         /// <param name="ExcelFileStream">读取到Excel的文件流</param>

         /// <param name="SheetIndex">表的索引</param>

         /// <param name="HeaderRowIndex">标题行的索引</param>

         /// <returns></returns>

         public static DataTable DataTableFromExcel(Stream ExcelFileStream, int SheetIndex, int HeaderRowIndex)

         {

             IWorkbook wk = new HSSFWorkbook(ExcelFileStream);

             ISheet sheet = wk.GetSheetAt(SheetIndex);

             DataTable table = new DataTable();

             IRow headerRow = sheet.GetRow(HeaderRowIndex);

             int cellCount = headerRow.LastCellNum;

             //处理标题行的数据

             for (int i = headerRow.FirstCellNum; i < cellCount; i++)

             {

                 DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);

                 table.Columns.Add(column);

             }

             //循环遍历sheet中的每一行，读取每一单元格的数据，同时创建table中的每一行，填充数据

             int rowCount = sheet.LastRowNum;

             for (int i = (sheet.FirstRowNum + ); i <= sheet.LastRowNum; i++)

             {

                 IRow row = sheet.GetRow(i);

                 //以表的格式创建一个新的行

                 DataRow dataRow = table.NewRow();

                 for (int j = row.FirstCellNum; j < cellCount; j++)

                 {

                     if (row.GetCell(j) != null)

                     {

                         dataRow[j] = row.GetCell(j).ToString();

                     }

                 }

                 //dataRow的数据填充好后把行加到表中

                 table.Rows.Add(dataRow);

             }

             ExcelFileStream.Close();

             wk = null;

             sheet = null;

             return table;

         }

     }

 }

 namespace NPOI操作Excel

 {

     public static class SqlHelper

     {

         //从配置文件中读取连接字符串

         private static readonly string conStr = ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;

         //执行命令的方法 insert update delete

         public static int ExecuteNonquey(string sql, params SqlParameter[] ps)

         {

             using (SqlConnection con = new SqlConnection(conStr))

             {

                 using (SqlCommand cmd = new SqlCommand(sql, con))

                 {

                     con.Open();

                     cmd.Parameters.AddRange(ps);

                     return cmd.ExecuteNonQuery();

                 }

             }

         }

         //获取首行首列

         public static object ExecuteScalar(string sql, params SqlParameter[] ps)

         {

             using (SqlConnection con = new SqlConnection(conStr))

             {

                 using (SqlCommand cmd = new SqlCommand(sql, con))

                 {

                     con.Open();

                     cmd.Parameters.AddRange(ps);

                     return cmd.ExecuteScalar();

                 }

             }

         }

         //获取读取数据库的对象

         public static SqlDataReader ExecuteDataReader(string sql, params SqlParameter[] ps)

         {

             SqlConnection con = new SqlConnection(conStr);

             using (SqlCommand cmd = new SqlCommand(sql, con))

             {

                 try

                 {

                     con.Open();

                     cmd.Parameters.AddRange(ps);

                     return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);

                 }

                 catch (Exception ex)

                 {

                     con.Close();

                     con.Dispose();

                     throw ex;

                 }

             }

         }

         //获取结果集

         public static DataTable GetDataTable(string sql, params SqlParameter[] ps)

         {

             //构造数据表，用于接收查询结果

             DataTable dt = new DataTable();

             //构造适配器对象

             using (SqlDataAdapter sda = new SqlDataAdapter(sql, conStr))

             {

                 //添加参数

                 sda.SelectCommand.Parameters.AddRange(ps);

                 //执行

                 sda.Fill(dt);

                 return dt;

             }

         }

     }

 }

         private void button1_Click(object sender, EventArgs e)

         {

             DataTable dt = SqlHelper.GetDataTable("select * from t_persons");

             MemoryStream ms = ExcelHelper.DatatableToExcel(dt) as MemoryStream;

             string saveFileNme = "";

             bool fileSaved = false;

             //保存文件对话框

             SaveFileDialog saveDialog = new SaveFileDialog();

             //设置默认的文件类型

             saveDialog.DefaultExt = "xls";

             saveDialog.Filter = "Excel文件|*.xls";

             //设置文件名

             saveDialog.FileName = "保存";

             saveDialog.ShowDialog();

             //获得文件全路径

             saveFileNme = saveDialog.FileName;

             if (saveFileNme.IndexOf(":") < ) return;

             if (saveFileNme != "")

             {

                 try

                 {

                     FileStream fs = new FileStream(saveDialog.FileName, FileMode.Create);

                     fs.Write(ms.GetBuffer(), , ms.GetBuffer().Length);

                     ms.Close();

                     ms.Dispose();

                     fs.Close();

                     fileSaved = true;

                 }

                 catch (Exception ex)

                 {

                     fileSaved = false;

                     MessageBox.Show("导出文件出错，文件可能正在被占用\n" + ex.Message);

                 }

             }

             else

             {

                 fileSaved = false;

             }

             GC.Collect();//强行销毁

             if (fileSaved&&File.Exists(saveFileNme))

             {

                 MessageBox.Show("导出成功", "通知");

             }

             else

             {

                 MessageBox.Show("导出失败", "通知");

             }

         }

         private void button2_Click(object sender, EventArgs e)

         {

             //打开文件对话框

             OpenFileDialog fileDialog=new OpenFileDialog();

             //指定要打开文件的格式

             fileDialog.Filter = "Excel文件|*.xls";

             //设置默认打开路径

             fileDialog.InitialDirectory = @"C:\Users\LWP1398\Desktop";

             if (fileDialog.ShowDialog()==DialogResult.OK)

             {

                 string fileName = fileDialog.FileName;//得到文件全路径

                 using (FileStream fsRead=new FileStream(fileName,FileMode.Open,FileAccess.Read))

                 {

                     DataTable dt = ExcelHelper.DataTableFromExcel(fsRead, , );

                     dgv.DataSource = dt;

                 }

             }

             MessageBox.Show("ok");

         }

     }