当前位置： 移动技术网 > IT编程>开发语言>.net > C#操作Excel数据增删改查示例

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

2017年12月12日  | 移动技术网IT编程  | 我要评论

强心脏120320,zazhimi,伊莉莎·卡普特洛娃

c#操作excel数据增删改查。

首先创建exceldb.xlsx文件，并添加两张工作表。

工作表1：

userinfo表，字段：userid、username、age、address、createtime。

工作表2：

order表，字段：orderno、productname、quantity、money、saledate。

1、创建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>
/// 执行无参数的sql语句
/// </summary>
/// <param name="sql">sql语句</param>
/// <returns>返回受sql语句影响的行数</returns>
public static int executecommand(string sql)
{
oledbcommand cmd = new oledbcommand(sql, connection);
int result = cmd.executenonquery();
connection.close();
return result;
}

/// <summary>
/// 执行有参数的sql语句
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="values">参数集合</param>
/// <returns>返回受sql语句影响的行数</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>
/// 返回单个值无参数的sql语句
/// </summary>
/// <param name="sql">sql语句</param>
/// <returns>返回受sql语句查询的行数</returns>
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">sql语句</param>
/// <param name="parameters">参数集合</param>
/// <returns>返回受sql语句查询的行数</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>
/// 执行查询无参数sql语句
/// </summary>
/// <param name="sql">sql语句</param>
/// <returns>返回数据集</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>
/// <param name="sql">sql语句</param>
/// <param name="parameters">参数集合</param>
/// <returns>返回数据集</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、 创建实体类

2.1 创建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转换成list数据
/// </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 创建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转换成list数据
/// </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、创建业务逻辑类

3.1 创建userinfobll.cs类，用户信息业务类。
复制代码 代码如下:

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>
/// 用户信息业务类
/// </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>
/// 获取用户总数
/// </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 创建orderbll.cs类，订单业务类
复制代码 代码如下:

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>
/// 订单业务类
/// </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>
/// 获取订单总数
/// </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;
}
}
}

您可能感兴趣的文章:

如对本文有疑问，请在下面进行留言讨论，广大热心网友会与你互动！！ 点击进行留言回复

相关文章:

验证码：
最近更新的文章
大家感兴趣的文章
移动技术网