Example of C# operation Excel data addition, deletion, modification and search

C# operation Excel data addition, deletion, modification and search.

First create the file and add two worksheets.

Worksheet 1:

UserInfo table, fields: UserId, UserName, Age, Address, CreateTime.

Worksheet 2:

Order table, fields: OrderNo, ProductName, Quantity, Money, SaleDate.

1. Create a class, Excel file processing class

Copy the codeThe code is as follows:

 
using System; 
using ; 
using ; 
using ; 
using ; 
using ; 

namespace  
{ 
/// <summary> 
/// Excel file processing class
/// </summary> 
public class ExcelHelper 
{ 
private static string fileName =  + @"/ExcelFile/"; 

private static OleDbConnection connection; 
public static OleDbConnection Connection 
{ 
get 
{ 
string connectionString = ""; 
string fileType = (fileName); 
if ((fileType)) return null; 
if (fileType == ".xls") 
{ 
connectionString = "Provider=.4.0;" + "Data Source=" + fileName + ";" + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=2\""; 
} 
else 
{ 
connectionString = "Provider=.12.0;" + "Data Source=" + fileName + ";" + ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=2\""; 
} 
if (connection == null) 
{ 
connection = new OleDbConnection(connectionString); 
(); 
} 
else if ( == ) 
{ 
(); 
} 
else if ( == ) 
{ 
(); 
(); 
} 
return connection; 
} 
} 

/// <summary> 
/// Execute SQL statements without parameters
/// </summary> 
/// <param name="sql">SQL statement</param>
/// <returns>Returns the number of rows affected by SQL statements</returns>
public static int ExecuteCommand(string sql) 
{ 
OleDbCommand cmd = new OleDbCommand(sql, Connection); 
int result = (); 
(); 
return result; 
} 

/// <summary> 
/// Execute SQL statements with parameters
/// </summary> 
/// <param name="sql">SQL statement</param>
/// <param name="values">parameter set</param>
/// <returns>Returns the number of rows affected by SQL statements</returns>
public static int ExecuteCommand(string sql, params OleDbParameter[] values) 
{ 
OleDbCommand cmd = new OleDbCommand(sql, Connection); 
(values); 
int result = (); 
(); 
return result; 
} 

/// <summary> 
/// Return a single value without parameters SQL statement
/// </summary> 
/// <param name="sql">SQL statement</param>
/// <returns>Returns the number of rows queried by SQL statement</returns>
public static int GetScalar(string sql) 
{ 
OleDbCommand cmd = new OleDbCommand(sql, Connection); 
int result = Convert.ToInt32(()); 
(); 
return result; 
} 

/// <summary> 
/// Return a SQL statement with a single value with parameters
/// </summary> 
/// <param name="sql">SQL statement</param>
/// <param name="parameters">parameter collection</param>
/// <returns>Returns the number of rows queried by SQL statement</returns>
public static int GetScalar(string sql, params OleDbParameter[] parameters) 
{ 
OleDbCommand cmd = new OleDbCommand(sql, Connection); 
(parameters); 
int result = Convert.ToInt32(()); 
(); 
return result; 
} 

/// <summary> 
/// Execute query without parameters SQL statement
/// </summary> 
/// <param name="sql">SQL statement</param>
/// <returns>Return the dataset</returns>
public static DataSet GetReader(string sql) 
{ 
OleDbDataAdapter da = new OleDbDataAdapter(sql, Connection); 
DataSet ds = new DataSet(); 
(ds, "UserInfo"); 
(); 
return ds; 
} 

/// <summary> 
/// Execute query with parameter SQL statement
/// </summary> 
/// <param name="sql">SQL statement</param>
/// <param name="parameters">parameter collection</param>
/// <returns>Return the dataset</returns>
public static DataSet GetReader(string sql, params OleDbParameter[] parameters) 
{ 
OleDbDataAdapter da = new OleDbDataAdapter(sql, Connection); 
(parameters); 
DataSet ds = new DataSet(); 
(ds); 
(); 
return ds; 
} 
} 
} 

2. Create entity class

2.1 Create a class, user information entity class.

Copy the codeThe code is as follows:

 
using System; 
using ; 
using ; 
using ; 
using ; 

namespace  
{ 
/// <summary> 
/// User information entity class
/// </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> 
/// Convert DataTable to List data
/// </summary> 
public static List<UserInfo> ToList(DataSet dataSet) 
{ 
List<UserInfo> userList = new List<UserInfo>(); 
if (dataSet != null &&  > 0) 
{ 
foreach (DataRow row in [0].Rows) 
{ 
UserInfo user = new UserInfo(); 
if ([0].("UserId") && !(row["UserId"])) 
 = Convert.ToInt32(row["UserId"]); 

if ([0].("UserName") && !(row["UserName"])) 
 = (string)row["UserName"]; 

if ([0].("Age") && !(row["Age"])) 
 = Convert.ToInt32(row["Age"]); 

if ([0].("Address") && !(row["Address"])) 
 = (string)row["Address"]; 

if ([0].("CreateTime") && !(row["CreateTime"])) 
 = (row["CreateTime"]); 

(user); 
} 
} 
return userList; 
} 
} 
} 

2.2 Create a class, order entity class.

Copy the codeThe code is as follows:

 
using System; 
using ; 
using ; 
using ; 
using ; 

namespace  
{ 
/// <summary> 
/// Order entity class
/// </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> 
/// Convert DataTable to List data
/// </summary> 
public static List<Order> ToList(DataSet dataSet) 
{ 
List<Order> orderList = new List<Order>(); 
if (dataSet != null &&  > 0) 
{ 
foreach (DataRow row in [0].Rows) 
{ 
Order order = new Order(); 
if ([0].("OrderNo") && !(row["OrderNo"])) 
 = (string)row["OrderNo"]; 

if ([0].("ProductName") && !(row["ProductName"])) 
 = (string)row["ProductName"]; 

if ([0].("Quantity") && !(row["Quantity"])) 
 = Convert.ToInt32(row["Quantity"]); 

if ([0].("Money") && !(row["Money"])) 
 = (row["Money"]); 

if ([0].("SaleDate") && !(row["SaleDate"])) 
 = (row["SaleDate"]); 

(order); 
} 
} 
return orderList; 
} 
} 
} 

3. Create a business logic class

3.1 Create class, user information business class.

Copy the codeThe code is as follows:

 
using System; 
using ; 
using ; 
using ; 
using ; 
using ; 
using ; 
using ; 

namespace  
{ 
/// <summary> 
/// User information business category
/// </summary> 
public class UserInfoBLL 
{ 
/// <summary> 
/// Query user list
/// </summary> 
public List<UserInfo> GetUserList() 
{ 
List<UserInfo> userList = new List<UserInfo>(); 
string sql = "SELECT * FROM [UserInfo$]"; 
DataSet dateSet = (sql); 
userList = (dateSet); 
return userList; 
} 

/// <summary> 
/// Get the total number of users
/// </summary> 
public int GetUserCount() 
{ 
int result = 0; 
string sql = "SELECT COUNT(*) FROM [UserInfo$]"; 
result = (sql); 
return result; 
} 

/// <summary> 
/// Add new user information
/// </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", ), 
new OleDbParameter("@UserName", ), 
new OleDbParameter("@Age", ), 
new OleDbParameter("@Address",), 
new OleDbParameter("@CreateTime",) 
}; 
result = (sql, oleDbParam); 
return result; 
} 

/// <summary> 
/// Modify user information
/// </summary> 
public int UpdateUserInfo(UserInfo param) 
{ 
int result = 0; 
if ( > 0) 
{ 
string sql = "UPDATE [UserInfo$] SET UserName=@UserName,Age=@Age,Address=@Address WHERE UserId=@UserId"; 
OleDbParameter[] sqlParam = new OleDbParameter[] 
{ 
new OleDbParameter("@UserId",), 
new OleDbParameter("@UserName", ), 
new OleDbParameter("@Age", ), 
new OleDbParameter("@Address",) 
}; 
result = (sql, sqlParam); 
} 
return result; 
} 

/// <summary> 
/// Delete user information
/// </summary> 
public int DeleteUserInfo(UserInfo param) 
{ 
int result = 0; 
if ( > 0) 
{ 
string sql = "DELETE [UserInfo$] WHERE UserId=@UserId"; 
OleDbParameter[] sqlParam = new OleDbParameter[] 
{ 
new OleDbParameter("@UserId",), 
}; 
result = (sql, sqlParam); 
} 
return result; 
} 
} 
} 

3.2 Creation class, order business class

Copy the codeThe code is as follows:

 
using System; 
using ; 
using ; 
using ; 
using ; 
using ; 
using ; 
using ; 

namespace  
{ 
/// <summary> 
/// Order business category
/// </summary> 
public class OrderBLL 
{ 
/// <summary> 
/// Query the order list
/// </summary> 
public List<Order> GetOrderList() 
{ 
List<Order> orderList = new List<Order>(); 
string sql = "SELECT * FROM [Order$]"; 
DataSet dateSet = (sql); 
orderList = (dateSet); 
return orderList; 
} 

/// <summary> 
/// Get the total number of orders
/// </summary> 
public int GetOrderCount() 
{ 
int result = 0; 
string sql = "SELECT COUNT(*) FROM [Order$]"; 
result = (sql); 
return result; 
} 

/// <summary> 
/// Add order
/// </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", ), 
new OleDbParameter("@ProductName", ), 
new OleDbParameter("@Quantity", ), 
new OleDbParameter("@Money",), 
new OleDbParameter("@SaleDate",) 
}; 
result = (sql, oleDbParam); 
return result; 
} 

/// <summary> 
/// Modify order
/// </summary> 
public int UpdateOrder(Order param) 
{ 
int result = 0; 
if (!()) 
{ 
string sql = "UPDATE [Order$] SET ProductName=@ProductName,Quantity=@Quantity,Money=@Money WHERE OrderNo=@OrderNo"; 
OleDbParameter[] sqlParam = new OleDbParameter[] 
{ 
new OleDbParameter("@OrderNo",), 
new OleDbParameter("@ProductName",), 
new OleDbParameter("@Quantity", ), 
new OleDbParameter("@Money", ) 
}; 
result = (sql, sqlParam); 
} 
return result; 
} 

/// <summary> 
/// Delete the order
/// </summary> 
public int DeleteOrder(Order param) 
{ 
int result = 0; 
if (!()) 
{ 
string sql = "DELETE [Order$] WHERE OrderNo=@OrderNo"; 
OleDbParameter[] sqlParam = new OleDbParameter[] 
{ 
new OleDbParameter("@OrderNo",), 
}; 
result = (sql, sqlParam); 
} 
return result; 
} 
} 
} 