C # operations Excel data Additions and Deletions sample

C # operations Excel data additions and deletions.

First create the Exceldb.xlsx file and add two sheets.

Worksheet 1:

UserInfo table, fields: UserId, UserName, age, Address, Createtime.

Worksheet 2:

Order table, Fields: OrderNo, ProductName, Quantity, Money, saledate.

1. Create ExcelHelper.cs class, Excel File Processing class

Using System; Using System.Collections.Generic; Using System.Linq; Using System.Text; Using System.Data.OleDb; Using System.Data; namespace Mystudy.dal {//<summary>///Excel File Processing class///</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> Execute parameterless SQL statements////</summary>//<param name= "SQL" >sql statements </param>//<retur Ns> returns the number of rows affected by the SQL statement </returns> public static int ExecuteCommand (String sql) {OleDbCommand cmd = new OleDbCommand (sq L, Connection); int result = cmd. ExecuteNonQuery (); Connection. Close (); return result; }///<summary>///execute parameterized SQL statements///</summary>//<param name= "SQL" >sql statements </param>//<param N Ame= "Values" > Parameters collection </param>//<returns> Returns the number of rows affected by the SQL statement </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>////Returns a single value SQL statement with no arguments///</summary>//<param name= "SQL" >sql statements </param>//<retu Rns> returns the number of rows queried by the SQL statement </returns> public static int getscalar (String sql) {OleDbCommand cmd = new OleDbCommand (SQL, Co Nnection); int result = Convert.ToInt32 (cmd). ExecuteScalar ()); Connection. Close (); return result; }///<summary>///Returns a single SQL statement with parameters////</summary>//<param name= "SQL" >sql statements </param>//<par Am name= "parameters" > Parameter collection </param>//<returns> Returns the number of rows queried by the SQL statement </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>//Execute Query parameterless SQL statements///</summary>//<param name= "SQL" >sql statements </param>//<retur ns> return Data set </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>///execute query with parameter SQL statements////</summary>//<param name= "SQL" >sql statements </param>//<param Name= "Parameters" > Parameter collection </param>//<returns> return Data set </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, create entity class

2.1 Create the UserInfo.cs class, the user information entity class.

Using System; Using System.Collections.Generic; Using System.Linq; Using System.Text; Using System.Data;  namespace Mystudy.model {//<summary>///user Information entity class///</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>///convert DataTable to List data////</summary> public static LIST&LT;USERINFO&G T ToList (DataSet DataSet) {list<userinfo> userlist = new list<userinfo> (); if (DataSet! = null && DataS Et. 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 Creates the Order.cs class, the Order entity class.

Using System; Using System.Collections.Generic; Using System.Linq; Using System.Text; Using System.Data; namespace Mystudy.model {//<summary>///Order entity class////</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>///convert DataTable to List data////</summary> public static list<order> to List (DataSet DataSet) {list<order> orderlist = new list<order> (); if (DataSet! = null && dataset.tabl Es. 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. Create business logic class

3.1 Create UserInfoBLL.cs class, user information business class.

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>//user Information Business class///</summary> public class Userinfobll {//<summary&gt ; Query user list///</summary> public list<userinfo> getuserlist () {list<userinfo> userlist = new List<us Erinfo> (); String sql = "SELECT * FROM [userinfo$]"; DataSet dateset = excelhelper.getreader (sql); UserList = Userinfo.tolist (Dateset); return userlist; }///<summary>///Get total users///</summary> public int getusercount () {int result = 0; String sql = "Select COU NT (*) from [userinfo$] "; result = Excelhelper.getscalar (SQL); return result; }///<summary>//New user information///</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>//Modify user information///</summary> public int updateuserinfo (UserInfo param) {int result = 0; if (para M.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>//delete user information///</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 Create OrderBLL.cs class, order business class

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>///Order Business class///</summary> public class ORDERBLL {///<summary>// Query order list///</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>//Get total orders///</summary> public int getordercount () {int result = 0; String sql = "Select CO UNT (*) from [order$] "; result = Excelhelper.getscalar (SQL); return result; }///<summary>//new orders///</summary> public int addorder (order param) {int result = 0; String sql = "Inser T into [order$] (orderno,productname,quantity,money,saledate) VALUES (@OrderNo, @ProductName, @Quantity, @Money, @ Saledate) "; OLE DBparameter[] 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>//Modify order///</summary> public int updateorder (order param) {int result = 0; 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>///delete order///</summary> public INT Deleteorder (Order param) {int result = 0; 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; } } }

The above is the C # operation of Excel data additions and deletions to the content of the sample, more relevant content please pay attention to topic.alibabacloud.com (www.php.cn)!

