C#操作Excel数据增删改查

系统管理员 2021-09-18 13:58 550阅读 0赞

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