1.界面

重点：如果计算机没装Excel，则必须安装AccessDatabaseEngine_32.exe或者AccessDatabaseEngine_64.exe引擎，这样电脑就安装Excel环境，导入导出就可以执行而不报错了。（我的资源有这两个文件）

2.导入导出button按钮

 //button导出private void btnSerch_Click(object sender, EventArgs e){//Excalexport Excal = new Excalexport();//Excal.ShowDialog();saveFileDialog1.FileName = txtExcel.Text+DateTime.Today.ToString("yyyyMMdd") + ".xlsx";saveFileDialog1.ShowDialog();grv.ExportToXlsx(saveFileDialog1.FileName);//if (string.IsNullOrEmpty(this.btnSerch.Text))//{//    MessageBox.Show("请选择保存的路径！");//    return;//}//this.grv.ExportToXls(this.btnSerch.Text + "\\体检结果.xls");//MessageBox.Show("导出成功！ " + this.btnSerch.Text + "\\体检结果.xls");        }//button导入private void simpleButton1_Click(object sender, EventArgs e){//grdSalemoduleListOther.DataSource = null;//grvSalemoduleListOther.Columns.Clear();//grdSalemoduleListOther.MainView.PopulateColumns();//OpenFileDialog openFile = new OpenFileDialog();//if (openFile.ShowDialog() == DialogResult.OK)//{//    DataTable dtdata = Utils.ExcalImport.GetExcelTable(openFile.FileName);//    grdSalemoduleListOther.DataSource = dtdata;//}//对原有数据清空grd.DataSource = null;grv.Columns.Clear();grd.MainView.PopulateColumns();OpenFileDialog openFile = new OpenFileDialog();if (openFile.ShowDialog() == DialogResult.OK){DataTable dtdata = Utils.ExcalImport.GetExcelTable(openFile.FileName);grd.DataSource = dtdata;}}

3.Excel类

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Windows.Forms;
using System.Data.OleDb;
using System.IO;namespace Utils
{public class ExcalImport{//根据excle的路径把第一个sheel中的内容放入datatablepublic static DataTable ReadExcelToTable(string path)//excel存放的路径{try{//连接字符串//string connstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties='Excel 8.0;HDR=NO;IMEX=1';"; // Office 07及以上版本 不能出现多余的空格 而且分号注意string connstring = "Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + path + ";Extended Properties='Excel 8.0;HDR=NO;IMEX=1';"; //Office 07以下版本 using (OleDbConnection conn = new OleDbConnection(connstring)){conn.Open();DataTable sheetsName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" }); //得到所有sheet的名字string firstSheetName = sheetsName.Rows[0][2].ToString(); //得到第一个sheet的名字string sql = string.Format("SELECT * FROM [{0}]", firstSheetName); //查询字符串//string sql = string.Format("SELECT * FROM [{0}] WHERE [日期] is not null", firstSheetName); //查询字符串OleDbDataAdapter ada = new OleDbDataAdapter(sql, connstring);DataSet set = new DataSet();ada.Fill(set);DataTable dt = new DataTable();if (set.Tables[0] != null && set.Tables[0].Rows.Count > 0){for (int i = 0; i < set.Tables[0].Columns.Count; i++){dt.Columns.Add(set.Tables[0].Rows[0][i].ToString());}for (int i = 1; i < set.Tables[0].Rows.Count; i++){DataRow dr = set.Tables[0].Rows[i];DataRow drNew = dt.NewRow();drNew.ItemArray = dr.ItemArray;dt.Rows.Add(drNew);}}return dt;}}catch (Exception){return null;}}public const string OledbConnString = "Provider = Microsoft.ACE.OLEDB.12.0 ; Data Source = {0};Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'"; //Excel/// <summary>  /// 获取Excel文件中的信息，保存到一个DataTable中  /// </summary>  /// <param name="path">文件路径</param>  /// <returns>返回生成的DataTable</returns>  public static DataTable GetExcelTable(string path){try{//获取excel数据  DataTable dt1 = new DataTable("excelTable");string strConn = string.Format(OledbConnString, path);OleDbConnection conn = new OleDbConnection(strConn);conn.Open();DataTable dt = conn.GetSchema("Tables");//判断excel的sheet页数量，查询第1页  if (dt.Rows.Count > 0){string selSqlStr = string.Format("select * from [{0}]", dt.Rows[0]["TABLE_NAME"]);OleDbDataAdapter oleDa = new OleDbDataAdapter(selSqlStr, conn);oleDa.Fill(dt1);}conn.Close();return dt1;}catch (Exception ex){MessageBox.Show("Excel转换DataTable出错：" + ex.Message);return null;}}/// <summary>/// 将Excel导入DataSet/// </summary>/// <param name="path">文件路径</param>/// <param name="sheetName">页名</param>/// <returns>如果返回null表示错误</returns>public static DataSet ExceltToDS(string path, string sheetName, string OfficeVersion){string strConn = "";string strExcel = "";OleDbConnection conn = null;OleDbDataAdapter adapter = null;DataSet ds = null;//string strOfficeVersion = LocalConfig.IniReadValue("OfficeVersions", OfficeVersion);//配置Excel版本strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=Excel 8.0;";if ((OfficeVersion.Equals("2007")) || (Path.GetExtension(path).ToLower() == ".xlsx")){strConn = "Provider=Microsoft.Ace.OleDb.12.0;Data Source=" + path + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1'";}strExcel = "Select * from [" + sheetName + "$]";try{conn = new OleDbConnection(strConn);conn.Open();adapter = new OleDbDataAdapter(strExcel, strConn);ds = new DataSet();adapter.Fill(ds, "myTable");return ds;}finally{conn.Close();}}/// <summary>/// 读取excle文件中的表单名称，在返回的table中字段名称为"TABLE_NAME"/// </summary>/// <param name="file">Excal文件路径</param>/// <param name="OfficeVersion">Exca版本号</param>/// <returns></returns>public static DataTable getExcelSheetNames(string file, string OfficeVersion){OleDbConnection conn = null;string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + file + ";Extended Properties=Excel 8.0;";//数据库连接字符串if ((OfficeVersion.Equals("2007以上")) || (Path.GetExtension(file).ToLower() == ".xlsx")){strConn = "Provider=Microsoft.Ace.OleDb.12.0;Data Source=" + file + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1'";}try{conn = new OleDbConnection(strConn);conn.Open();DataTable table = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);return table;}finally{conn.Close();}}}
}