C#操作Excel数据增删改查(转)

C#操作Excel数据增删改查(转)

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

首先创建ExcelDB.xlsx文件，并添加两张工作表。
工作表1：
UserInfo表，字段：UserId、UserName、Age、Address、CreateTime。
工作表2：
Order表，字段：OrderNo、ProductName、Quantity、Money、SaleDate。
1、创建ExcelHelper.cs类，Excel文件处理类C#操作Excel数据增删改查(转)

using System;  

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

namespace MyStudy.DAL
{
///
/// Excel文件处理类
///
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\"";  

或
connectionString = “Provider=Microsoft.Jet.OLEDB.4.0;” + “Data Source=” + fileName + “;” + “Extended Properties=Excel 12.0;”;
}
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
{
///
/// 用户信息实体类
///
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
{
///
/// 订单实体类
///
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
{
///
/// 用户信息业务类
///
public class UserInfoBLL
{
///
/// 查询用户列表
///
public List GetUserList()
{
List userList = new List();
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
{
///
/// 订单业务类
///
public class OrderBLL
{
///
/// 查询订单列表
///
public List GetOrderList()
{
List orderList = new List();
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;  
    }    
}  

}