快捷導航

C#操作Excel數(shù)據(jù)增刪改查示例

更新時間：2013年11月03日 17:26:28 作者：

Excel數(shù)據(jù)增刪改查我們可以使用c#進行操作，首先創(chuàng)建ExcelDB.xlsx文件，并添加兩張工作表，接下按照下面的操作步驟即可

C#操作Excel數(shù)據(jù)增刪改查。

首先創(chuàng)建ExcelDB.xlsx文件，并添加兩張工作表。

工作表1：

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

工作表2：

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

1、創(chuàng)建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> 
/// 執(zhí)行無參數(shù)的SQL語句 
/// </summary> 
/// <param name="sql">SQL語句</param> 
/// <returns>返回受SQL語句影響的行數(shù)</returns> 
public static int ExecuteCommand(string sql) 
{ 
OleDbCommand cmd = new OleDbCommand(sql, Connection); 
int result = cmd.ExecuteNonQuery(); 
connection.Close(); 
return result; 
} 

/// <summary> 
/// 執(zhí)行有參數(shù)的SQL語句 
/// </summary> 
/// <param name="sql">SQL語句</param> 
/// <param name="values">參數(shù)集合</param> 
/// <returns>返回受SQL語句影響的行數(shù)</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> 
/// 返回單個值無參數(shù)的SQL語句 
/// </summary> 
/// <param name="sql">SQL語句</param> 
/// <returns>返回受SQL語句查詢的行數(shù)</returns> 
public static int GetScalar(string sql) 
{ 
OleDbCommand cmd = new OleDbCommand(sql, Connection); 
int result = Convert.ToInt32(cmd.ExecuteScalar()); 
connection.Close(); 
return result; 
} 

/// <summary> 
/// 返回單個值有參數(shù)的SQL語句 
/// </summary> 
/// <param name="sql">SQL語句</param> 
/// <param name="parameters">參數(shù)集合</param> 
/// <returns>返回受SQL語句查詢的行數(shù)</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> 
/// 執(zhí)行查詢無參數(shù)SQL語句 
/// </summary> 
/// <param name="sql">SQL語句</param> 
/// <returns>返回數(shù)據(jù)集</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> 
/// 執(zhí)行查詢有參數(shù)SQL語句 
/// </summary> 
/// <param name="sql">SQL語句</param> 
/// <param name="parameters">參數(shù)集合</param> 
/// <returns>返回數(shù)據(jù)集</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、創(chuàng)建實體類

2.1 創(chuàng)建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轉(zhuǎn)換成List數(shù)據(jù) 
/// </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 創(chuàng)建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轉(zhuǎn)換成List數(shù)據(jù) 
/// </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、創(chuàng)建業(yè)務(wù)邏輯類

3.1 創(chuàng)建UserInfoBLL.cs類，用戶信息業(yè)務(wù)類。

復制代碼代碼如下:

 
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> 
/// 用戶信息業(yè)務(wù)類 
/// </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> 
/// 獲取用戶總數(shù) 
/// </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 創(chuàng)建OrderBLL.cs類，訂單業(yè)務(wù)類

復制代碼代碼如下:

 
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> 
/// 訂單業(yè)務(wù)類 
/// </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> 
/// 獲取訂單總數(shù) 
/// </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; 
} 
} 
} 