首页 > 代码库 > C#使用ADO操作Excel

C#使用ADO操作Excel

2024-07-18 03:27:09   229人阅读

1 说明

　　把excel当成一个数据库，类似于Access数据库来操作。

2 源代码

2.1 Model层

    /// <summary>    /// 人员信息    /// </summary>    public class MPerson    {        /// <summary>        /// 人员姓名        /// </summary>        public string PersonName        {            get;            set;        }        /// <summary>        /// 人员家乡        /// </summary>        public string PersonHome        {            get;            set;        }    }

2.2 数据库访问接口

    /// <summary>    /// 人员数据库访问接口    /// </summary>    public interface IPerson    {        /// <summary>        /// 获取所有人员信息        /// </summary>        /// <param name="conn">连接</param>        /// <param name="tran">事物</param>        /// <returns>所有人员信息</returns>        List<MPerson> GetAllPersonInfo(OleDbConnection conn, OleDbTransaction tran);        /// <summary>        /// 添加人员信息        /// </summary>        /// <param name="conn">连接</param>        /// <param name="tran">事物</param>        /// <param name="person">人员信息</param>        /// <returns>影响记录数</returns>        int AddPerson(OleDbConnection conn, OleDbTransaction tran, MPerson person);        /// <summary>        /// 修改人员信息        /// </summary>        /// <param name="conn">连接</param>        /// <param name="tran">事物</param>        /// <param name="model">人员</param>        /// <returns>影响记录数</returns>        int UpdatePerson(OleDbConnection conn, OleDbTransaction tran, MPerson model);        /// <summary>        /// 查询人员信息        /// </summary>        /// <param name="conn">连接</param>        /// <returns>所有人员信息</returns>        List<MPerson> GetPersonInfo(OleDbConnection conn, OleDbTransaction tran, string personName);        /// <summary>        /// 删除人员信息【不支持】        /// </summary>        /// <param name="conn">连接</param>        /// <param name="tran">事物</param>        /// <param name="model">人员</param>        /// <returns>影响记录数</returns>        //int DelPerson(OleDbConnection conn, OleDbTransaction tran, MPerson model);    }

2.3 数据库访问类

    /// <summary>    /// 人员信息数据库访问    /// </summary>    public class DPerson : IPerson    {        #region IPerson 成员        /// <summary>        /// 获取所有人员信息        /// </summary>        /// <param name="conn">连接</param>        /// <returns>所有人员信息</returns>        public List<MPerson> GetAllPersonInfo(OleDbConnection conn, OleDbTransaction tran)        {            List<MPerson> list = new List<MPerson>();            string sql = "select PersonName,PersonHome from [PersonInfo$]";            OleDbCommand cmd = new OleDbCommand(sql, conn, tran);            using (OleDbDataReader reader = cmd.ExecuteReader())            {                while (reader.Read())                {                    MPerson model = new MPerson();                    model.PersonName = reader["PersonName"] == DBNull.Value ? string.Empty : reader["PersonName"].ToString();                    model.PersonHome = reader["PersonHome"] == DBNull.Value ? string.Empty : reader["PersonHome"].ToString();                    list.Add(model);                }            }            return list;        }        /// <summary>        /// 查询所有人员信息        /// </summary>        /// <param name="conn">连接</param>        /// <returns>所有人员信息</returns>        public List<MPerson> GetPersonInfo(OleDbConnection conn, OleDbTransaction tran, string personName)        {            List<MPerson> list = new List<MPerson>();            string sql = "SELECT PersonName,PersonHome from [PersonInfo$] WHERE PersonName = @PersonName";            OleDbCommand cmd = new OleDbCommand(sql, conn, tran);            OleDbParameter[] para =             {                new OleDbParameter("@PersonName", personName.Trim()),            };            cmd.Parameters.AddRange(para);            using (OleDbDataReader reader = cmd.ExecuteReader())            {                while (reader.Read())                {                    MPerson model = new MPerson();                    model.PersonName = reader["PersonName"] == DBNull.Value ? string.Empty : reader["PersonName"].ToString();                    model.PersonHome = reader["PersonHome"] == DBNull.Value ? string.Empty : reader["PersonHome"].ToString();                    list.Add(model);                }            }            return list;        }        /// <summary>        /// 添加人员信息        /// </summary>        /// <param name="conn">连接</param>        /// <param name="person">人员信息</param>        /// <returns>影响记录数</returns>        public int AddPerson(OleDbConnection conn, OleDbTransaction tran, MPerson person)        {            string sql = @"INSERT INTO [PersonInfo$](PersonName, PersonHome)                            VALUES(@Name, @Home)";            OleDbParameter[] para =            {                new OleDbParameter("@Name", person.PersonName),                new OleDbParameter("@Home", person.PersonHome)            };            OleDbCommand cmd = null;            cmd = new OleDbCommand(sql, conn, tran);            cmd.Parameters.AddRange(para);            return cmd.ExecuteNonQuery();        }        /// <summary>        /// 修改人员信息        /// </summary>        /// <param name="conn">连接</param>        /// <param name="tran">事物</param>        /// <param name="model">人员</param>        /// <returns>影响记录数</returns>        public int UpdatePerson(OleDbConnection conn, OleDbTransaction tran, MPerson model)        {            string sql = string.Format(@"                            UPDATE [PersonInfo$]                            SET PersonHome=@PersonHome                            WHERE PersonName=‘{0}‘                        ", model.PersonName);            OleDbParameter[] para =             {                new OleDbParameter("@PersonHome", model.PersonHome.Trim())            };            OleDbCommand cmd = null;            cmd = new OleDbCommand(sql, conn, tran);            cmd.Parameters.AddRange(para);            return cmd.ExecuteNonQuery();        }        /// <summary>        /// 删除人员信息【不支持】        /// </summary>        /// <param name="conn">连接</param>        /// <param name="tran">事物</param>        /// <param name="model">人员</param>        /// <returns>影响记录数</returns>//        public int DelPerson(OleDbConnection conn, OleDbTransaction tran, MPerson model)//        {//            string sql = string.Format(@"//                            DELETE FROM [PersonInfo$]//                            WHERE PersonName=‘{0}‘//                        ", model.PersonName);//            OleDbCommand cmd = null;//            cmd = new OleDbCommand(sql, conn, tran);//            return cmd.ExecuteNonQuery();//        }        #endregion    }

2.4 数据库连接工厂

    /// <summary>    /// 数据库链接工厂    /// </summary>    public class ConnectionFactory    {        /// <summary>        /// 基础数据读库        /// </summary>        public static OleDbConnection BaseInfoDBRead        {            get { return GetConnection("BaseInfoDBRead"); }        }        /// <summary>        /// 基础数据写库        /// </summary>        public static OleDbConnection BaseInfoDBWrite        {            get { return GetConnection("BaseInfoDBWrite"); }        }        /// <summary>        /// 根据连接字符串获取连接        /// </summary>        /// <param name="connectionKey">连接键</param>        /// <returns>连接</returns>        public static OleDbConnection GetConnection(string connectionKey)        {            OleDbConnection connection = null;            string connectionString = ConfigurationManager.ConnectionStrings[connectionKey].ConnectionString;            connection = new OleDbConnection(connectionString);            return connection;        }

2.5 数据库访问工厂

    /// <summary>    /// 数据库访问工厂    /// </summary>    public class DALFactory    {        /// <summary>        /// 获取人员信息数据访问层        /// </summary>        /// <returns>数据访问层</returns>        public static IPerson GetPersonDao()        {            return new DPerson();        }    }

2.6 业务层

2.6.1 添加事物类

    /// <summary>    /// 添加人员事物    /// </summary>    public class BAddPersonsTrans : ExcelDbTransaction    {        /// <summary>        /// 人员信息        /// </summary>        private List<MPerson> personList = null;        /// <summary>        /// 构造函数        /// </summary>        /// <param name="list">人员信息</param>        public BAddPersonsTrans(List<MPerson> list)        {            this.personList = list;            this.Connection = ConnectionFactory.BaseInfoDBWrite;            this.IsolationLevel = System.Data.IsolationLevel.ReadCommitted;            this.IsBeginTransation = true;        }        /// <summary>        /// 是否成功        /// </summary>        public bool IsSuccess        {            get;            set;        }        /// <summary>        /// 添加人员信息        /// </summary>        protected override void ExecuteMethod()        {            IPerson personDao = DALFactory.GetPersonDao();            if (this.personList != null && this.personList.Count > 0)            {                foreach (MPerson item in this.personList)                {                    personDao.AddPerson(this.Connection, this.Transation, item);                }                this.IsSuccess = true;            }        }    }

2.6.2 业务类

    /// <summary>    /// 人员信息业务层    /// </summary>    public class BPerson    {        /// <summary>        /// 获取所有人员信息        /// </summary>        /// <returns>所有人员信息</returns>        public List<MPerson> GetAllPersonInfo()        {            IPerson dao = DALFactory.GetPersonDao();            OleDbConnection conn = ConnectionFactory.BaseInfoDBRead;            try            {                if (conn.State == System.Data.ConnectionState.Closed)                {                    conn.Open();                }                return dao.GetAllPersonInfo(conn, null);            }            finally            {                conn.Close();                conn.Dispose();            }        }        /// <summary>        /// 查询所有人员信息        /// </summary>        /// <returns>所有人员信息</returns>        public List<MPerson> GetPersonInfo(string personName)        {            IPerson dao = DALFactory.GetPersonDao();            OleDbConnection conn = ConnectionFactory.BaseInfoDBRead;            try            {                if (conn.State == System.Data.ConnectionState.Closed)                {                    conn.Open();                }                return dao.GetPersonInfo(conn, null, personName);            }            finally            {                conn.Close();                conn.Dispose();            }        }        /// <summary>        /// 添加人员列表        /// </summary>        /// <param name="list">人员列表</param>        /// <returns>是否成功</returns>        public bool AddPersonList(List<MPerson> list)        {            BAddPersonsTrans tran = new BAddPersonsTrans(list);            tran.Execute();            return tran.IsSuccess;        }        /// <summary>        /// 修改人员信息        /// </summary>        /// <param name="model">人员对象</param>        /// <returns>影响数据库记录数</returns>        public int UpdatePerson(MPerson model)        {            IPerson dao = DALFactory.GetPersonDao();            OleDbConnection conn = ConnectionFactory.BaseInfoDBWrite;            try            {                if (conn.State == System.Data.ConnectionState.Closed)                {                    conn.Open();                }                return dao.UpdatePerson(conn, null, model);            }            finally            {                conn.Close();                conn.Dispose();            }        }        /// <summary>        /// 删除人员信息【不支持】        /// </summary>        /// <param name="model">人员对象</param>        /// <returns>影响数据库记录数</returns>        //public int DelPerson(MPerson model)        //{        //    IPerson dao = DALFactory.GetPersonDao();        //    OleDbConnection conn = ConnectionFactory.BaseInfoDBWrite;        //    try        //    {        //        if (conn.State == System.Data.ConnectionState.Closed)        //        {        //            conn.Open();        //        }        //        return dao.DelPerson(conn, null, model);        //    }        //    finally        //    {        //        conn.Close();        //        conn.Dispose();        //    }        //}    }

2.7 测试

2.7.1 配置文件

App.config配置如下：

<?xml version="1.0" encoding="utf-8" ?><configuration>  <connectionStrings>    <add name="BaseInfoDBRead" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=BaseInfoDB.xls;Extended Properties=‘Excel 8.0;HDR=YES;IMEX=2‘"/>    <add name="BaseInfoDBWrite" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=BaseInfoDB.xls;Extended Properties=‘Excel 8.0;HDR=YES;IMEX=2‘"/>  </connectionStrings></configuration>

2.7.2 测试代码

 /// <summary>    /// 测试    /// </summary>    public class Test    {        /// <summary>        /// 测试获取人员信息        /// </summary>        public static void TestGetAllPerson()        {            try            {                BPerson bll = new BPerson();                List<MPerson> personList = bll.GetAllPersonInfo();                if (personList != null && personList.Count > 0)                {                    foreach (MPerson item in personList)                    {                        Console.WriteLine(item.PersonName + "," + item.PersonHome);                    }                }            }            catch (Exception ex)            {                string str = ex.ToString();                Console.WriteLine(str);            }        }        /// <summary>        /// 测试添加人员列表        /// </summary>        public static void AddPersonList()        {            try            {                BPerson bll = new BPerson();                MPerson model = new MPerson();                model.PersonName = "艾玛";                model.PersonHome = "美国";                List<MPerson> list = new List<MPerson>();                list.Add(model);                model = new MPerson();                model.PersonName = "汤姆";                model.PersonHome = "美国";                list.Add(model);                bool result = bll.AddPersonList(list);                Console.WriteLine(result);            }            catch (Exception ex)            {                Console.WriteLine(ex.ToString());            }        }        /// <summary>        /// 测试修改        /// </summary>        public static void UpdatePerson()        {            try            {                MPerson model = new MPerson();                model.PersonName = "张三";                model.PersonHome = "上海";                BPerson bll = new BPerson();                int result = bll.UpdatePerson(model);                Console.WriteLine("结果:" + result);            }            catch (Exception ex)            {                Console.WriteLine(ex.ToString());            }        }        /// <summary>        /// 测试删除        /// </summary>        //public static void DelPerson()        //{        //    try        //    {        //        MPerson model = new MPerson();        //        model.PersonName = "张三";        //        BPerson bll = new BPerson();        //        int result = bll.DelPerson(model);        //        Console.WriteLine("结果:" + result);        //    }        //    catch (Exception ex)        //    {        //        Console.WriteLine(ex.ToString());        //    }        //}    }
View Code

2.7.3 测试文件

　　BaseInfoDB.xls，注意将属性设置为始终复制

　　格式如下：

　　注意抬头部分一定是：PersonName,PersonHome

　　

3 注意

　　（1）首先，上面虽然写到事物，但是事物确不能生效，也就是要不全部执行成功，要不全部执行失败，在网上找了很久，发现代码没什么大问题，但是不能实现效果，很是郁闷，还是把代码贴出来，万一以后就知道怎么回事了呢？

　　（2）修改的时候，不知道为什么不能在where语句中使用参数，只能使用拼sql，才执行成功，我在查询的时候试了一下，发现可以在where语句中使用参数，不知道这里为什么不行。

　　（3）、另外不能进行删除操作。报出来得错误信息是：该ISAM不支持在连接表中删除数据。网上查了一下确实是不支持删除。如果要删除就只能使用修改，在查询的使用进行处理。

　　

 

 

　　


联系
我们