思路：
导入：
1，初始化一个OpenFileDialog类 （OpenFileDialog fileDialog = new OpenFileDialog();）
2， 获取用户选择文件的后缀名（string extension = Path.GetExtension(fileDialog.FileName).ToLower();），并设置允许后缀文件名；
3，NPOI转datetable，遍历tatetable转成实体类列表并入库；
导出：
1， 创建提示用户保存类，SaveFileDialog saveFileDialog = new SaveFileDialog()，设置允许导出文件名，对话框显示信息等属性；
2，saveFileDialog.FileName.Length>0，sql转datetable，NPOI创建文件（设置保存路径 saveFileDialog.FileName）
动态添加控件注意：
1，在panal添加对应控件，便于数据太多可以下拉（AutoScroll=true）；
2，设置new Point（x,y）坐标位置，控件大小调整；特别是坐标不好处理，现在页面上设置排版，并记录x,y 记录；
3，给与对应控件添加响应事件（如双击全选等等）
4，保存时后台查找控件对应值，winform没有webform的findcontrol方法，故网上找到帮助类（如下），但原理一样都是从对应Control下查找已添加类，注意动态添加控件时，命名需有规则，便于查找：
1 using System; 2 using System.Collections.Generic; 3 using System.Linq; 4 using System.Text; 5 using System.Windows.Forms; 6 7 namespace webfrom.common 8 { 9 public static class ControlUtil 10 { 11 /// <summary> 12 /// 按名称查找控件 13 /// </summary> 14 /// <param name="parentControl">查找控件的父容器控件</param> 15 /// <param name="findCtrlName">查找控件名称</param> 16 /// <returns>若没有查找到返回NULL</returns> 17 public static Control FindControl(this Control parentControl, string findCtrlName) 18 { 19 Control _findedControl = null; 20 if (!string.IsNullOrEmpty(findCtrlName) && parentControl != null) 21 { 22 foreach (Control ctrl in parentControl.Controls) 23 { 24 if (ctrl.Name.Equals(findCtrlName)) 25 { 26 _findedControl = ctrl; 27 break; 28 } 29 } 30 } 31 return _findedControl; 32 } 33 /// <summary> 34 /// 将Control转换某种控件类型 35 /// </summary> 36 /// <typeparam name="T">控件类型</typeparam> 37 /// <param name="control">Control</param> 38 /// <param name="result">转换结果</param> 39 /// <returns>若成功则返回控件；若失败则返回NULL</returns> 40 public static T Cast<T>(this Control control, out bool result) where T : Control 41 { 42 result = false; 43 T _castCtrl = null; 44 if (control != null) 45 { 46 if (control is T) 47 { 48 try 49 { 50 _castCtrl = control as T; 51 result = true; 52 } 53 catch (Exception ex) 54 { 55 result = false; 56 } 57 } 58 } 59 return _castCtrl; 60 } 61 } 62 63 //public class comBoBoxEx : System.Windows.Forms.ComboBox 64 //{ 65 // public bool isWheel = false; 66 // public string strComB = null; 67 // protected override void OnMouseWheel(System.Windows.Forms.MouseEventArgs e) 68 // { 69 // strComB = Text; 70 // isWheel = true; 71 // } 72 73 // protected override void OnMouseDown(System.Windows.Forms.MouseEventArgs e) 74 // { 75 // base.OnMouseDown(e); 76 // isWheel = false; 77 78 // } 79 80 // protected override void OnTextChanged(EventArgs e) 81 // { 82 // base.OnTextChanged(e); 83 // if (isWheel) 84 // { 85 // Text = strComB; 86 // } 87 // } 88 //} 89 }
其他：
鼠标滚动，禁用；窗口置顶；
所有后台代码：
1 using System; 2 using System.Collections.Generic; 3 using System.ComponentModel; 4 using System.Data; 5 using System.Drawing; 6 using System.Linq; 7 using System.Text; 8 using System.Windows.Forms; 9 using webframework.common; 10 using System.Collections; 11 using System.IO; 12 using webfrom.common; 13 using System.Text.RegularExpressions; 14 using webfrom.bll; 15 using webfrom.model; 16 using MySql.Data.MySqlClient; 17 using System.Data.SqlClient; 18 19 namespace webfrom.trip 20 { 21 public partial class tripmain : Form, IMessageFilter 22 { 23 //当前登陆用户 24 public string Username { get; set; } 25 //乘客人数 26 public int PsgCount { get; set; } 27 //订单ID 28 public string OrderID { get; set; } 29 //tt订单号 30 public string OrderNO { get; set; } 31 //平台id 32 public string orderFrom { get; set; } 33 //快递单号 34 public string ExpNo { get; set; } 35 36 37 public tripmain() 38 { 39 InitializeComponent(); 40 } 41 public bool PreFilterMessage(ref Message m) 42 { 43 if (m.Msg == 522) 44 { 45 return true; 46 } 47 else 48 { 49 return false; 50 } 51 } 52 //导入 53 private void btnimport_Click(object sender, EventArgs e) 54 { 55 lbl_msg.Text = ""; 56 //初始化一个OpenFileDialog类 57 OpenFileDialog fileDialog = new OpenFileDialog(); 58 //判断用户是否正确的选择了文件 59 if (fileDialog.ShowDialog() == DialogResult.OK) 60 { 61 //获取用户选择文件的后缀名 62 string extension = Path.GetExtension(fileDialog.FileName).ToLower(); 63 //声明允许的后缀名 64 string[] str = new string[] { ".xls", ".xlsx", ".csv" }; 65 if (!((IList)str).Contains(extension)) 66 { 67 //MessageBox.Show("仅能导入xls,xlsx,csv文件！"); 68 lbl_msg.Text = "提示信息:仅能导入xls,xlsx,csv文件！"; 69 } 70 else 71 { 72 DataTable dt = new DataTable(); 73 switch (extension) 74 { 75 case ".xlsx": 76 dt = common.ExcelHelper.ExcelToTableForXLSX(fileDialog.FileName); 77 break; 78 case ".xls": 79 dt = common.ExcelHelper.GetExcelDataAsTableNPOI(fileDialog.FileName); 80 break; 81 case ".csv": 82 dt = common.CSVUtil.getCsvDataByTitle(fileDialog.FileName, "业务单号", null); 83 break; 84 default: 85 break; 86 } 87 if (dt.Rows.Count > 0) 88 { 89 int error = 0; 90 int count = 0; 91 try 92 { 93 List<modelt_printexpress> list = new List<modelt_printexpress>(); 94 modelt_printexpress m; 95 DateTime dtnow = DateTime.Now; 96 string orderid = string.Empty; 97 98 foreach (DataRow dr in dt.Rows) 99 { 100 count++; 101 orderid = dr["业务单号"].ToString().Trim(); 102 if (string.IsNullOrEmpty(orderid)) 103 continue; 104 m = new modelt_printexpress(); 105 #region model赋值 106 m.expcompany = dr["物流公司"].ToString().Trim(); 107 m.busno = dr["业务单号"].ToString().Trim();//对应国内tt订单号 108 m.expno = dr["运单号"].ToString().Trim(); 109 m.fworkaddress = dr["寄件单位"].ToString().Trim(); 110 m.fname = dr["寄件人姓名"].ToString().Trim(); 111 m.ftelno = dr["寄件人电话"].ToString().Trim(); 112 m.fmobileno = dr["寄件人手机"].ToString().Trim(); 113 m.fprovince = dr["寄件人省"].ToString().Trim(); 114 m.fcity = dr["寄件人市"].ToString().Trim(); 115 m.fregion = dr["寄件区/县"].ToString().Trim(); 116 m.faddress = dr["寄件人地址"].ToString().Trim(); 117 m.fpostcode = dr["寄件人邮编"].ToString().Trim(); 118 m.tname = dr["收件人姓名"].ToString().Trim(); 119 m.ttelno = dr["收件人电话"].ToString().Trim(); 120 m.tmobileno = dr["收件人手机"].ToString().Trim(); 121 m.tprovince = dr["收件省"].ToString().Trim(); 122 m.tcity = dr["收件市"].ToString().Trim(); 123 m.tregion = dr["收件区/县"].ToString().Trim(); 124 m.taddress = dr["收件人地址"].ToString().Trim(); 125 m.tpostcode = dr["收件邮政编码"].ToString().Trim(); 126 m.exppay = StringUtils.StrToDecimal(dr["运费"].ToString().Trim(), 0); 127 m.orderprice = StringUtils.StrToDecimal(dr["订单金额"].ToString().Trim(), 0); 128 m.itemname = dr["商品名称"].ToString().Trim(); 129 m.itemcode = dr["商品编码"].ToString().Trim(); 130 m.itemattribute = dr["销售属性"].ToString().Trim(); 131 m.itemprice = StringUtils.StrToDecimal(dr["商品金额"].ToString().Trim(), 0); 132 m.itemacount = StringUtils.StrToInt(dr["数量"].ToString().Trim(), 0); 133 m.leavemsg = dr["留言"].ToString().Trim(); 134 m.note = dr["备注"].ToString().Trim(); 135 m.operater = this.Username; 136 m.creattime = dtnow; 137 #endregion 138 list.Add(m); 139 } 140 string msg = string.Empty; 141 new bllprintexpress().InsertList(list, Config.CONSQL_172_16_6_1_WRITE, ref msg); 142 //MessageBox.Show("数据导入完毕【文件总数:" + dt.Rows.Count + " 导入" + msg + "--未匹配tt订单号:" + error + "】"); 143 lbl_msg.Text = "提示信息:" + "数据导入完毕【文件总数:" + dt.Rows.Count + " 导入" + msg + "--未匹配tt订单号:" + error + "】"; 144 145 146 } 147 catch (Exception ex) 148 { 149 //MessageBox.Show("导入数据报错：" + ex.Message); 150 lbl_msg.Text = "提示信息:" + "导入数据报错：" + ex.Message; 151 } 152 } 153 else 154 { 155 //MessageBox.Show("您选择的文件报表中没有数据"); 156 lbl_msg.Text = "提示信息:" + "您选择的文件报表中没有数据"; 157 } 158 } 159 } 160 } 161 //导出 162 private void btnexport_Click(object sender, EventArgs e) 163 { 164 try 165 { 166 lbl_msg.Text = ""; 167 DateTime dtbegin = Convert.ToDateTime(dtpbegin.Value.ToString("yyyy-MM-dd")); 168 DateTime dtend = Convert.ToDateTime(dtpend.Value.ToString("yyyy-MM-dd")); 169 if ((dtend - dtbegin).Days > 30) 170 { 171 //MessageBox.Show("航班日期跨度不能超过30天"); 172 lbl_msg.Text = "提示信息:" + "航班日期跨度不能超过30天"; 173 dtpbegin.Focus(); 174 return; 175 } 176 if (dtend < dtbegin) 177 { 178 //MessageBox.Show("航班开始日期不能大于结束日期"); 179 lbl_msg.Text = "提示信息:" + "航班开始日期不能大于结束日期"; 180 dtpbegin.Focus(); 181 return; 182 } 183 184 SaveFileDialog saveFileDialog = new SaveFileDialog(); 185 saveFileDialog.Filter = "导出Excel (*.xls)|*.xls"; 186 saveFileDialog.FilterIndex = 0; 187 saveFileDialog.RestoreDirectory = true; 188 saveFileDialog.CreatePrompt = true; 189 saveFileDialog.Title = "导出文件保存路径"; 190 saveFileDialog.ShowDialog(); 191 string strName = saveFileDialog.FileName; 192 if (strName.Length != 0) 193 { 194 string sql = "SELECT o.id orderid ,o.orderNo,jd.receiver,jd.receiverPhone,jd.postCode,jd.postAddress " + 195 "FROM t_order AS o " + 196 "LEFT JOIN t_journey AS jo ON o.id = jo.orderId " + 197 "LEFT JOIN t_journeypassenger AS jp ON jo.id = jp.journeyId " + 198 "LEFT JOIN t_passenger AS p ON jp.passengerId = p.id " + 199 "LEFT JOIN t_travel AS tr ON jp.id = tr.journeyPassengerId " + 200 "RIGHT JOIN t_journeydetail AS jd ON o.id = jd.orderId " + 201 "WHERE 1=1 AND o.orderFrom=1 " + 202 "AND (jp.pTikState IS NULL OR jp.pTikState!=1) " + 203 "AND jd.postAddress <>'' " + 204 "AND (o.orderState=1 OR o.orderState=3 OR o.orderState=7) " + 205 "AND jo.takeoffDate >='" + dtbegin.ToString("yyyy-MM-dd") + "' " + 206 "AND jo.takeoffDate <='" + dtend.ToString("yyyy-MM-dd") + "' " + 207 "AND (tr.printState IS NULL OR tr.printState=0) " + 208 "AND jd.isTra=1 AND (jd.isTra=1 AND jd.isTraKf <> 0) " + 209 //"AND jd.isTra=1 AND ((jd.isTra=1 AND jd.isTraKf <> 0) or jd.isTraKf=1) " + 210 //" and (jd.traAddress<>'' or jd.isTraKf=1) orderFrom ==0" + 211 "GROUP BY orderid"; 212 DataTable dt = webframework.common.MySqlHelper.ExecuteDataSet(webframework.common.MySqlHelper.ConnectionString, CommandType.Text, sql).Tables[0]; 213 if (dt != null && dt.Rows.Count > 0) 214 { 215 string strids = string.Empty; 216 string straddress = string.Empty; 217 string mobilecode = string.Empty; 218 string phonecode = string.Empty; 219 string postcode = string.Empty; 220 string address = string.Empty; 221 222 DataTable dtnew = new DataTable(); 223 //dtnew.Columns.Add("姓名", typeof(string)); 224 //dtnew.Columns.Add("订单ID", typeof(string)); 225 //dtnew.Columns.Add("联系单位", typeof(string)); 226 //dtnew.Columns.Add("联系地址", typeof(string)); 227 //dtnew.Columns.Add("手机号码", typeof(string)); 228 //dtnew.Columns.Add("电话号码", typeof(string)); 229 //dtnew.Columns.Add("邮编", typeof(string)); 230 231 dtnew.Columns.Add("业务单号", typeof(string)); 232 dtnew.Columns.Add("收件人姓名", typeof(string)); 233 dtnew.Columns.Add("收件人手机", typeof(string)); 234 dtnew.Columns.Add("收件人地址", typeof(string)); 235 dtnew.Columns.Add("品名", typeof(string)); 236 dtnew.Columns.Add("数量", typeof(string)); 237 dtnew.Columns.Add("备注", typeof(string)); 238 foreach (DataRow item in dt.Rows) 239 { 240 if (!strids.Contains(item["orderid"].ToString().Trim())) 241 { 242 strids += item["orderid"].ToString().Trim() + ","; 243 straddress = item["postAddress"].ToString().Trim().TrimEnd('-').TrimStart('-'); 244 var arr = straddress.Split('-'); 245 if (arr.Length < 3) 246 continue; 247 248 mobilecode = item["receiverPhone"].ToString().Trim(); 249 if (string.IsNullOrEmpty(mobilecode)) 250 { 251 if (Regex.IsMatch(straddress, @"-1\d{10}", RegexOptions.Multiline)) 252 mobilecode = new Regex(@"-1\d{10}", RegexOptions.Multiline).Matches(straddress)[0].Groups[0].Value.Replace("-", ""); 253 else 254 mobilecode = arr[arr.Length - 1]; 255 } 256 257 if (Regex.IsMatch(straddress, @"-\d{4}-\d{7,8}|-\d{3}-\d{8}", RegexOptions.Multiline)) 258 phonecode = new Regex(@"-\d{4}-\d{7,8}|-\d{3}-\d{8}", RegexOptions.Multiline).Matches(straddress)[0].Groups[0].Value.TrimStart('-'); 259 else 260 phonecode = ""; 261 262 if (Regex.IsMatch(straddress, @"-\d{6}-", RegexOptions.Multiline)) 263 postcode = new Regex(@"-\d{6}-", RegexOptions.Multiline).Matches(straddress)[0].Groups[0].Value.Replace("-", ""); 264 else 265 postcode = arr[arr.Length - 2]; 266 267 address = straddress.Substring(straddress.IndexOf('-') + 1).Replace(mobilecode, "").Replace(postcode, ""); 268 address = (phonecode.Length > 0 ? address.Replace(phonecode, "") : address).Replace("-", " "); 269 DataRow dtrow = dtnew.NewRow(); 270 ////姓名 订单ID 联系单位 联系地址 手机号码 电话号码 邮编 271 //dtrow["姓名"] = item["receiver"].ToString().Trim(); 272 //dtrow["订单ID"] = item["orderid"].ToString().Trim(); 273 //dtrow["联系单位"] = "天泰"; 274 //dtrow["手机号码"] = mobilecode; 275 //dtrow["电话号码"] = phonecode; 276 //dtrow["邮编"] = postcode; 277 //dtrow["联系地址"] = address; 278 279 //业务单号 收件人姓名 收件人手机 收件人地址 品名 数量 备注 280 dtrow["业务单号"] = item["orderid"].ToString().Trim(); 281 dtrow["收件人姓名"] = item["receiver"].ToString().Trim(); 282 dtrow["收件人手机"] = mobilecode; 283 dtrow["收件人地址"] = address; 284 dtrow["品名"] = "票务"; 285 dtrow["数量"] = "1"; 286 dtrow["备注"] = item["orderNo"].ToString().Trim() + "||" + item["postAddress"].ToString().Trim(); 287 dtnew.Rows.Add(dtrow); 288 } 289 290 } 291 292 NewExcelHelper.ExportExcel(dtnew, strName, "联系人数据" + dtbegin.ToString("yyyyMMdd") + "_" + dtend.ToString("yyyyMMdd")); 293 //MessageBox.Show("导出数据成功（路径：" + strName); 294 lbl_msg.Text = "提示信息:" + "导出数据成功（路径：" + strName; 295 } 296 } 297 } 298 catch (Exception ex) 299 { 300 //MessageBox.Show("导出数据出错：" + ex.Message); 301 lbl_msg.Text = "提示信息:" + "导出数据出错：" + ex.Message; 302 } 303 } 304 305 private void txtbarcode_TextChanged(object sender, EventArgs e) 306 { 307 lbl_msg.Text = ""; 308 if (txtbarcode.Text.Trim().Length > 10) 309 { 310 this.PsgCount = 0; 311 this.OrderID = ""; 312 this.OrderNO = ""; 313 this.orderFrom = ""; 314 this.ExpNo = ""; 315 //gbdetail.Text = "扫条码快递号对应TT订单明细"; 316 string sql1 = "select busno,expno from t_printexpress where expno=@expno"; 317 SqlParameter[] parameters = { new SqlParameter("@expno", SqlDbType.VarChar, 50) }; 318 parameters[0].Value = txtbarcode.Text.Trim(); 319 DataTable dtp = SqlHelper.ExecuteDataTable(Config.CONSQL_172_16_6_1_WRITE, CommandType.Text, sql1, parameters); 320 if (dtp != null && dtp.Rows.Count > 0) 321 { 322 OrderID = dtp.Rows[0]["busno"].ToString(); 323 ExpNo = dtp.Rows[0]["expno"].ToString(); 324 if (!string.IsNullOrEmpty(OrderID)) 325 { 326 BindData(OrderID); 327 } 328 } 329 else 330 { 331 gbdetail.Visible = false; 332 pldetail.Controls.Clear(); 333 } 334 } 335 else if (txtbarcode.Text.Trim().Length == 0) 336 { 337 gbdetail.Visible = false; 338 pldetail.Controls.Clear(); 339 } 340 } 341 private void BindData(string orderid) 342 { 343 this.OrderNO = ""; 344 this.orderFrom = ""; 345 txtorderid.Text = ""; 346 txtorderno.Text = ""; 347 //gbdetail.Text = "扫条码快递号对应TT订单明细"; 348 string sql = "SELECT o.id,o.orderfrom,o.bank,o.shopName,o.returnDate,o.tbRate,o.orderNo,o.cNo,o.outDate,o.outTime,o.totalBrokerage,o.orderState,o.payState,o.policyType,o.totalPrice,o.tradeNo,o.impUser,o.impUserId,o.otherOrderSum,o.otherpolicysource,o.otherpolicycode, o.orderFrom,o.imDate,o.imTime,o.platformFlag,o.ispnrpnamepiny,isCheckedData,o.ext1,o.createTime,lk.id AS k_id ,lk.orderno AS k_orderno,lk.orderid AS k_orderid , lk.name AS k_name,lk.phone AS k_phone,lk.preparePhone,lk.email,lk.postAddress, od.id AS od_id , od.needInsurance,od.needps,od.backnote,od.xcdprice,jo.id AS j_id,jo.orderid AS jo_orderid, jo.id AS j_id , jo.pnr AS jo_pnr,jo.flightNo,jo.startCity,jo.reachCity,jo.takeoffDate,jo.takeoffTime,jo.reachTime,jo.sailType,jo.policyType AS jo_policytype,jo.seat,jo.lastOpenCabin,jo.cBasePrice,jo.bigpnr,jo.journeypoint,jo.journeystate,jo.reachdate,jo.journeyIsOutTicket,jo.journeyOtherOrderNo,jo.journeyIsOutTicket,jo.journeyOtherOrderNo,jo.journeyPlatformFlag,jo.journeyTradeNo,jo.journeyOtherOrderSum,jo.journeyRemark,jo.journeyTotalTax,jo.cwebprice,jo.shoppingprice,jo.isshareflight,jo.lowcabinSeat,jo.lowcabinPnr,jo.goType,jp.id AS jp_id ,jp.journeyId,jp.pnr,jp.insureCount,jp.insurePrice,jp.tktNo1,jp.tktPrice,jp.backPoint,jp.isOpen AS isopen,jp.purchPrice,jp.payPurchPrice,jp.actualPrice,jp.sellPrice,jp.tax,jp.myc,jp.purchPalse,jp.TbCommission,jp.payCard,jp.reciptWay,jp.otherTax,jp.otherMyc,jp.pTikState,jp.jpstatus,jp.outcode,jp.thirdOrderno,jp.delayinsurePrice,jp.delayinsureCount,jp.bigpnr AS jp_bigpnr,p.id AS p_id,p.name AS p_name, p.ptype, p.email AS p_email, p.phone AS p_phone, p.idCard, p.birthday, p.papersType, p.nationality, p.mun,jd.id AS jd_id,jd.orderid AS jd_orderId,jd.orderno AS jd_orderNo,jd.journeyId AS jd_journeyid,jd.isReturnTra,jd.isTra,jd.traCostPrice,jd.traPrice,jd.postaddress AS jd_postAddress,jd.postCode,jd.receiver,jd.receiverPhone,jd.traAddress,jd.isInsure,jd.sendWay,jd.isTraKf,jd.isInsureKf,jd.insureRmkKf,jd.traRmkKf,jd.expressFee,jd.tradeNO AS jd_tradeNO,t.travelNo,t.printState,t.printType,t.postWay,t.postNo " + 349 "FROM t_order o LEFT JOIN t_orderdetail od ON o.id=od.id " + 350 "LEFT JOIN t_linkman lk ON o.id=lk.orderId " + 351 "LEFT JOIN t_journey jo ON o.id=jo.orderId " + 352 "LEFT JOIN t_journeypassenger jp ON jp.journeyId=jo.id " + 353 "LEFT JOIN t_passenger p ON jp.passengerId = p.id " + 354 "LEFT JOIN t_journeydetail jd ON jd.orderid = o.id " + 355 "LEFT JOIN t_travel t on o.id=t.orderId and t.journeyPassengerId=jp.id " + 356 "WHERE (jp.pTikState IS NULL OR jp.pTikState!=1) " + 357 "AND o.id=" + orderid; 358 DataTable dt = webframework.common.MySqlHelper.ExecuteDataSet(webframework.common.MySqlHelper.ConnectionString, CommandType.Text, sql).Tables[0]; 359 360 361 string sqltemp = "select tripno,printstate,printtype,postway from t_tripexpress where tktno='{0}'"; 362 string tripno = ""; 363 string printstate = ""; 364 string printtype = ""; 365 string postway = ""; 366 string expno = ""; 367 DataTable dttripno = new DataTable(); 368 if (dt != null && dt.Rows.Count > 0) 369 { 370 pldetail.Controls.Clear(); 371 PsgCount = dt.Rows.Count; 372 DataRow row = dt.Rows[0]; 373 //lblflightconnect.Text = "航班号： HO1179 承运人： HO 起始地： PVG 目的地： HRB 日期(起)： 2016-10-21 时间(起)： 08:30:00 航程类型： S 舱位： P \r\n联系人：xxx 联系电话：1234567890"; 374 lblflightconnect.Text = string.Format("航班号:{0} 承运人:{1} 起始地:{2} 目的地:{3} 日期(起):{4} 时间(起): {5} 航程类型:{6} 舱位:{7} \r\n\r\n联系人:{8} 联系地址:{9}", row["flightNo"].ToString().Trim(), row["flightNo"].ToString().Trim().Substring(0, 2), row["startCity"].ToString().Trim(), row["reachCity"].ToString().Trim(), (string.IsNullOrEmpty(row["takeoffDate"].ToString().Trim()) ? "" : Convert.ToDateTime(row["takeoffDate"].ToString().Trim()).ToString("yyyy-MM-dd")), row["takeoffTime"].ToString().Trim(), row["sailType"].ToString().Trim(), row["seat"].ToString().Trim(), row["receiver"].ToString().Trim(), row["jd_postAddress"].ToString().Trim()); 375 int num = 0; 376 Label lbl = new Label(); 377 int x = 18; 378 int y = 22; 379 int rowwidth = 100; 380 TextBox txt = new TextBox(); 381 ComboBox cbb = new ComboBox(); 382 foreach (DataRow item in dt.Rows) 383 { 384 this.OrderNO = item["orderNo"].ToString().Trim(); 385 this.orderFrom = item["orderfrom"].ToString().Trim(); 386 //第一行 387 lbl = new Label(); 388 lbl.Width = 703; 389 lbl.Height = 12; 390 lbl.ForeColor = Color.Blue; 391 lbl.Location = new Point(x, y + rowwidth * num); 392 lbl.Name = "lblpsgdes" + num; 393 lbl.Text = string.Format("退票状态:{0} 类型:{1} 票面:{2} 采购价(实):{3} 销售价:{4} 机建:{5} 燃油:{6} 实收金额:{7}\r\n\r\n", GetTikStateDesc(item["pTikState"]), GetPtypeDesc(item["ptype"]), item["sellprice"].ToString().Trim(), item["purchprice"].ToString().Trim(), item["actualprice"].ToString().Trim(), item["tax"].ToString().Trim(), item["myc"].ToString().Trim(), StringUtils.StrToDecimal(item["actualprice"].ToString().Trim(), 0) + StringUtils.StrToDecimal(item["tax"].ToString().Trim(), 0) + StringUtils.StrToDecimal(item["myc"].ToString().Trim(), 0)); 394 pldetail.Controls.Add(lbl); 395 //第二行 396 lbl = new Label(); 397 lbl.Location = new Point(18, (y * 2 + 6) + rowwidth * num);//18,50 398 lbl.Name = "lblpname" + num; 399 lbl.Text = "姓名:"; 400 lbl.Width = 60; 401 lbl.Height = 12; 402 pldetail.Controls.Add(lbl); 403 txt = new TextBox(); 404 txt.Location = new Point(x * 4 + 6, (y * 2 + 2) + rowwidth * num);//78.46 405 txt.Name = "txtpname" + num; 406 txt.Text = item["p_name"].ToString().Trim(); 407 txt.MouseDoubleClick += txt_MouseDoubleClick; 408 txt.ReadOnly = true; 409 txt.Width = 110; 410 txt.Height = 21; 411 pldetail.Controls.Add(txt); 412 413 lbl = new Label(); 414 lbl.Location = new Point(x * 10 + 8 + 6, (y * 2 + 6) + rowwidth * num);//188+6,50 415 lbl.Name = "lblidCard" + num; 416 lbl.Text = "证件号:"; 417 lbl.Width = 60 - 5; 418 lbl.Height = 12; 419 pldetail.Controls.Add(lbl); 420 txt = new TextBox(); 421 txt.Location = new Point(x * 14 + 12 - 12 - 5, (y * 2 + 2) + rowwidth * num);//244-12-5.46 422 txt.Name = "txtidCard" + num; 423 txt.Text = item["idCard"].ToString().Trim(); 424 txt.MouseDoubleClick += txt_MouseDoubleClick; 425 txt.ReadOnly = true; 426 txt.Width = 115; 427 txt.Height = 21; 428 pldetail.Controls.Add(txt); 429 430 lbl = new Label(); 431 lbl.Location = new Point(x * 20 + 4 + 6, (y * 2 + 6) + rowwidth * num);//364+6,50 432 lbl.Name = "lbltktNo1" + num; 433 lbl.Text = "票号:"; 434 lbl.Width = 60; 435 lbl.Height = 12; 436 pldetail.Controls.Add(lbl); 437 txt = new TextBox(); 438 txt.Location = new Point(x * 23 + 14, (y * 2 + 2) + rowwidth * num);//428.46 439 txt.Name = "txttktNo1" + num; 440 txt.Text = item["tktNo1"].ToString().Trim(); 441 txt.MouseDoubleClick += txt_MouseDoubleClick; 442 txt.ReadOnly = true; 443 txt.Width = 110; 444 txt.Height = 21; 445 pldetail.Controls.Add(txt); 446 447 tripno = item["travelNo"].ToString().Trim(); 448 if (string.IsNullOrEmpty(tripno) && item["tktNo1"].ToString().Trim().Length > 0) 449 { 450 dttripno = SqlHelper.ExecuteDataTable(Config.CONSQL_172_16_6_1_WRITE, CommandType.Text, string.Format(sqltemp, item["tktNo1"].ToString().Trim())); 451 if (dttripno != null && dttripno.Rows.Count > 0) 452 { 453 tripno = dttripno.Rows[0]["tripno"].ToString(); 454 } 455 } 456 lbl = new Label(); 457 lbl.Location = new Point(x * 29 + 12 + 4, (y * 2 + 6) + rowwidth * num);//534+4,50 458 lbl.Name = "lbltravelNo" + num; 459 lbl.Text = "行程单号:"; 460 lbl.Width = 60; 461 lbl.Height = 12; 462 pldetail.Controls.Add(lbl); 463 txt = new TextBox(); 464 txt.Location = new Point(x * 32 + 14 + 6, (y * 2 + 2) + rowwidth * num);//590+6.46 465 txt.Name = "txttravelNo" + num; 466 txt.MouseDoubleClick += txt_MouseDoubleClick; 467 txt.Text = tripno; 468 txt.BackColor = Color.FloralWhite; 469 txt.Width = 110; 470 txt.Height = 21; 471 pldetail.Controls.Add(txt); 472 473 //打印状态 printState 、打印方式 printType、邮寄方式 postWay、邮寄单号 postNo、行程单号 travelNo 474 //第三行 475 lbl = new Label(); 476 lbl.Location = new Point(x, (y * 3 + 12) + rowwidth * num);//18,78 477 lbl.Name = "lblprintState" + num; 478 lbl.Text = "打印状态:"; 479 lbl.Width = 60; 480 lbl.Height = 12; 481 pldetail.Controls.Add(lbl); 482 printstate = GetPrintState(item["printState"].ToString().Trim()); 483 cbb = new ComboBox(); 484 cbb.DropDownStyle = ComboBoxStyle.DropDownList; 485 cbb.Width = 110; 486 cbb.Height = 21; 487 cbb.Name = "cbbprintState" + num; 488 cbb.Location = new Point(x * 4 + 6, (y * 3 + 6) + rowwidth * num);//78.72 489 BindCbbPrintState(ref cbb, printstate, (dttripno != null && dttripno.Rows.Count > 0) ? dttripno.Rows[0]["printstate"].ToString() : ""); 490 if (num == 0) 491 cbb.SelectedIndexChanged += cbb_SelectedIndexChanged; 492 pldetail.Controls.Add(cbb); 493 494 lbl = new Label(); 495 lbl.Location = new Point(x * 10 + 8 + 6, (y * 3 + 12) + rowwidth * num);//188+6,78 496 lbl.Name = "lblprintType" + num; 497 lbl.Text = "打印方式:"; 498 lbl.Width = 60; 499 lbl.Height = 12; 500 pldetail.Controls.Add(lbl); 501 printtype = GetPrintType(item["printType"].ToString().Trim()); 502 cbb = new ComboBox(); 503 cbb.DropDownStyle = ComboBoxStyle.DropDownList; 504 cbb.Location = new Point(x * 14 + 12 - 12, (y * 3 + 6) + rowwidth * num);//244-12.72 505 cbb.Width = 110; 506 cbb.Height = 21; 507 cbb.Name = "cbbprintType" + num; 508 BindCbbPrintType(ref cbb, printtype, (dttripno != null && dttripno.Rows.Count > 0) ? dttripno.Rows[0]["printtype"].ToString() : ""); 509 if (num == 0) 510 cbb.SelectedIndexChanged += cbb_SelectedIndexChanged; 511 pldetail.Controls.Add(cbb); 512 513 lbl = new Label(); 514 lbl.Location = new Point(x * 20 + 4 + 6, (y * 3 + 12) + rowwidth * num);//364+6,78 515 lbl.Name = "lblpostWay" + num; 516 lbl.Text = "邮寄方式:"; 517 lbl.Width = 60; 518 lbl.Height = 12; 519 pldetail.Controls.Add(lbl); 520 postway = GetPostWay(item["postWay"].ToString().Trim()); 521 cbb = new ComboBox(); 522 cbb.DropDownStyle = ComboBoxStyle.DropDownList; 523 cbb.Location = new Point(x * 23 + 14, (y * 3 + 6) + rowwidth * num);//428.72 524 cbb.Width = 110; 525 cbb.Height = 21; 526 cbb.Name = "cbbpostWay" + num; 527 BindCbbPostWay(ref cbb, postway, (dttripno != null && dttripno.Rows.Count > 0) ? dttripno.Rows[0]["postway"].ToString() : ""); 528 if (num == 0) 529 cbb.SelectedIndexChanged += cbb_SelectedIndexChanged; 530 pldetail.Controls.Add(cbb); 531 532 expno = string.IsNullOrEmpty(item["postNo"].ToString().Trim()) ? ExpNo : item["postNo"].ToString().Trim(); 533 lbl = new Label(); 534 lbl.Location = new Point(x * 29 + 12 + 4, (y * 3 + 12) + rowwidth * num);//534+4,78 535 lbl.Name = "lblpostNo" + num; 536 lbl.Text = "快递单号:"; 537 lbl.Width = 60; 538 lbl.Height = 12; 539 pldetail.Controls.Add(lbl); 540 txt = new TextBox(); 541 txt.Location = new Point(x * 32 + 14 + 6, (y * 3 + 6) + rowwidth * num);//590+6.72 542 txt.Name = "txtpostNo" + num; 543 txt.MouseDoubleClick += txt_MouseDoubleClick; 544 txt.Text = expno; 545 txt.Width = 110; 546 txt.Height = 21; 547 txt.ReadOnly = true; 548 pldetail.Controls.Add(txt); 549 550 551 num++; 552 } 553 //gbdetail.Text = "扫条码快递号对应TT订单明细(订单ID:" + orderid + " 订单号:" + this.OrderNO + ")"; 554 txtorderid.Text = this.OrderID; 555 txtorderno.Text = this.OrderNO; 556 gbdetail.Visible = true; 557 //pldetail.Focus(); 558 } 559 else 560 { 561 logclass.Info("订单ID(" + orderid + " 订单号:" + this.OrderNO + ")在tt上匹配不了数据"); 562 } 563 } 564 565 private void BindCbbPrintState(ref ComboBox cbb, string ttkey, string strcurrent) 566 { 567 //'打印状态(0:未打印 1:已打印 2:已退回)' 568 cbb.Items.Add("已打印"); 569 cbb.Items.Add(""); 570 cbb.Items.Add("未打印"); 571 cbb.Items.Add("已退回"); 572 cbb.SelectedIndex = 0; 573 if (!string.IsNullOrEmpty(ttkey)) 574 { 575 int index = 0; 576 foreach (var item in cbb.Items) 577 { 578 if (item.ToString() == ttkey) 579 { 580 cbb.SelectedIndex = index; 581 cbb.Enabled = false; 582 break; 583 } 584 index++; 585 } 586 } 587 else 588 { 589 if (!string.IsNullOrEmpty(strcurrent)) 590 { 591 int index = 0; 592 foreach (var item in cbb.Items) 593 { 594 if (item.ToString() == strcurrent) 595 { 596 cbb.SelectedIndex = index; 597 break; 598 } 599 index++; 600 } 601 } 602 } 603 } 604 private string GetPrintState(string key) 605 { 606 //'打印状态(0:未打印 1:已打印 2:已退回)' 607 string result = ""; 608 switch (key) 609 { 610 case "0": 611 result = "未打印"; 612 break; 613 case "1": 614 result = "已打印"; 615 break; 616 case "2": 617 result = "已退回"; 618 break; 619 default: 620 break; 621 } 622 return result; 623 } 624 625 private void BindCbbPrintType(ref ComboBox cbb, string ttkey, string strcurrent) 626 { 627 //打印方式(1:创打 2:模打 3:机场打印 4;客票未使用 5:客票已退票 6:客人取消) 628 cbb.Items.Add("创打"); 629 cbb.Items.Add(""); 630 cbb.Items.Add("模打"); 631 cbb.Items.Add("机场打印"); 632 cbb.Items.Add("客票未使用"); 633 cbb.Items.Add("客票已退票"); 634 cbb.Items.Add("客人取消"); 635 cbb.SelectedIndex = 0; 636 if (!string.IsNullOrEmpty(ttkey)) 637 { 638 int index = 0; 639 foreach (var item in cbb.Items) 640 { 641 if (item.ToString() == ttkey) 642 { 643 cbb.SelectedIndex = index; 644 cbb.Enabled = false; 645 break; 646 } 647 index++; 648 } 649 } 650 else 651 { 652 if (!string.IsNullOrEmpty(strcurrent)) 653 { 654 int index = 0; 655 foreach (var item in cbb.Items) 656 { 657 if (item.ToString() == strcurrent) 658 { 659 cbb.SelectedIndex = index; 660 break; 661 } 662 index++; 663 } 664 } 665 } 666 } 667 private string GetPrintType(string key) 668 { 669 ////打印方式(1:创打 2:模打 3:机场打印 4;客票未使用 5:客票已退票 6:客人取消)' 670 string result = ""; 671 switch (key) 672 { 673 case "1": 674 result = "创打"; 675 break; 676 case "2": 677 result = "模打"; 678 break; 679 case "3": 680 result = "机场打印"; 681 break; 682 case "4": 683 result = "客票未使用"; 684 break; 685 case "5": 686 result = "客票已退票"; 687 break; 688 case "6": 689 result = "客人取消"; 690 break; 691 default: 692 break; 693 } 694 return result; 695 } 696 697 private void BindCbbPostWay(ref ComboBox cbb, string ttkey, string strcurrent) 698 { 699 //'邮寄方式(1 公司自取 2 其它 12 韵达月结 13 韵达到付 4 汇通月结 5 汇通到付 9 顺丰到付 10 顺丰月结 11 邮政小包 700 cbb.Items.Add("韵达月结"); 701 cbb.Items.Add(""); 702 cbb.Items.Add("公司自取"); 703 cbb.Items.Add("其它"); 704 cbb.Items.Add("韵达到付"); 705 cbb.Items.Add("汇通月结"); 706 cbb.Items.Add("已打印"); 707 cbb.Items.Add("汇通到付"); 708 cbb.Items.Add("顺丰到付"); 709 cbb.Items.Add("顺丰月结"); 710 cbb.Items.Add("邮政小包"); 711 cbb.SelectedIndex = 0; 712 if (!string.IsNullOrEmpty(ttkey)) 713 { 714 int index = 0; 715 foreach (var item in cbb.Items) 716 { 717 if (item.ToString() == ttkey) 718 { 719 cbb.SelectedIndex = index; 720 cbb.Enabled = false; 721 break; 722 } 723 index++; 724 } 725 } 726 else 727 { 728 if (!string.IsNullOrEmpty(strcurrent)) 729 { 730 int index = 0; 731 foreach (var item in cbb.Items) 732 { 733 if (item.ToString() == strcurrent) 734 { 735 cbb.SelectedIndex = index; 736 break; 737 } 738 index++; 739 } 740 } 741 } 742 } 743 private string GetPostWay(string key) 744 { 745 //'邮寄方式(1 公司自取 2 其它 12 韵达月结 13 韵达到付 4 汇通月结 5 汇通到付 9 顺丰到付 10 顺丰月结 11 邮政小包 746 string result = ""; 747 switch (key) 748 { 749 case "1": 750 result = "公司自取"; 751 break; 752 case "2": 753 result = "其它"; 754 break; 755 case "12": 756 result = "韵达月结"; 757 break; 758 case "13": 759 result = "韵达到付"; 760 break; 761 case "4": 762 result = "汇通月结"; 763 break; 764 case "5": 765 result = "汇通到付"; 766 break; 767 case "9": 768 result = "顺丰到付"; 769 break; 770 case "10": 771 result = "顺丰月结"; 772 break; 773 case "11": 774 result = "邮政小包"; 775 break; 776 default: 777 break; 778 } 779 return result; 780 } 781 782 private void btnensure_Click(object sender, EventArgs e) 783 { 784 if (!gbdetail.Visible) 785 { 786 return; 787 } 788 789 if (this.PsgCount > 0 && !string.IsNullOrEmpty(this.OrderID) && !string.IsNullOrEmpty(this.Username)) 790 { 791 List<modelt_tripexpress> list = new List<modelt_tripexpress>(); 792 modelt_tripexpress m; 793 bool ischange = false; 794 string tripno = string.Empty; 795 string printstate = string.Empty; 796 string printtype = string.Empty; 797 string postway = string.Empty; 798 TextBox txt; 799 ComboBox cbb; 800 for (int i = 0; i < PsgCount; i++) 801 { 802 //打印状态 printState、行程单号 travelNo 、打印方式 printType、邮寄方式 postWay、邮寄单号 postNo 803 txt = pldetail.FindControl("txttravelNo" + i.ToString()).Cast<TextBox>(out ischange); 804 tripno = txt.Text.Trim(); 805 if (string.IsNullOrEmpty(tripno)) 806 { 807 lbl_msg.Text = "提示信息:" + "第" + (i + 1).ToString() + "个乘客的行程单号不能为空"; 808 txt.Focus(); 809 txt.SelectAll(); 810 return; 811 } 812 cbb = pldetail.FindControl("cbbprintState" + i.ToString()).Cast<ComboBox>(out ischange); 813 printstate = cbb.SelectedItem.ToString(); 814 if (string.IsNullOrEmpty(printstate)) 815 { 816 lbl_msg.Text = "提示信息:" + "第" + (i + 1).ToString() + "个乘客的打印状态不能为空"; 817 cbb.Focus(); 818 return; 819 } 820 cbb = pldetail.FindControl("cbbprintType" + i.ToString()).Cast<ComboBox>(out ischange); 821 printtype = cbb.SelectedItem.ToString(); 822 if (string.IsNullOrEmpty(printtype)) 823 { 824 lbl_msg.Text = "提示信息:" + "第" + (i + 1).ToString() + "个乘客的打印方式不能为空"; 825 cbb.Focus(); 826 return; 827 } 828 cbb = pldetail.FindControl("cbbpostWay" + i.ToString()).Cast<ComboBox>(out ischange); 829 postway = cbb.SelectedItem.ToString(); 830 if (string.IsNullOrEmpty(postway)) 831 { 832 lbl_msg.Text = "提示信息:" + "第" + (i + 1).ToString() + "个乘客的邮寄方式不能为空"; 833 cbb.Focus(); 834 return; 835 } 836 837 838 839 m = new modelt_tripexpress(); 840 m.operater = this.Username; 841 m.orderid = this.OrderID; 842 m.postway = postway; 843 m.printstate = printstate; 844 m.printtype = printtype; 845 m.expno = this.ExpNo; 846 m.tktno = pldetail.FindControl("txttktNo1" + i.ToString()).Cast<TextBox>(out ischange).Text.Trim(); 847 m.tripno = tripno; 848 m.creattime = DateTime.Now; 849 list.Add(m); 850 } 851 string msg = string.Empty; 852 if (new blltripexpress().InsertList1(list, Config.CONSQL_172_16_6_1_WRITE, ref msg) > 0) 853 { 854 //MessageBox.Show("行程单号保存成功!"); 855 string msg1 = "提示信息:(订单ID:" + this.OrderID + " 订单号:" + this.OrderNO + ")行程单号保存成功!"; 856 BindData(OrderID); 857 txtbarcode.Text = ""; 858 lbl_msg.Text = msg1; 859 } 860 else 861 { 862 //MessageBox.Show("添加失败"); 863 lbl_msg.Text = "提示信息:(订单ID:" + this.OrderID + " 订单号:" + this.OrderNO + ")行程单号添加失败"; 864 } 865 txtbarcode.SelectAll(); 866 txtbarcode.Focus(); 867 } 868 else 869 { 870 //MessageBox.Show("没有匹配到数据"); 871 lbl_msg.Text = "提示信息:(订单ID:" + this.OrderID + " 订单号:" + this.OrderNO + ")行程单号没有匹配到数据"; 872 } 873 874 } 875 private void txt_MouseDoubleClick(object sender, MouseEventArgs e) 876 { 877 ((TextBox)sender).SelectAll(); 878 } 879 private string GetTikStateDesc(object item) 880 { 881 string result = "已出票"; 882 string pTikState = item == null ? "0" : item.ToString().Trim(); 883 //pTikState 客户行程状态(0已出票 1已退票 2已改签) 884 switch (pTikState) 885 { 886 case "0": 887 result = "已出票"; 888 break; 889 case "1": 890 result = "已退票"; 891 break; 892 case "2": 893 result = "已改签"; 894 break; 895 default: 896 break; 897 } 898 return result; 899 } 900 private string GetPtypeDesc(object item) 901 { 902 string result = "成人"; 903 string Ptype = item == null ? "1" : item.ToString().Trim(); 904 ////乘机人类型(1成人2儿童3婴儿4老人) 905 switch (Ptype) 906 { 907 case "1": 908 result = "成人"; 909 break; 910 case "2": 911 result = "儿童"; 912 break; 913 case "3": 914 result = "婴儿"; 915 break; 916 case "4": 917 result = "老人"; 918 break; 919 default: 920 break; 921 } 922 return result; 923 } 924 private void tripmain_Load(object sender, EventArgs e) 925 { 926 if (string.IsNullOrEmpty(this.Username)) 927 { 928 MessageBox.Show("非法操作"); 929 lbl_msg.Text = "提示信息:" + "非法操作"; 930 this.Close(); 931 } 932 else 933 { 934 Application.AddMessageFilter(this); 935 this.MouseWheel += new MouseEventHandler(pnl_MouseWheel); 936 dtpbegin.Value = DateTime.Now.AddDays(-28); 937 lbl_msg.Text = ""; 938 timer1.Interval = 1000 * 60 * 2;//毫秒为单位 939 timer1.Start(); 940 gbdetail.Visible = false; 941 this.Text = string.Format("快递单扫描======={0},欢迎您！", this.Username); 942 943 } 944 } 945 private void tripmain_KeyDown(object sender, KeyEventArgs e) 946 { 947 if (e.Control && e.KeyCode == Keys.Tab) 948 { 949 //这里写你摁下 Tab之后所要进行的动作的代码 950 txtbarcode.Focus(); 951 txtbarcode.SelectAll(); 952 } 953 } 954 //窗口置顶 955 private void timer1_Tick(object sender, EventArgs e) 956 { 957 this.TopMost = false; 958 this.BringToFront(); 959 this.TopMost = true; 960 } 961 private void tripmain_FormClosed(object sender, FormClosedEventArgs e) 962 { 963 timer1.Stop(); 964 965 if (e.CloseReason == CloseReason.WindowsShutDown) 966 { 967 //添加所需使用的代码 968 logclass.Debug(DateTime.Now + "【电脑关机或者被注销" + "===系统用户：" + System.Environment.UserName + "】"); 969 } 970 if (e.CloseReason == CloseReason.TaskManagerClosing) 971 { 972 //添加所需使用的代码 973 logclass.Debug(DateTime.Now + "【任务管理器关闭" + "===系统用户：" + System.Environment.UserName + "】"); 974 } 975 if (e.CloseReason == CloseReason.None) 976 { 977 //添加所需使用的代码 978 logclass.Debug(DateTime.Now + "【未知意外关闭" + "===系统用户：" + System.Environment.UserName + "】"); 979 } 980 } 981 private void tripmain_FormClosing(object sender, FormClosingEventArgs e) 982 { 983 logclass.Debug("==程序关闭=="); 984 System.Environment.Exit(System.Environment.ExitCode); 985 } 986 private void tripmain_Activated(object sender, EventArgs e) 987 { 988 txtbarcode.Focus(); 989 990 //this.AcceptButton = btnensure; 991 } 992 993 private void linkurl_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e) 994 { 995 BrowserHelper.OpenIe(string.Format("http:www.test.com?orderFrom={1}&id={0}", this.OrderID, this.orderFrom)); 996 } 997 998 private void cbb_SelectedIndexChanged(object sender, EventArgs e) 999 { 1000 ComboBox cbb = ((ComboBox)sender); 1001 bool ischange = false; 1002 int index = cbb.SelectedIndex; 1003 string name = cbb.Name; 1004 int current = int.Parse(name.Substring(name.Length - 1)); 1005 string tempname = name.Substring(0, name.Length - 1); 1006 1007 for (int i = 0; i < PsgCount; i++) 1008 { 1009 if (current == i) 1010 continue; 1011 pldetail.FindControl(tempname + i.ToString()).Cast<ComboBox>(out ischange).SelectedIndex = index; 1012 } 1013 1014 1015 } 1016 /// <summary> 1017 /// 鼠标滚动 1018 /// </summary> 1019 /// <param name="sender"></param> 1020 /// <param name="e"></param> 1021 private void pnl_MouseWheel(object sender, MouseEventArgs e) 1022 { 1023 //获取光标位置 1024 Point mousePoint = new Point(e.X, e.Y); 1025 //换算成相对本窗体的位置 1026 mousePoint.Offset(this.Location.X, this.Location.Y); 1027 //判断是否在panel内 1028 if (pldetail.RectangleToScreen(pldetail.DisplayRectangle).Contains(mousePoint)) 1029 { 1030 //滚动 1031 pldetail.AutoScrollPosition = new Point(0, pldetail.VerticalScroll.Value - e.Delta); 1032 } 1033 } 1034 1035 1036 1037 1038 } 1039 }
效果图：
ExcelHelper:
using System; using System.Collections.Generic; using System.Text; using System.Data; using System.IO; using System.Data.Odbc; using System.Data.OleDb; using System.Web; using NPOI.XSSF.UserModel; using NPOI.HSSF.UserModel; using NPOI.SS.UserModel; using ICSharpCode.SharpZipLib.Zip; //using NPOI.SS.Formula.Functions; public class ExcelHelper { /// <summary> /// DataTable创建excel 2003 .xls /// </summary> /// <param name="dt"></param> /// <param name="path"></param> /// <param name="name"></param> /// <returns></returns> public static bool CreateExcel(DataTable dt, string path, string name) { List<string> exceltitlelist = new List<string>(); foreach (DataColumn dc in dt.Columns) { exceltitlelist.Add(dc.ColumnName); } try { NPOI.HSSF.UserModel.HSSFWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook(); NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet(name); sheet.AutoSizeColumn(0); var cellFont = workbook.CreateFont(); var cellStyle = workbook.CreateCellStyle(); var cellStyle2 = workbook.CreateCellStyle(); ////- 加粗，白色前景色 cellFont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold; ////- 这个是填充的模式，可以是网格、花式等。如果需要填充单色，请使用：SOLID_FOREGROUND ////cellStyle.FillPattern = NPOI.SS.UserModel.FillPatternType.SOLID_FOREGROUND; ////- 设置这个样式的字体，如果没有设置，将与所有单元格拥有共同字体！ cellStyle.SetFont(cellFont); cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; cellStyle2.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; //CellStyle cellStyleDate = workbook.CreateCellStyle(); //DataFormat format = workbook.CreateDataFormat(); //cellStyleDate.DataFormat = format.GetFormat("yyyy年m月d日"); string[] titles = exceltitlelist.ToArray(); int rowIndex = 0; NPOI.SS.UserModel.IRow row = sheet.CreateRow(rowIndex); for (int i = 0; i < titles.Length; i++) //生成sheet第一行列名 { NPOI.SS.UserModel.ICell celltmp = row.CreateCell(i); celltmp.SetCellValue(titles[i]); celltmp.CellStyle = cellStyle; } NPOI.SS.UserModel.ICell cell; rowIndex++; string tmp; DataRow m; for (int i = 0; i < dt.Rows.Count; i++) { try { m = dt.Rows[i]; row = sheet.CreateRow(rowIndex); for (int j = 0; j < titles.Length; j++) { cell = row.CreateCell(j); cell.CellStyle = cellStyle2; cell.SetCellValue(m.ItemArray[j].ToString()); } rowIndex++; } catch (Exception e1) { //logclass.Debug("===== 生成excel报错 =====" + e1.Message); } } sheet.ForceFormulaRecalculation = true; using (FileStream file = new FileStream(path, FileMode.Create)) { workbook.Write(file); //创建xls文件。 file.Close(); } } catch (Exception e) { //policyframework.common.logclass.Debug("=====CreateExcel 生成excel报错 =====" + e.Message); return false; } return true; } /// <summary> /// DataTable创建excel 2007 .xlsx /// </summary> /// <param name="dt"></param> /// <param name="path"></param> /// <param name="name"></param> /// <returns></returns> public static bool CreateExcel2007(DataTable dt, string path, string name) { List<string> exceltitlelist = new List<string>(); foreach (DataColumn dc in dt.Columns) { exceltitlelist.Add(dc.ColumnName); } try { NPOI.XSSF.UserModel.XSSFWorkbook workbook = new XSSFWorkbook(); NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet(name); sheet.AutoSizeColumn(0); var cellFont = workbook.CreateFont(); var cellStyle = workbook.CreateCellStyle(); var cellStyle2 = workbook.CreateCellStyle(); ////- 加粗，白色前景色 cellFont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold; ////- 这个是填充的模式，可以是网格、花式等。如果需要填充单色，请使用：SOLID_FOREGROUND ////cellStyle.FillPattern = NPOI.SS.UserModel.FillPatternType.SOLID_FOREGROUND; ////- 设置这个样式的字体，如果没有设置，将与所有单元格拥有共同字体！ cellStyle.SetFont(cellFont); cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; cellStyle2.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; //CellStyle cellStyleDate = workbook.CreateCellStyle(); //DataFormat format = workbook.CreateDataFormat(); //cellStyleDate.DataFormat = format.GetFormat("yyyy年m月d日"); string[] titles = exceltitlelist.ToArray(); int rowIndex = 0; NPOI.SS.UserModel.IRow row = sheet.CreateRow(rowIndex); for (int i = 0; i < titles.Length; i++) //生成sheet第一行列名 { NPOI.SS.UserModel.ICell celltmp = row.CreateCell(i); celltmp.SetCellValue(titles[i]); celltmp.CellStyle = cellStyle; } NPOI.SS.UserModel.ICell cell; rowIndex++; string tmp; DataRow m; for (int i = 0; i < dt.Rows.Count; i++) { try { m = dt.Rows[i]; row = sheet.CreateRow(rowIndex); for (int j = 0; j < titles.Length; j++) { cell = row.CreateCell(j); cell.CellStyle = cellStyle2; cell.SetCellValue(m.ItemArray[j].ToString()); } rowIndex++; } catch (Exception e1) { //logclass.Debug("===== 生成excel报错 =====" + e1.Message); } } sheet.ForceFormulaRecalculation = true; using (FileStream file = new FileStream(path, FileMode.Create)) { workbook.Write(file); //创建xlsx文件。 file.Close(); } } catch (Exception e) { //policyframework.common.logclass.Debug("=====CreateExcel 生成excel报错 =====" + e.Message); return false; } return true; } /// <summary> /// 相对路径 /// </summary> /// <param name="name"></param> /// <returns></returns> public static DataTable NPOILoadExcel(string name) { FileStream fs = null; IWorkbook book; try { fs = File.OpenRead(name); if (name.IndexOf(".xlsx") > -1) { book = new XSSFWorkbook(fs); } else if (name.IndexOf(".xls") > -1) { book = new HSSFWorkbook(fs); } else { book = null; } } catch { throw new Exception("导入文件错误"); } finally { fs.Dispose(); fs.Close(); } if (book == null) { throw new Exception("导入文件格式错误"); } ISheet sheet = book.GetSheetAt(0); if (sheet == null) { throw new Exception("报表数据不能为空"); } IRow firstRow = sheet.GetRow(0); int cellCount = firstRow.Cells.Count; DataTable dt = new DataTable(); ICell cell; IRow row; try { for (int i = 0; i < cellCount; i++) { cell = firstRow.GetCell(i); if (cell != null) { string cellValue = cell.StringCellValue; if (cellValue != null) { DataColumn column = new DataColumn(cellValue); dt.Columns.Add(column); } } } for (int i = 1; i <= sheet.LastRowNum; i++) { row = sheet.GetRow(i); if (row == null) continue; DataRow dataRow = dt.NewRow(); for (int j = 0; j < cellCount; j++) { ICell cell2 = row.GetCell(j); if (cell2 == null) { continue; } else { if (cell2.CellType == CellType.Numeric && DateUtil.IsCellDateFormatted(cell2)) { dataRow[j] = cell2.DateCellValue.ToString(); } else { dataRow[j] = cell2.ToString(); } } } dt.Rows.Add(dataRow); } } catch (Exception ex) { } return dt; } /// <summary> /// 相对路径 /// </summary> /// <param name="name"></param> /// <returns></returns> public static DataTable LoadSheet(ISheet sheet) { if (sheet == null) { return new DataTable(); } DataTable dt = new DataTable(); try { IRow firstRow = sheet.GetRow(0); if (firstRow == null) { return new DataTable(); } int cellCount = firstRow.Cells.Count; ICell cell; IRow row; for (int i = 0; i < cellCount; i++) { cell = firstRow.GetCell(i); if (cell != null) { string cellValue = cell.StringCellValue.Replace(" ", ""); if (cellValue != null) { DataColumn column = new DataColumn(cellValue); dt.Columns.Add(column); } } } for (int i = 1; i <= sheet.LastRowNum; i++) { row = sheet.GetRow(i); if (row != null) { DataRow dataRow = dt.NewRow(); for (int j = 0; j < cellCount; j++) { if (row.GetCell(j) == null) { continue; } dataRow[j] = row.GetCell(j).ToString(); } dt.Rows.Add(dataRow); } } } catch (Exception ex) { } return dt; } /// <summary> /// 将CSV文件的数据读取到DataTable中 /// </summary> /// <param name="fileName">CSV文件路径</param> /// <returns>返回读取了CSV数据的DataTable</returns> public static DataTable OpenCSV(HttpPostedFile file) { DataTable dt = new DataTable(); //StreamReader sr = new StreamReader(fs, Encoding.UTF8); StreamReader sr = new StreamReader(file.InputStream, Encoding.UTF8); //string fileContent = sr.ReadToEnd(); //encoding = sr.CurrentEncoding; //记录每次读取的一行记录 string strLine = ""; //记录每行记录中的各字段内容 string[] aryLine = null; string[] tableHead = null; //标示列数 int columnCount = 0; //标示是否是读取的第一行 bool IsFirst = true; //逐行读取CSV中的数据 while ((strLine = sr.ReadLine()) != null) { //strLine = Common.ConvertStringUTF8(strLine, encoding); //strLine = Common.ConvertStringUTF8(strLine); if (IsFirst == true) { tableHead = strLine.Split(','); IsFirst = false; columnCount = tableHead.Length; //创建列 for (int i = 0; i < columnCount; i++) { DataColumn dc = new DataColumn(tableHead[i]); dt.Columns.Add(dc); } } else { aryLine = strLine.Split(','); DataRow dr = dt.NewRow(); for (int j = 0; j < columnCount; j++) { dr[j] = aryLine[j]; } dt.Rows.Add(dr); } } if (aryLine != null && aryLine.Length > 0) { dt.DefaultView.Sort = tableHead[0] + " " + "asc"; } sr.Close(); return dt; } /// <summary> /// 压缩文件 /// </summary> /// <param name="dir">文件目录</param> /// <param name="zipfilename">zip文件名</param> public static string compressFiles(string files, string zipfilename) { string resfilepath = ""; try { string[] filenames = files.Split('|'); using (ZipOutputStream s = new ZipOutputStream(File.Create(zipfilename))) { s.SetLevel(9); // 0 - store only to 9 - means best compression byte[] buffer = new byte[4096]; foreach (string file in filenames) { ZipEntry entry = new ZipEntry(Path.GetFileName(file)); entry.DateTime = DateTime.Now; s.PutNextEntry(entry); using (FileStream fs = File.OpenRead(file)) { int sourceBytes; do { sourceBytes = fs.Read(buffer, 0, buffer.Length); s.Write(buffer, 0, sourceBytes); } while (sourceBytes > 0); } } s.Finish(); s.Close(); } } catch { } return resfilepath; } /// <summary> /// 将Excel文件中的数据读出到DataTable中(xlsx) /// </summary> /// <param name="file"></param> /// <returns></returns> public static DataTable ExcelToTableForXLSX(string file) { DataTable dt = new DataTable(); using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read)) { XSSFWorkbook xssfworkbook = new XSSFWorkbook(fs); ISheet sheet = xssfworkbook.GetSheetAt(0); //表头 IRow header = sheet.GetRow(sheet.FirstRowNum); if (header != null) { List<int> columns = new List<int>(); for (int i = 0; i < header.LastCellNum; i++) { object obj = GetValueTypeForXLSX(header.GetCell(i) as XSSFCell); if (obj == null || obj.ToString() == string.Empty) { dt.Columns.Add(new DataColumn("Columns" + i.ToString())); //continue; } else dt.Columns.Add(new DataColumn(obj.ToString())); columns.Add(i); } //数据 for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++) { DataRow dr = dt.NewRow(); bool hasValue = false; foreach (int j in columns) { dr[j] = GetValueTypeForXLSX(sheet.GetRow(i).GetCell(j) as XSSFCell); if (dr[j] != null && dr[j].ToString() != string.Empty) { hasValue = true; } } if (hasValue) { dt.Rows.Add(dr); } } } } return dt; } public static DataTable GetExcelDataAsTableNPOI(string fileName) { if (fileName.LastIndexOf('.') > 0 && fileName.Substring(fileName.LastIndexOf('.'), fileName.Length - fileName.LastIndexOf('.')).Contains("xlsx")) { return ExcelToTableForXLSX(fileName); } using (FileStream fs = new FileStream(fileName, FileMode.Open)) { HSSFWorkbook wb = new HSSFWorkbook(fs); NPOI.SS.UserModel.ISheet sheet = wb.GetSheetAt(0); DataTable table = new DataTable(); //由第一列取標題做為欄位名稱 NPOI.SS.UserModel.IRow headerRow = sheet.GetRow(0); if (headerRow != null) { int cellCount = headerRow.LastCellNum; for (int i = headerRow.FirstCellNum; i < cellCount; i++) //以欄位文字為名新增欄位，此處全視為字串型別以求簡化 table.Columns.Add( new DataColumn(headerRow.GetCell(i).StringCellValue)); NPOI.SS.UserModel.IRow row; DataRow dataRow; //略過第零列(標題列)，一直處理至最後一列 for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++) { row = sheet.GetRow(i); if (row == null) continue; dataRow = table.NewRow(); //依先前取得的欄位數逐一設定欄位內容 for (int j = row.FirstCellNum; j < cellCount; j++) if (row.GetCell(j) != null) //如要針對不同型別做個別處理，可善用.CellType判斷型別 //再用.StringCellValue, .DateCellValue, .NumericCellValue...取值 //此處只簡單轉成字串 dataRow[j] = row.GetCell(j).ToString(); table.Rows.Add(dataRow); } } return table; } } /// <summary> /// 获取单元格类型(xlsx) /// </summary> /// <param name="cell"></param> /// <returns></returns> public static object GetValueTypeForXLSX(XSSFCell cell) { if (cell == null) return null; switch (cell.CellType) { case CellType.Blank: //BLANK: return null; case CellType.Boolean: //BOOLEAN: return cell.BooleanCellValue; case CellType.Numeric: //NUMERIC: return cell.NumericCellValue; case CellType.String: //STRING: return cell.StringCellValue; case CellType.Error: //ERROR: return cell.ErrorCellValue; case CellType.Formula: //FORMULA: default: return "=" + cell.CellFormula; } } }
