C#操作Excel数据增删改查示例
2023-06-13 09:15:07 时间
C#操作Excel数据增删改查。
首先创建ExcelDB.xlsx文件，并添加两张工作表。
工作表1：
UserInfo表，字段：UserId、UserName、Age、Address、CreateTime。
工作表2：
Order表，字段：OrderNo、ProductName、Quantity、Money、SaleDate。
1、创建ExcelHelper.cs类，Excel文件处理类
复制代码代码如下:
首先创建ExcelDB.xlsx文件，并添加两张工作表。
工作表1：
UserInfo表，字段：UserId、UserName、Age、Address、CreateTime。
工作表2：
Order表，字段：OrderNo、ProductName、Quantity、Money、SaleDate。
1、创建ExcelHelper.cs类，Excel文件处理类
usingSystem;
usingSystem.Collections.Generic;
usingSystem.Linq;
usingSystem.Text;
usingSystem.Data.OleDb;
usingSystem.Data;
namespaceMyStudy.DAL
{
///<summary>
///Excel文件处理类
///</summary>
publicclassExcelHelper
{
privatestaticstringfileName=AppDomain.CurrentDomain.SetupInformation.ApplicationBase+@"/ExcelFile/ExcelDB.xlsx";
privatestaticOleDbConnectionconnection;
publicstaticOleDbConnectionConnection
{
get
{
stringconnectionString="";
stringfileType=System.IO.Path.GetExtension(fileName);
if(string.IsNullOrEmpty(fileType))returnnull;
if(fileType==".xls")
{
connectionString="Provider=Microsoft.Jet.OLEDB.4.0;"+"DataSource="+fileName+";"+";ExtendedProperties=\"Excel8.0;HDR=YES;IMEX=2\"";
}
else
{
connectionString="Provider=Microsoft.ACE.OLEDB.12.0;"+"DataSource="+fileName+";"+";ExtendedProperties=\"Excel12.0;HDR=YES;IMEX=2\"";
}
if(connection==null)
{
connection=newOleDbConnection(connectionString);
connection.Open();
}
elseif(connection.State==System.Data.ConnectionState.Closed)
{
connection.Open();
}
elseif(connection.State==System.Data.ConnectionState.Broken)
{
connection.Close();
connection.Open();
}
returnconnection;
}
}
///<summary>
///执行无参数的SQL语句
///</summary>
///<paramname="sql">SQL语句</param>
///<returns>返回受SQL语句影响的行数</returns>
publicstaticintExecuteCommand(stringsql)
{
OleDbCommandcmd=newOleDbCommand(sql,Connection);
intresult=cmd.ExecuteNonQuery();
connection.Close();
returnresult;
}
///<summary>
///执行有参数的SQL语句
///</summary>
///<paramname="sql">SQL语句</param>
///<paramname="values">参数集合</param>
///<returns>返回受SQL语句影响的行数</returns>
publicstaticintExecuteCommand(stringsql,paramsOleDbParameter[]values)
{
OleDbCommandcmd=newOleDbCommand(sql,Connection);
cmd.Parameters.AddRange(values);
intresult=cmd.ExecuteNonQuery();
connection.Close();
returnresult;
}
///<summary>
///返回单个值无参数的SQL语句
///</summary>
///<paramname="sql">SQL语句</param>
///<returns>返回受SQL语句查询的行数</returns>
publicstaticintGetScalar(stringsql)
{
OleDbCommandcmd=newOleDbCommand(sql,Connection);
intresult=Convert.ToInt32(cmd.ExecuteScalar());
connection.Close();
returnresult;
}
///<summary>
///返回单个值有参数的SQL语句
///</summary>
///<paramname="sql">SQL语句</param>
///<paramname="parameters">参数集合</param>
///<returns>返回受SQL语句查询的行数</returns>
publicstaticintGetScalar(stringsql,paramsOleDbParameter[]parameters)
{
OleDbCommandcmd=newOleDbCommand(sql,Connection);
cmd.Parameters.AddRange(parameters);
intresult=Convert.ToInt32(cmd.ExecuteScalar());
connection.Close();
returnresult;
}
///<summary>
///执行查询无参数SQL语句
///</summary>
///<paramname="sql">SQL语句</param>
///<returns>返回数据集</returns>
publicstaticDataSetGetReader(stringsql)
{
OleDbDataAdapterda=newOleDbDataAdapter(sql,Connection);
DataSetds=newDataSet();
da.Fill(ds,"UserInfo");
connection.Close();
returnds;
}
///<summary>
///执行查询有参数SQL语句
///</summary>
///<paramname="sql">SQL语句</param>
///<paramname="parameters">参数集合</param>
///<returns>返回数据集</returns>
publicstaticDataSetGetReader(stringsql,paramsOleDbParameter[]parameters)
{
OleDbDataAdapterda=newOleDbDataAdapter(sql,Connection);
da.SelectCommand.Parameters.AddRange(parameters);
DataSetds=newDataSet();
da.Fill(ds);
connection.Close();
returnds;
}
}
}
2、创建实体类
2.1创建UserInfo.cs类，用户信息实体类。
usingSystem;
usingSystem.Collections.Generic;
usingSystem.Linq;
usingSystem.Text;
usingSystem.Data;
namespaceMyStudy.Model
{
///<summary>
///用户信息实体类
///</summary>
publicclassUserInfo
{
publicintUserId{get;set;}
publicstringUserName{get;set;}
publicint?Age{get;set;}
publicstringAddress{get;set;}
publicDateTime?CreateTime{get;set;}
///<summary>
///将DataTable转换成List数据
///</summary>
publicstaticList<UserInfo>ToList(DataSetdataSet)
{
List<UserInfo>userList=newList<UserInfo>();
if(dataSet!=null&&dataSet.Tables.Count>0)
{
foreach(DataRowrowindataSet.Tables[0].Rows)
{
UserInfouser=newUserInfo();
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);
}
}
returnuserList;
}
}
}
2.2创建Order.cs类，订单实体类。
usingSystem;
usingSystem.Collections.Generic;
usingSystem.Linq;
usingSystem.Text;
usingSystem.Data;
namespaceMyStudy.Model
{
///<summary>
///订单实体类
///</summary>
publicclassOrder
{
publicstringOrderNo{get;set;}
publicstringProductName{get;set;}
publicint?Quantity{get;set;}
publicdecimal?Money{get;set;}
publicDateTime?SaleDate{get;set;}
///<summary>
///将DataTable转换成List数据
///</summary>
publicstaticList<Order>ToList(DataSetdataSet)
{
List<Order>orderList=newList<Order>();
if(dataSet!=null&&dataSet.Tables.Count>0)
{
foreach(DataRowrowindataSet.Tables[0].Rows)
{
Orderorder=newOrder();
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);
}
}
returnorderList;
}
}
}
3、创建业务逻辑类
3.1创建UserInfoBLL.cs类，用户信息业务类。
usingSystem;
usingSystem.Collections.Generic;
usingSystem.Linq;
usingSystem.Text;
usingSystem.Data;
usingMyStudy.Model;
usingMyStudy.DAL;
usingSystem.Data.OleDb;
namespaceMyStudy.BLL
{
///<summary>
///用户信息业务类
///</summary>
publicclassUserInfoBLL
{
///<summary>
///查询用户列表
///</summary>
publicList<UserInfo>GetUserList()
{
List<UserInfo>userList=newList<UserInfo>();
stringsql="SELECT*FROM[UserInfo$]";
DataSetdateSet=ExcelHelper.GetReader(sql);
userList=UserInfo.ToList(dateSet);
returnuserList;
}
///<summary>
///获取用户总数
///</summary>
publicintGetUserCount()
{
intresult=0;
stringsql="SELECTCOUNT(*)FROM[UserInfo$]";
result=ExcelHelper.GetScalar(sql);
returnresult;
}
///<summary>
///新增用户信息
///</summary>
publicintAddUserInfo(UserInfoparam)
{
intresult=0;
stringsql="INSERTINTO[UserInfo$](UserId,UserName,Age,Address,CreateTime)VALUES(@UserId,@UserName,@Age,@Address,@CreateTime)";
OleDbParameter[]oleDbParam=newOleDbParameter[]
{
newOleDbParameter("@UserId",param.UserId),
newOleDbParameter("@UserName",param.UserName),
newOleDbParameter("@Age",param.Age),
newOleDbParameter("@Address",param.Address),
newOleDbParameter("@CreateTime",param.CreateTime)
};
result=ExcelHelper.ExecuteCommand(sql,oleDbParam);
returnresult;
}
///<summary>
///修改用户信息
///</summary>
publicintUpdateUserInfo(UserInfoparam)
{
intresult=0;
if(param.UserId>0)
{
stringsql="UPDATE[UserInfo$]SETUserName=@UserName,Age=@Age,Address=@AddressWHEREUserId=@UserId";
OleDbParameter[]sqlParam=newOleDbParameter[]
{
newOleDbParameter("@UserId",param.UserId),
newOleDbParameter("@UserName",param.UserName),
newOleDbParameter("@Age",param.Age),
newOleDbParameter("@Address",param.Address)
};
result=ExcelHelper.ExecuteCommand(sql,sqlParam);
}
returnresult;
}
///<summary>
///删除用户信息
///</summary>
publicintDeleteUserInfo(UserInfoparam)
{
intresult=0;
if(param.UserId>0)
{
stringsql="DELETE[UserInfo$]WHEREUserId=@UserId";
OleDbParameter[]sqlParam=newOleDbParameter[]
{
newOleDbParameter("@UserId",param.UserId),
};
result=ExcelHelper.ExecuteCommand(sql,sqlParam);
}
returnresult;
}
}
}
3.2创建OrderBLL.cs类，订单业务类
usingSystem;
usingSystem.Collections.Generic;
usingSystem.Linq;
usingSystem.Text;
usingSystem.Data;
usingMyStudy.Model;
usingMyStudy.DAL;
usingSystem.Data.OleDb;
namespaceMyStudy.BLL
{
///<summary>
///订单业务类
///</summary>
publicclassOrderBLL
{
///<summary>
///查询订单列表
///</summary>
publicList<Order>GetOrderList()
{
List<Order>orderList=newList<Order>();
stringsql="SELECT*FROM[Order$]";
DataSetdateSet=ExcelHelper.GetReader(sql);
orderList=Order.ToList(dateSet);
returnorderList;
}
///<summary>
///获取订单总数
///</summary>
publicintGetOrderCount()
{
intresult=0;
stringsql="SELECTCOUNT(*)FROM[Order$]";
result=ExcelHelper.GetScalar(sql);
returnresult;
}
///<summary>
///新增订单
///</summary>
publicintAddOrder(Orderparam)
{
intresult=0;
stringsql="INSERTINTO[Order$](OrderNo,ProductName,Quantity,Money,SaleDate)VALUES(@OrderNo,@ProductName,@Quantity,@Money,@SaleDate)";
OleDbParameter[]oleDbParam=newOleDbParameter[]
{
newOleDbParameter("@OrderNo",param.OrderNo),
newOleDbParameter("@ProductName",param.ProductName),
newOleDbParameter("@Quantity",param.Quantity),
newOleDbParameter("@Money",param.Money),
newOleDbParameter("@SaleDate",param.SaleDate)
};
result=ExcelHelper.ExecuteCommand(sql,oleDbParam);
returnresult;
}
///<summary>
///修改订单
///</summary>
publicintUpdateOrder(Orderparam)
{
intresult=0;
if(!String.IsNullOrEmpty(param.OrderNo))
{
stringsql="UPDATE[Order$]SETProductName=@ProductName,Quantity=@Quantity,Money=@MoneyWHEREOrderNo=@OrderNo";
OleDbParameter[]sqlParam=newOleDbParameter[]
{
newOleDbParameter("@OrderNo",param.OrderNo),
newOleDbParameter("@ProductName",param.ProductName),
newOleDbParameter("@Quantity",param.Quantity),
newOleDbParameter("@Money",param.Money)
};
result=ExcelHelper.ExecuteCommand(sql,sqlParam);
}
returnresult;
}
///<summary>
///删除订单
///</summary>
publicintDeleteOrder(Orderparam)
{
intresult=0;
if(!String.IsNullOrEmpty(param.OrderNo))
{
stringsql="DELETE[Order$]WHEREOrderNo=@OrderNo";
OleDbParameter[]sqlParam=newOleDbParameter[]
{
newOleDbParameter("@OrderNo",param.OrderNo),
};
result=ExcelHelper.ExecuteCommand(sql,sqlParam);
}
returnresult;
}
}
}
相关文章
- 进销存excel_用Excel制作简单的进销存系统「建议收藏」
- c#数组赋初值_C#数组初始化
- C#中using语句是什么意思「建议收藏」
- c# mysql executenonquery_C#与数据库访问技术总结（八）之ExecuteNonQuery方法
- 图片链接如何在excel里转成图片_mdf文件怎么转成Excel
- Python xlwt数据保存到 Excel中以及xlrd读取excel文件画图
- C#导入导出数据到Excel的通用类代码详解编程语言
- Excel数据快速转移至MySQL（excel转mysql）
- 文件Linux下如何打开Excel文件（linux打开excel）
- SQL Server数据导出到Excel的方法（sqlserver导出到excel）
- 轻松操作：oracle快速导入Excel数据（oracle导入excel数据）
- MYSQL与EXCEL：实现数据管理的双重力量（mysql与excel）
- Oracle数据快速导出至Excel表格（oracle导出excel）
- C#数据导入/导出Excel文件及winForm导出Execl总结
- ASP.NET(C#)读取Excel的文件内容
- C#将htmltable导出成excel实例
- c#利用Excel直接读取数据到DataGridView
- .net/c#memcached缓存获取所有缓存键的方法步骤
- c#生成excel示例sql数据库导出excel
- c#连接excel示例分享
- C#为配置文件加密的实现方法
- C#复合模式(CompositePattern)实例教程