private IList <order> GetExcelTableData(string fPath, string tableName, IList <order> orderList) { string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1;\";Data Source=" + fPath; OleDbConnection oledbCon = new OleDbConnection(strConn); oledbCon.Open(); string strExcel = ""; OleDbDataAdapter oledbCmd = null; strExcel = "select * from [" + tableName + "]"; oledbCmd = new OleDbDataAdapter(strExcel, strConn); DataTable table = new DataTable(); oledbCmd.Fill(table); oledbCon.Close(); foreach (DataRow dr in table.Rows)//遍历数据行 { if (dr != null && dr[0].ToString() != "") { order orderObj = new order();//创建客户信息 orderObj.state = dr["机票状态"].ToString(); orderObj.type = dr["机票类型"].ToString(); orderObj.clearCode = dr["结算码"].ToString(); orderObj.number = dr["票号"].ToString(); orderObj.clearCodeNumber = dr["结算代码票号"].ToString(); orderObj.flightNumber = dr["航班号"].ToString(); if (dr[6].ToString() != "") { orderObj.ticketTime = Convert.ToDateTime(dr["出票时间"].ToString()); } if (dr[7].ToString() != "") { orderObj.payTime = Convert.ToDateTime(dr["支付时间"].ToString()); } if (dr[8].ToString() != "") { orderObj.flightTime = Convert.ToDateTime(dr["乘机时间"].ToString()); } orderObj.orderPeople = dr["预订员"].ToString(); orderObj.Carrier = dr["承运人"].ToString(); orderObj.Discount = dr["舱位折扣"].ToString(); orderObj.Passenger = dr["乘客"].ToString(); orderObj.Voyage = dr["航程"].ToString(); orderObj.PNR = dr["PNR"].ToString(); orderObj.ticketPrice = dr["票价"].ToString(); orderObj.taxPrice = dr["税费"].ToString(); orderObj.ranyouFee = dr["燃油费"].ToString(); orderObj.jijianFee = dr["基建费"].ToString(); orderObj.rebate = dr["返点"].ToString(); orderObj.shouxuFee = dr["手续费"].ToString(); orderObj.yongjin = dr["佣金"].ToString(); orderObj.tgqFee = dr["退改签手续费"].ToString(); orderObj.payFee = dr["支付手续费"].ToString(); orderObj.actualPayPrice = dr["实付款"].ToString(); orderObj.buyer = dr["采购商"].ToString(); string[] arryBuyer = dr["采购商"].ToString().Replace("yinuo", ",").Split(','); orderObj.buyername = arryBuyer[0]; if (arryBuyer.Length >= 2) { orderObj.buyerOPUserId = arryBuyer[1]; } else { orderObj.buyerOPUserId = ""; } orderObj.Supplier = dr["供应商"].ToString(); orderObj.Policy = dr["政策类型"].ToString(); orderObj.payWay = dr["支付公司"].ToString(); orderList.Add(orderObj);//添加到集合 } } return(orderList); }
/// <summary> /// 增加一条数据 /// </summary> public void Add(order model) { StringBuilder strSql = new StringBuilder(); strSql.Append("insert into [order]("); strSql.Append("state,[type],clearCode,[number],clearCodeNumber,flightNumber,ticketTime,payTime,flightTime,orderPeople,Carrier,Discount,Passenger,Voyage,PNR,ticketPrice,taxPrice,ranyouFee,jijianFee,rebate,shouxuFee,yongjin,tgqFee,payFee,actualPayPrice,buyer,buyername,buyerOPUserId,Supplier,Policy,payWay)"); strSql.Append(" values ("); strSql.Append("@state,@type,@clearCode,@number,@clearCodeNumber,@flightNumber,@ticketTime,@payTime,@flightTime,@orderPeople,@Carrier,@Discount,@Passenger,@Voyage,@PNR,@ticketPrice,@taxPrice,@ranyouFee,@jijianFee,@rebate,@shouxuFee,@yongjin,@tgqFee,@payFee,@actualPayPrice,@buyer,@buyername,@buyerOPUserId,@Supplier,@Policy,@payWay)"); OleDbParameter[] parameters = { new OleDbParameter("@state", OleDbType.VarChar, 50), new OleDbParameter("@type", OleDbType.VarChar, 50), new OleDbParameter("@clearCode", OleDbType.VarChar, 50), new OleDbParameter("@number", OleDbType.VarChar, 50), new OleDbParameter("@clearCodeNumber", OleDbType.VarChar, 50), new OleDbParameter("@flightNumber", OleDbType.VarChar, 50), new OleDbParameter("@ticketTime", OleDbType.Date), new OleDbParameter("@payTime", OleDbType.Date), new OleDbParameter("@flightTime", OleDbType.Date), new OleDbParameter("@orderPeople", OleDbType.VarChar, 50), new OleDbParameter("@Carrier", OleDbType.VarChar, 50), new OleDbParameter("@Discount", OleDbType.VarChar, 50), new OleDbParameter("@Passenger", OleDbType.VarChar, 50), new OleDbParameter("@Voyage", OleDbType.VarChar, 50), new OleDbParameter("@PNR", OleDbType.VarChar, 50), new OleDbParameter("@ticketPrice", OleDbType.VarChar, 50), new OleDbParameter("@taxPrice", OleDbType.VarChar, 50), new OleDbParameter("@ranyouFee", OleDbType.VarChar, 50), new OleDbParameter("@jijianFee", OleDbType.VarChar, 50), new OleDbParameter("@rebate", OleDbType.VarChar, 50), new OleDbParameter("@shouxuFee", OleDbType.VarChar, 50), new OleDbParameter("@yongjin", OleDbType.VarChar, 50), new OleDbParameter("@tgqFee", OleDbType.VarChar, 50), new OleDbParameter("@payFee", OleDbType.VarChar, 50), new OleDbParameter("@actualPayPrice", OleDbType.VarChar, 50), new OleDbParameter("@buyer", OleDbType.VarChar, 50), new OleDbParameter("@buyername", OleDbType.VarChar, 255), new OleDbParameter("@buyerOPUserId", OleDbType.VarChar, 50), new OleDbParameter("@Supplier", OleDbType.VarChar, 50), new OleDbParameter("@Policy", OleDbType.VarChar, 50), new OleDbParameter("@payWay", OleDbType.VarChar, 50) }; parameters[0].Value = model.state; parameters[1].Value = model.type; parameters[2].Value = model.clearCode; parameters[3].Value = model.number; parameters[4].Value = model.clearCodeNumber; parameters[5].Value = model.flightNumber; parameters[6].Value = model.ticketTime; parameters[7].Value = model.payTime; parameters[8].Value = model.flightTime; parameters[9].Value = model.orderPeople; parameters[10].Value = model.Carrier; parameters[11].Value = model.Discount; parameters[12].Value = model.Passenger; parameters[13].Value = model.Voyage; parameters[14].Value = model.PNR; parameters[15].Value = model.ticketPrice; parameters[16].Value = model.taxPrice; parameters[17].Value = model.ranyouFee; parameters[18].Value = model.jijianFee; parameters[19].Value = model.rebate; parameters[20].Value = model.shouxuFee; parameters[21].Value = model.yongjin; parameters[22].Value = model.tgqFee; parameters[23].Value = model.payFee; parameters[24].Value = model.actualPayPrice; parameters[25].Value = model.buyer; parameters[26].Value = model.buyername; parameters[27].Value = model.buyerOPUserId; parameters[28].Value = model.Supplier; parameters[29].Value = model.Policy; parameters[30].Value = model.payWay; DbHelperOleDb.ExecuteSql(strSql.ToString(), parameters); }