C#操作Ecel数据增删改查示例

1、C#操作 Excel 数据增删改查示例Excel 数据增删改查我们可以使用c#进行操作，首先创建 ExcelDB.xlsx 文件，并添加两张工作表，接下按照下面的操作步骤即可C#操作 Excel 数据增删改查。 首先创建 ExcelDB.xlsx 文件，并添加两张工作表。 工作表 1： UserInfo 表，字段： UserId 、UserName、 Age、 Address、 CreateTime。 工作表 2： Order表，字段：OrderNo 、ProductName、Quantity 、Money 、SaleDate。1、创建 ExcelHelper.cs 类， Excel 文件处理

2、类复制代码代码如下: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 staticstring fileName =AppDomain.Curr

3、entDomain.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) re

4、turn null; if (fileType =".xls") connectionString ="Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" +fileName + "" + "Extended Properties="Excel8.0;HDR=YES;IMEX=2"" else connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;"

5、+ "Data Source=" + fileName + "" + "Extended Properties="Excel12.0;HDR=YES;IMEX=2"" if (connection = null) connection = new OleDbConnection(connectionString); connection.Open(); else if (connection.State = System.Data.ConnectionState.Closed) connection.Open();

6、 else if (connection.State = System.Data.ConnectionState.Broken) connection.Close(); connection.Open(); return connection; / <summary>/ 执行无参数的 SQL 语句 / </summary> / <paramname="sql">SQL语句 </param> / <returns>返回受 SQL 语句影响

7、的行数</returns> public static intExecuteCommand(string sql) OleDbCommand cmd = newOleDbCommand(sql, Connection); int result =cmd.ExecuteNonQuery(); connection.Close(); return result; /<summary> /执行有参数的SQL 语句/</summary> / <param name="sql"&

8、gt;SQL语句</param> / <param name="values"> 参数集合 </param> / <returns> 返回受 SQL 语句影响的行数 </returns> public static int ExecuteCommand(string sql, params OleDbParameter values) OleDbCommand cmd =new OleDbCommand(sql, Connection)

9、; cmd.Parameters.AddRange(values); int result = cmd.ExecuteNonQuery(); connection.Close(); return result; / <summary> / 返回单个值无参数的 SQL 语句 /</summary> / <param name="sql">SQL语句</param> / <returns>返回受 SQL 语句查询的行数</re

10、turns> 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"&

11、amp;gt;SQL语句 </param> /<param name="parameters"> 参数集合 </param> / <returns> 返回受 SQL 语句查询的行数 </returns> public static int GetScalar(string sql, params OleDbParameter parameters) OleDbCommand cmd = new OleDbCommand(sql, Con

12、nection); cmd.Parameters.AddRange(parameters); int result = Convert.ToInt32(cmd.ExecuteScalar(); connection.Close();return result; / <summary> /执行查询无参数SQL语句/ </summary> / <param name="sql">SQL语句 </param> / <returns>返回数据集

13、</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> / <p

14、aram name="sql">SQL语句</param> / <param name="parameters"> 参数集合 </param> / <returns> 返回数据集 </returns> public static DataSet GetReader(string sql, params OleDbParameter parameters) OleDbDataAdapter da = ne

15、w OleDbDataAdapter(sql, Connection);da.SelectCommand.Parameters.AddRange(parameters); DataSet ds = new DataSet(); da.Fill(ds); connection.Close(); returnds; 2 、 创建实体类2.1 创建 UserInfo.cs 类，用户信息实体类。复制代码代码如下:using System; using System.Collections.Generic; using System.Linq; using System.Text; using Syst

16、em.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; / <summary> /将 D

17、ataTable转换成 List 数据/ </summary> public staticList<UserInfo> ToList(DataSet dataSet) List<UserInfo> userList = new List<UserInfo>();if (dataSet != null && dataSet.Tables.Count > 0) foreach (DataRow row in dataSet.Tables0.R

18、ows) 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") && !Convert.IsDBNu

19、ll(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("Address") &amp

20、;& !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");userList.Add(use

21、r); return userList; 2.2创建/Order.cs类，订单实体类。复制代码代码如下:using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data; namespaceMyStudy.Model / <summary> /订单实体类/</summary> public class Order public string OrderNo get; set; public stri

22、ng ProductName get; set; public int? Quantity get; set; public decimal? Money get; set; publicDateTime? SaleDate get; set; / <summary> / 将 DataTable 转换成 List 数据 / </summary> public static List<Order> ToList(DataSet dataSet) List<Order> orderLis

23、t = 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"OrderNo")

24、 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") &&am

25、p;amp; !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 (dataSet.Tables0.Co

26、lumns.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; us

27、ing 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&

28、;lt;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> / 获取用户总数 / &

29、amp;lt;/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

30、 ="INSERT INTOUserInfo$(UserId,UserName,Age,Address,CreateTime) VALUES(UserId,UserName,Age,Address,CreateTim e)" OleDbParameter oleDbParam = new OleDbParameter new OleDbParameter("UserId", param.UserId), new OleDbParameter("UserName", param.UserName), newOleDbParameter(

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

32、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("UserName"

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

34、 0; if(param.UserId > 0) string sql = "DELETE UserInfo$WHERE UserId=UserId" OleDbParameter sqlParam = newOleDbParameter newOleDbParameter("UserId",param.UserId), ; result =ExcelHelper.ExecuteCommand(sql, sqlParam); returnresult; 3.2创建 OrderBLL.cs 类，订单业务类复制代码代码如下:using Syst

35、em; 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> publicclass OrderBLL / <summary> /查询订单列表/&

36、lt;/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

37、> / 获取订单总数 / </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 INTOOrder$(OrderNo,ProductName,Quantity,Money,SaleDate) VALUES(OrderNo,ProductName,Quantity,Money,S aleDate)" OleDbParameter oleDbParam = newOleDbParameter new OleDbParameter("OrderNo",param.OrderNo), new OleDbParameter(&qu