Excel操作--使用NPOI导入导出Excel为DataTable

1.ExcelHelper封装

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(0);

            //处理标题部分
            foreach (DataColumn column in dataTable.Columns)
            {
                headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
            }
            //处理table中的数据
            int rowIndex = 1;
            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 = 0;

            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 + 1); 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;
        }
    }
}

2.SqlHelper封装

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;
            }
        }

    }
}

3.winform中操作

3.1从数据库读取数据导入到Excel中

        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(":") < 0) return;
            if (saveFileNme != "")
            {
                try
                {
                    FileStream fs = new FileStream(saveDialog.FileName, FileMode.Create);
                    fs.Write(ms.GetBuffer(), 0, ms.GetBuffer().Length);
                    ms.Close();
                    ms.Dispose();
                    fs.Close();
                    fileSaved = true;
                }
                catch (Exception ex)
                {
                    fileSaved = false;
                    MessageBox.Show("导出文件出错，文件可能正在被占用
" + ex.Message);
                }
            }
            else
            {
                fileSaved = false;
            }
            GC.Collect();//强行销毁
            if (fileSaved&&File.Exists(saveFileNme))
            {
                MessageBox.Show("导出成功", "通知");
            }
            else
            {
                MessageBox.Show("导出失败", "通知");
            }
        }

3.2读取Excel文件，转换成DataTable绑定到DataGridView上

        private void button2_Click(object sender, EventArgs e)
        {
            //打开文件对话框
            OpenFileDialog fileDialog=new OpenFileDialog();
            //指定要打开文件的格式
            fileDialog.Filter = "Excel文件|*.xls";
            //设置默认打开路径
            fileDialog.InitialDirectory = @"C:UsersLWP1398Desktop";
            if (fileDialog.ShowDialog()==DialogResult.OK)
            {
                string fileName = fileDialog.FileName;//得到文件全路径
                using (FileStream fsRead=new FileStream(fileName,FileMode.Open,FileAccess.Read))
                {
                    DataTable dt = ExcelHelper.DataTableFromExcel(fsRead, 0, 0);
                    dgv.DataSource = dt;
                }
            }
            MessageBox.Show("ok");
        }
    }

 NPOI目前主要还是操作xls文件，对操作xlsx文件支持得不是很好