C#操作Excel数据增删改查示例

1.首先创建测试库.xls文件，并添加两张工作表。

　　工作表1：
　　专业擂台，字段：ID、CATEGORY、NUM、QUESTION、ANSWER。

2.创建ExcelHelper.cs类，Excel文件处理类

　　

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data;

namespace DTXT.DAL
{
    /// <summary> 
    /// Excel文件处理类 
    /// </summary> 
    public class ExcelHelper
    {
        private static string fileName = AppDomain.CurrentDomain.SetupInformation.ApplicationBase + @"ExcelFile\测试库.xls";

        private static OleDbConnection connection;
        public static OleDbConnection Connection
        {
            get
            {
                string connectionString = "";
                string fileType = System.IO.Path.GetExtension(fileName);
                if (string.IsNullOrEmpty(fileType)) return null;
                if (fileType == ".xls")
                {
                    connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + fileName + ";" + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=2;\"";
                }
                else
                {
                    connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + fileName + ";" + ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=2;\"";
                }
                if (connection == null)
                {
                    connection = new OleDbConnection(connectionString);
                    connection.Open();
                }
                else if (connection.State == System.Data.ConnectionState.Closed)
                {
                    connection.Open();
                }
                else if (connection.State == System.Data.ConnectionState.Broken)
                {
                    connection.Close();
                    connection.Open();
                }
                return connection;
            }
        }

        /// <summary> 
        /// 执行无参数的SQL语句 
        /// </summary> 
        /// <param name="sql">SQL语句</param> 
        /// <returns>返回受SQL语句影响的行数</returns> 
        public static int ExecuteCommand(string sql)
        {
            OleDbCommand cmd = new OleDbCommand(sql, Connection);
            int result = cmd.ExecuteNonQuery();
            connection.Close();
            return result;
        }

        /// <summary> 
        /// 执行有参数的SQL语句 
        /// </summary> 
        /// <param name="sql">SQL语句</param> 
        /// <param name="values">参数集合</param> 
        /// <returns>返回受SQL语句影响的行数</returns> 
        public static int ExecuteCommand(string sql, params OleDbParameter[] values)
        {
            OleDbCommand cmd = new OleDbCommand(sql, Connection);
            cmd.Parameters.AddRange(values);
            int result = cmd.ExecuteNonQuery();
            connection.Close();
            return result;
        }

        /// <summary> 
        /// 返回单个值无参数的SQL语句 
        /// </summary> 
        /// <param name="sql">SQL语句</param> 
        /// <returns>返回受SQL语句查询的行数</returns> 
        public static int GetScalar(string sql)
        {
            OleDbCommand cmd = new OleDbCommand(sql, Connection);
            int result = Convert.ToInt32(cmd.ExecuteScalar());
            connection.Close();
            return result;
        }

        /// <summary> 
        /// 返回单个值有参数的SQL语句 
        /// </summary> 
        /// <param name="sql">SQL语句</param> 
        /// <param name="parameters">参数集合</param> 
        /// <returns>返回受SQL语句查询的行数</returns> 
        public static int GetScalar(string sql, params OleDbParameter[] parameters)
        {
            OleDbCommand cmd = new OleDbCommand(sql, Connection);
            cmd.Parameters.AddRange(parameters);
            int result = Convert.ToInt32(cmd.ExecuteScalar());
            connection.Close();
            return result;
        }

        /// <summary> 
        /// 执行查询无参数SQL语句 
        /// </summary> 
        /// <param name="sql">SQL语句</param> 
        /// <returns>返回数据集</returns> 
        public static DataSet GetReader(string sql)
        {
            OleDbDataAdapter da = new OleDbDataAdapter(sql, Connection);
            DataSet ds = new DataSet();
            da.Fill(ds, "UserInfo");
            connection.Close();
            return ds;
        }

        /// <summary> 
        /// 执行查询有参数SQL语句 
        /// </summary> 
        /// <param name="sql">SQL语句</param> 
        /// <param name="parameters">参数集合</param> 
        /// <returns>返回数据集</returns> 
        public static DataSet GetReader(string sql, params OleDbParameter[] parameters)
        {
            OleDbDataAdapter da = new OleDbDataAdapter(sql, Connection);
            da.SelectCommand.Parameters.AddRange(parameters);
            DataSet ds = new DataSet();
            da.Fill(ds);
            connection.Close();
            return ds;
        }
    }
}

　　

3. 创建实体类 创建ZYLT.cs类，专业擂台实体类。

　　

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace DTXT.Model
{
    /// <summary> 
    /// 专业擂台 
    /// </summary> 
    public class ZYLT
    {
        public string ID { get; set; }
        public string CATEGORY { get; set; }
        public string NUM { get; set; }
        public string QUESTION { get; set; }
        public string ANSWER { get; set; }

        /// <summary> 
        /// 将DataTable转换成List数据 
        /// </summary> 
        public static List<ZYLT> ToList(DataSet dataSet)
        {
            List<ZYLT> ZYLT_Lst = new List<ZYLT>();
            if (dataSet != null && dataSet.Tables.Count > 0)
            {
                foreach (DataRow row in dataSet.Tables[0].Rows)
                {
                    ZYLT zylt = new ZYLT();
                    if (dataSet.Tables[0].Columns.Contains("ID") && !Convert.IsDBNull(row["ID"]))
                        zylt.ID = row["ID"].ToString();

                    if (dataSet.Tables[0].Columns.Contains("CATEGORY") && !Convert.IsDBNull(row["CATEGORY"]))
                        zylt.CATEGORY = row["CATEGORY"].ToString();

                    if (dataSet.Tables[0].Columns.Contains("NUM") && !Convert.IsDBNull(row["NUM"]))
                        zylt.NUM = row["NUM"].ToString();

                    if (dataSet.Tables[0].Columns.Contains("QUESTION") && !Convert.IsDBNull(row["QUESTION"]))
                        zylt.QUESTION = row["QUESTION"].ToString();

                    if (dataSet.Tables[0].Columns.Contains("ANSWER") && !Convert.IsDBNull(row["ANSWER"]))
                        zylt.ANSWER = row["ANSWER"].ToString();

                    ZYLT_Lst.Add(zylt);
                }
            }
            return ZYLT_Lst;
        }
    }
}

　　

4、创建业务逻辑类 创建ZYLTBLL.cs类，专业擂台业务类。

　　

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using DTXT.Model;
using DTXT.DAL;
using System.Data.OleDb;

namespace DTXT.BLL
{
    /// <summary> 
    /// 专业擂台业务类 
    /// </summary> 
    public class ZYLTBLL
    {
        /// <summary> 
        /// 查询用户列表 
        /// </summary> 
        public List<ZYLT> GetZYLTList(string sqlWhere = "")
        {
            List<ZYLT> ZYLT_Lst = new List<ZYLT>();
            string sql = "SELECT * FROM [专业擂台$] WHERE 1=1 ";
            if (sqlWhere != "")
            {
                sql += "AND " + sqlWhere;
            }
            DataSet dateSet = ExcelHelper.GetReader(sql);
            ZYLT_Lst = ZYLT.ToList(dateSet);
            return ZYLT_Lst;
        }

        /// <summary> 
        /// 获取专业擂台总数 
        /// </summary> 
        public int GetZYLTCount()
        {
            int result = 0;
            string sql = "SELECT COUNT(*) FROM [专业擂台$]";
            result = ExcelHelper.GetScalar(sql);
            return result;
        }

        /// <summary> 
        /// 新增专业擂台
        /// </summary> 
        public int AddZYLT(ZYLT param)
        {
            int result = 0;
            string sql = "INSERT INTO [专业擂台$](ID,CATEGORY,NUM,QUESTION,ANSWER) VALUES(@ID,@CATEGORY,@NUM,@QUESTION,@ANSWER)";
            OleDbParameter[] oleDbParam = new OleDbParameter[] 
                { 
                    new OleDbParameter("@ID", param.ID), 
                    new OleDbParameter("@CATEGORY", param.CATEGORY), 
                    new OleDbParameter("@NUM", param.NUM), 
                    new OleDbParameter("@QUESTION",param.QUESTION), 
                    new OleDbParameter("@ANSWER",param.ANSWER) 
                };
            result = ExcelHelper.ExecuteCommand(sql, oleDbParam);
            return result;
        }

        /// <summary> 
        /// 修改专业擂台 在SET中要添加上 ID=@ID这个条件
        /// </summary> 
        public int UpdateZYLT(ZYLT param)
        {
            int result = 0;
            if (!string.IsNullOrEmpty(param.ID))
            {
                string sql = "UPDATE [专业擂台$] SET ID=@ID,CATEGORY=@CATEGORY,NUM=@NUM,QUESTION=@QUESTION,ANSWER=@ANSWER WHERE ID=@ID";
                OleDbParameter[] sqlParam = new OleDbParameter[] 
                { 
                    new OleDbParameter("@ID",param.ID), 
                    new OleDbParameter("@CATEGORY", param.CATEGORY), 
                    new OleDbParameter("@NUM", param.NUM), 
                    new OleDbParameter("@QUESTION",param.QUESTION), 
                    new OleDbParameter("@ANSWER",param.ANSWER) 
                };
                result = ExcelHelper.ExecuteCommand(sql, sqlParam);
            }
            return result;
        }

        /// <summary> 
        /// 删除专业擂台
        /// </summary> 
        public int DeleteZYLT(ZYLT param)
        {
            int result = 0;
            if (!string.IsNullOrEmpty(param.ID))
            {
                string sql = "DELETE FROM [专业擂台$] WHERE ID=@ID";
                OleDbParameter[] sqlParam = new OleDbParameter[] 
                { 
                     new OleDbParameter("@ID",param.ID), 
                 };
                result = ExcelHelper.ExecuteCommand(sql, sqlParam);
            }
            return result;
        }

        /// <summary> 
        /// 删除专业擂台
        /// </summary> 
        public int DeleteZYLTByID(string ID)
        {
            int result = 0;
            if (!string.IsNullOrEmpty(ID))
            {
                string sql = "DELETE [专业擂台$] WHERE ID=@ID";
                OleDbParameter[] sqlParam = new OleDbParameter[] 
                { 
                     new OleDbParameter("@ID",ID), 
                 };
                result = ExcelHelper.ExecuteCommand(sql, sqlParam);
            }
            return result;
        }

    }
}