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