一、连接设置 

1、添加引用(不必)
  Interop.Excel.dll
  Interop.Microsoft.Office.Core.dll

2、新建一个Excel助手类
    class ExcelHelper

3、引入命名空间
        using Excel;
        using System.Data.OleDb;

4、 定义变量 
        private OleDbConnection conn = null;
        private OleDbCommand cmd = null;
        private OleDbDataReader sdr = null;

5、 数据表链接
        public ExcelHelper()
        {
            string xlsPath = MainForm.Strpath + "\\Data\\Data.xls"; // 绝对物理路径
            string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + xlsPath + ";Extended Properties ='Excel 8.0'";
            conn = new OleDbConnection(connStr);
        }

6、 打开数据库
        private OleDbConnection GetConn()
        {
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            return conn;
        }

7、 执行增删改SQL语句
        public int ExecuteNonQuery(string cmdText)//返回数值为被执行影响的行数
        {
            int res;
            try
            {
                cmd = new OleDbCommand(cmdText, GetConn());
                cmd.CommandType =CommandType.Text;
                res = cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();                                            //关闭数据库连接
                }
            }
            return res;
        }
        #endregion

8、执行查询SQL语句
        public System.Data.DataTable ExecuteQuery(string cmdText)
        {
            System.Data.DataTable dt = new System.Data.DataTable();
            cmd = new OleDbCommand(cmdText, GetConn());
            cmd.CommandType = CommandType.Text;
            using (sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
            {
                dt.Load(sdr);
            }
            return dt;
        }

    //根据Excel物理路径获取Excel文件中所有表名

    public String[] GetExcelSheetNames(string excelFile)
    {
        System.Data.DataTable dt = null;
        try
        {
            dt = GetConn().GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            if (dt == null)
            {
                return new string[0];
            }String[] excelSheets = new String[dt.Rows.Count];
            int i = 0;
            foreach (DataRow row in dt.Rows)
            {
                excelSheets[i] = row["TABLE_NAME"].ToString();
                i++;
            }
            return excelSheets;
        } catch
        {
            return null;
        }
        finally
        {
            if (conn.State == ConnectionState.Open)
            {
                conn.Close();                                            //关闭数据库连接
            }
            if (dt != null)
            {
                dt.Dispose();
            }
        }
    }

 二、调用方法

1、在需要调用的类中定义变量
        private ExcelHelper excelhelper;
        public 调用类名()
        {
            excelhelper = new ExcelHelper();
        }

2、新增记录
        public bool Insert(string 数值1, string 数值2)
        {
            bool flag = false;
            string cmdText = "INSERT INTO [表名$] ([列名1], [列名]) VALUES ('" + 数值1 + "','" + 数值2+ "')";
            int res = excelhelper.ExecuteNonQuery(cmdText);
            if (res > 0)
            {
                flag = true;
            }
            return flag;
        }

3、修改记录
        public bool Update(string 数值1, string 数值2, string 数值3)
        {
            bool flag = false;
            string cmdText = "UPDATE [表名$] SET  [列名1] ='" + 数值1+ "',[列名2] ='" +数值2+ "'  WHERE  [列名3] = '" + 数值3+ "'";
            int res = excelhelper.ExecuteNonQuery(cmdText);
            if (res > 0)
            {
                flag = true;
            }
            return flag;
        }

4、查询记录
        public DataTable SelectAll()
        {
            DataTable dt = new DataTable();
            string cmdText = "SELECT * FROM [表名$] ORDER BY [列名1]";
            dt = excelhelper.ExecuteQuery(cmdText);
            return dt;
        }