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

1、C#操作Excel數(shù)據(jù)增刪改查示例Excel數(shù)據(jù)增刪改查我們可以使用c#進(jìn)行操作，首先創(chuàng)建 ExcelDB.xlsx 文件，并添加兩張工作表，接下按照下面的 操作步驟即可C#操作Excel數(shù)據(jù)增刪改查。首先創(chuàng)建ExcelDB.xIsx文件， 并添加兩張工作表。 工作表 1： UserInfo 表，字段： UserId 、UserName、Age、Address、CreateTime。 工作表 2： Order表，字段：OrderNo、ProductName、Quantity、Money、SaleDate。1、創(chuàng)建 ExcelHelper.cs 類， Excel 文件處理類 復(fù)制代碼代碼如下 :

2、 using System; using System.Collections.Generic; usingSystem.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.

3、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;

4、 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;" + &quo

5、t;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 i

6、f (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ù) &

7、amp;lt;/returns> public static intExecuteCommand(string sql) OleDbCommand cmd = newOleDbCommand(sql, Connection); int result = cmd.ExecuteNonQuery(); connection.Close(); return result; / <summary> / 執(zhí)行有參數(shù)的 SQL 語句 / </summary> / <param name="sql"&am

8、p;gt;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, Connectio

9、n);cmd.Parameters.AddRange(values); int result = cmd.ExecuteNonQuery(); connection.Close(); return result; / <summary> / 返回單個值無參數(shù)的 SQL 語句 / </summary> / <param name="sql">SQL 語句</param> / <returns> 返回受 SQL 語句查詢的行數(shù)&lt

10、;/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&q

11、uot;>SQL 語句 </param> / <param name="parameters"> 參數(shù)集合 </param> / <returns> 返回受 SQL 語句查詢的行數(shù) </returns>public static int GetScalar(string sql, params OleDbParameter parameters) OleDbCommand cmd = new OleDbCommand(sq

12、l,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&

13、gt; 返回數(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&gt

14、; / <param name="sql">SQL 語句</param> / <param name="parameters"> 參數(shù)集合</param> / <returns> 返回數(shù)據(jù)集 </returns>public static DataSet GetReader(string sql, paramsOleDbParameter parameters) OleDbDataAdap

15、ter da = newOleDbDataAdapter(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 類，用戶信息 實體類。復(fù)制代碼代碼如下 : using System; using System.Collections.Generic; usingSystem.Linq; using System.Te

16、xt; using System.Data; namespaceMyStudy.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; / <summa

17、ry> / 將 DataTable轉(zhuǎn)換成 List 數(shù)據(jù) / </summary> public staticList<UserInfo> ToList(DataSet dataSet) List<UserInfo> userList = new List<UserInfo>();if (dataSet != null && dataSet.Tables.Count > 0) foreach (DataRow row in

18、dataSet.Tables0.Rows) UserInfo user = new UserInfo(); if (dataSet.Tables0.Columns.Contains("UserId") && !Convert.IsDBNull(row"UserId") user.UserId =Convert.ToInt32(row"UserId"); if (dataSet.Tables0.Columns.Contains("UserName") &&amp

19、; !Convert.IsDBNull(row"UserName") user.UserName = (string)row"UserName" if (dataSet.Tables0.Columns.Contains("Age") && !Convert.IsDBNull(row"Age") user.Age =Convert.ToInt32(row"Age"); if (dataSet.Tables0.Columns.Contains("Addres

20、s") && !Convert.IsDBNull(row"Address") user.Address = (string)row"Address" if/(dataSet.Tables0.Columns.Contains("CreateTime") && !Convert.IsDBNull(row"CreateTime") user.CreateTime =Convert.ToDateTime(row"CreateTime&quo

21、t;);userList.Add(user); return userList; 2.2創(chuàng)建 Order.cs類，訂單實體類。復(fù)制代碼代碼如下 : using System; using System.Collections.Generic; usingSystem.Linq; using System.Text; using System.Data; namespaceMyStudy.Model / <summary> /訂單實體類 / </summary> public class Order public string OrderN

22、o get; set; public string ProductName get; set; public int?Quantity get; set; public decimal? Money get; set; publicDateTime? SaleDate get; set; / <summary> / 將DataTable 轉(zhuǎn)換成 List 數(shù)據(jù) / </summary> public staticList<Order> ToList(DataSet dataSet) List<Or

23、der>orderList = new List<Order>(); if (dataSet != null && dataSet.Tables.Count > 0) foreach (DataRow row in dataSet.Tables0.Rows) Order order = new Order(); if (dataSet.Tables0.Columns.Contains("OrderNo") && !Convert.IsDBNull(row&

24、quot;OrderNo") order.OrderNo = (string)row"OrderNo" if(dataSet.Tables0.Columns.Contains("ProductName") && !Convert.IsDBNull(row"ProductName") order.ProductName = (string)row"ProductName" if (dataSet.Tables0.Columns.Contains("Quantity&

25、quot;) && !Convert.IsDBNull(row"Quantity") order.Quantity = Convert.ToInt32(row"Quantity"); if (dataSet.Tables0.Columns.Contains("Money") && !Convert.IsDBNull(row"Money") order.Money = Convert.ToDecimal(row"Money"); if

26、 (dataSet.Tables0.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ù)類。復(fù)制代碼代碼如下 : using System; using Syste

27、m.Collections.Generic; usingSystem.Linq; using System.Text; using System.Data; usingMyStudy.Model; using MyStudy.DAL; usingSystem.Data.OleDb; namespace MyStudy.BLL / <summary> / 用戶信息業(yè)務(wù)類 / </summary>public class UserInfoBLL / <summary> / 查詢用戶 列表 / </su

28、mmary> public List<UserInfo>GetUserList() List<UserInfo> userList = newList<UserInfo>(); string sql = "SELECT * FROM UserInfo$" DataSet dateSet = ExcelHelper.GetReader(sql);userList = UserInfo.ToList(dateSet); return userList; / <summ

29、ary> / 獲取用戶總數(shù) / </summary>public int GetUserCount() int result = 0; string sql = "SELECTCOUNT(*) FROM UserInfo$" result =ExcelHelper.GetScalar(sql); return result; / <summary>/ 新增用戶信息 / </summary> public intAddUserInfo(UserInfo param) int re

30、sult = 0; string sql = "INSERT INTO UserInfo$(UserId,UserName,Age,Address,CreateTime)VALUES(UserId,UserName,Age,Address,CreateTim e)" OleDbParameter oleDbParam = new OleDbParameter new OleDbParameter("UserId", param.UserId), newOleDbParameter("UserName", param.UserName)

31、, newOleDbParameter("Age", param.Age), newOleDbParameter("Address",param.Address), newOleDbParameter("CreateTime",param.CreateTime) ; result =ExcelHelper.ExecuteCommand(sql, oleDbParam); return result; / <summary> / 修改用戶信息 / </summary>public

32、int UpdateUserInfo(UserInfo param) int result = 0; if(param.UserId > 0) string sql = "UPDATE UserInfo$ SETUserName=UserName,Age=Age,Address=AddressWHERE UserId=UserId" OleDbParameter sqlParam = newOleDbParameter newOleDbParameter("UserId",param.UserId), newOleDbParameter(&

33、quot;UserName", param.UserName), newOleDbParameter("Age", param.Age), newOleDbParameter("Address",param.Address) ; result =ExcelHelper.ExecuteCommand(sql, sqlParam); return result; / <summary> / 刪除用戶信息 / </summary>public int DeleteUserInfo(UserInf

34、o param) int result = 0; if (param.UserId > 0) string sql = "DELETE UserInfo$WHERE UserId=UserId" OleDbParameter sqlParam = newOleDbParameter newOleDbParameter("UserId",param.UserId), ; result =ExcelHelper.ExecuteCommand(sql, sqlParam); return result; 3.2 創(chuàng)建 OrderBLL.cs 類，

35、訂單業(yè)務(wù)類 復(fù)制代碼代碼如下 : using System; using System.Collections.Generic; usingSystem.Linq; using System.Text; using System.Data; usingMyStudy.Model; using MyStudy.DAL; usingSystem.Data.OleDb; namespace MyStudy.BLL /<summary> / 訂單業(yè)務(wù)類 / </summary> public class OrderBLL / <su

36、mmary> / 查詢訂單列表 / </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

37、 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,S aleDate)" OleDbParameter oleDbParam = newOleDbParameter new OleDbParameter("OrderNo", param.OrderNo), new OleDbParameter(