C#操作Excel数据增删改查

C#操作Excel数据增删改查。

首先创建ExcelDB.xlsx文件，并添加两张工作表。

工作表1：

UserInfo表，字段：UserId、UserName、Age、Address、CreateTime。

工作表2：

Order表，字段：OrderNo、ProductName、Quantity、Money、SaleDate。

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

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

namespace MyStudy.DAL
{
    /// <summary>
    /// Excel文件处理类
    /// </summary>
    public class ExcelHelper
    {
        private static string fileName = AppDomain.CurrentDomain.SetupInformation.ApplicationBase + @"/ExcelFile/ExcelDB.xlsx";

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

2、 创建实体类

2.1 创建UserInfo.cs类，用户信息实体类。

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

namespace MyStudy.Model
{
    /// <summary>
    /// 用户信息实体类
    /// </summary>
    public class UserInfo
    {
        public int UserId { get; set; }
        public string UserName { get; set; }
        public int? Age { get; set; }
        public string Address { get; set; }
        public DateTime? CreateTime { get; set; }

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

                    if (dataSet.Tables[0].Columns.Contains("UserName") && !Convert.IsDBNull(row["UserName"]))
                        user.UserName = (string)row["UserName"];

                    if (dataSet.Tables[0].Columns.Contains("Age") && !Convert.IsDBNull(row["Age"]))
                        user.Age = Convert.ToInt32(row["Age"]);

                    if (dataSet.Tables[0].Columns.Contains("Address") && !Convert.IsDBNull(row["Address"]))
                        user.Address = (string)row["Address"];

                    if (dataSet.Tables[0].Columns.Contains("CreateTime") && !Convert.IsDBNull(row["CreateTime"]))
                        user.CreateTime = Convert.ToDateTime(row["CreateTime"]);

                    userList.Add(user);
                }
            }
            return userList;
        }  
    }
}

2.2 创建Order.cs类，订单实体类。

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

namespace MyStudy.Model
{
    /// <summary>
    /// 订单实体类
    /// </summary>
    public class Order
    {
        public string OrderNo { get; set; }
        public string ProductName { get; set; }
        public int? Quantity { get; set; }
        public decimal? Money { get; set; }
        public DateTime? SaleDate { get; set; }

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

                    if (dataSet.Tables[0].Columns.Contains("ProductName") && !Convert.IsDBNull(row["ProductName"]))
                        order.ProductName = (string)row["ProductName"];

                    if (dataSet.Tables[0].Columns.Contains("Quantity") && !Convert.IsDBNull(row["Quantity"]))
                        order.Quantity = Convert.ToInt32(row["Quantity"]);

                    if (dataSet.Tables[0].Columns.Contains("Money") && !Convert.IsDBNull(row["Money"]))
                        order.Money = Convert.ToDecimal(row["Money"]);

                    if (dataSet.Tables[0].Columns.Contains("SaleDate") && !Convert.IsDBNull(row["SaleDate"]))
                        order.SaleDate = Convert.ToDateTime(row["SaleDate"]);

                    orderList.Add(order);
                }
            }
            return orderList;
        }
    }
}

3、创建业务逻辑类

3.1 创建UserInfoBLL.cs类，用户信息业务类。

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

namespace MyStudy.BLL
{
    /// <summary>
    /// 用户信息业务类
    /// </summary>
    public class UserInfoBLL
    {
        /// <summary>  
        /// 查询用户列表  
        /// </summary>  
        public List<UserInfo> GetUserList()
        {
            List<UserInfo> userList = new List<UserInfo>();
            string sql = "SELECT * FROM [UserInfo$]";
            DataSet dateSet = ExcelHelper.GetReader(sql);
            userList = UserInfo.ToList(dateSet);
            return userList;
        }

        /// <summary>  
        /// 获取用户总数  
        /// </summary>  
        public int GetUserCount()
        {
            int result = 0;
            string sql = "SELECT COUNT(*) FROM [UserInfo$]";
            result = ExcelHelper.GetScalar(sql);
            return result;
        }

        /// <summary>  
        /// 新增用户信息  
        /// </summary>  
        public int AddUserInfo(UserInfo param)
        {
            int result = 0;
            string sql = "INSERT INTO [UserInfo$](UserId,UserName,Age,Address,CreateTime) VALUES(@UserId,@UserName,@Age,@Address,@CreateTime)";
            OleDbParameter[] oleDbParam = new OleDbParameter[]  
            {  
                new OleDbParameter("@UserId", param.UserId),   
                new OleDbParameter("@UserName", param.UserName),   
                new OleDbParameter("@Age", param.Age),  
                new OleDbParameter("@Address",param.Address),
                new OleDbParameter("@CreateTime",param.CreateTime)  
            };
            result = ExcelHelper.ExecuteCommand(sql, oleDbParam);
            return result;
        }

        /// <summary>  
        /// 修改用户信息  
        /// </summary>  
        public int UpdateUserInfo(UserInfo param)
        {
            int result = 0;
            if (param.UserId > 0)
            {
                string sql = "UPDATE [UserInfo$] SET UserName=@UserName,Age=@Age,Address=@Address WHERE UserId=@UserId";
                OleDbParameter[] sqlParam = new OleDbParameter[]  
                {  
                    new OleDbParameter("@UserId",param.UserId),  
                    new OleDbParameter("@UserName", param.UserName),   
                    new OleDbParameter("@Age", param.Age),  
                    new OleDbParameter("@Address",param.Address)  
                };
                result = ExcelHelper.ExecuteCommand(sql, sqlParam);
            }
            return result;
        }

        /// <summary>  
        /// 删除用户信息  
        /// </summary>  
        public int DeleteUserInfo(UserInfo param)
        {
            int result = 0;
            if (param.UserId > 0)
            {
                string sql = "DELETE [UserInfo$] WHERE UserId=@UserId";
                OleDbParameter[] sqlParam = new OleDbParameter[]  
                {  
                    new OleDbParameter("@UserId",param.UserId),  
                };
                result = ExcelHelper.ExecuteCommand(sql, sqlParam);
            }
            return result;
        }  
    }
}

3.2 创建OrderBLL.cs类，订单业务类

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

namespace MyStudy.BLL
{
    /// <summary>
    /// 订单业务类
    /// </summary>
    public class OrderBLL
    {
        /// <summary>  
        /// 查询订单列表  
        /// </summary>  
        public List<Order> GetOrderList()
        {
            List<Order> orderList = new List<Order>();
            string sql = "SELECT * FROM [Order$]";
            DataSet dateSet = ExcelHelper.GetReader(sql);
            orderList = Order.ToList(dateSet);
            return orderList;
        }

        /// <summary>  
        /// 获取订单总数  
        /// </summary>  
        public int GetOrderCount()
        {
            int result = 0;
            string sql = "SELECT COUNT(*) FROM [Order$]";
            result = ExcelHelper.GetScalar(sql);
            return result;
        }

        /// <summary>  
        /// 新增订单  
        /// </summary>  
        public int AddOrder(Order param)
        {
            int result = 0;
            string sql = "INSERT INTO [Order$](OrderNo,ProductName,Quantity,Money,SaleDate) VALUES(@OrderNo,@ProductName,@Quantity,@Money,@SaleDate)";
            OleDbParameter[] oleDbParam = new OleDbParameter[]  
            {  
                new OleDbParameter("@OrderNo", param.OrderNo),   
                new OleDbParameter("@ProductName", param.ProductName),   
                new OleDbParameter("@Quantity", param.Quantity),  
                new OleDbParameter("@Money",param.Money),
                new OleDbParameter("@SaleDate",param.SaleDate)  
            };
            result = ExcelHelper.ExecuteCommand(sql, oleDbParam);
            return result;
        }

        /// <summary>  
        /// 修改订单  
        /// </summary>  
        public int UpdateOrder(Order param)
        {
            int result = 0;
            if (!String.IsNullOrEmpty(param.OrderNo))
            {
                string sql = "UPDATE [Order$] SET ProductName=@ProductName,Quantity=@Quantity,Money=@Money WHERE OrderNo=@OrderNo";
                OleDbParameter[] sqlParam = new OleDbParameter[]  
                {  
                    new OleDbParameter("@OrderNo",param.OrderNo),  
                    new OleDbParameter("@ProductName",param.ProductName),  
                    new OleDbParameter("@Quantity", param.Quantity),   
                    new OleDbParameter("@Money", param.Money)   
                };
                result = ExcelHelper.ExecuteCommand(sql, sqlParam);
            }
            return result;
        }

        /// <summary>  
        /// 删除订单  
        /// </summary>  
        public int DeleteOrder(Order param)
        {
            int result = 0;
            if (!String.IsNullOrEmpty(param.OrderNo))
            {
                string sql = "DELETE [Order$] WHERE OrderNo=@OrderNo";
                OleDbParameter[] sqlParam = new OleDbParameter[]  
                {  
                    new OleDbParameter("@OrderNo",param.OrderNo),  
                };
                result = ExcelHelper.ExecuteCommand(sql, sqlParam);
            }
            return result;
        }  
    }
}