首先创建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; } } }
以上就是c#操作excel数据增删改查示例的内容。