public string ImportData() { int error = 0; string message = "请选择格式正确的文件再导入!"; string falseMessage = ""; int count = HttpContext.Request.Files.Count; if (count > 0) { HttpPostedFileBase file = HttpContext.Request.Files[0]; if (string.IsNullOrEmpty(file.FileName)) { return(message); } if (!(file.FileName.Substring(file.FileName.IndexOf('.')).Contains("xls") || file.FileName.Substring(file.FileName.IndexOf('.')).Contains("xlsx"))) { return(message); } string fileName = DateTime.Now.ToString("yyyyMMddHHmmss") + System.IO.Path.GetExtension(file.FileName); file.SaveAs(Server.MapPath("~/Resource/temp/" + fileName)); DataTable dt = ExcelHelper.ExcelImport(Server.MapPath("~/Resource/temp/" + fileName)); int order = 1; DataItemDetailBLL dataItemDetailBLL = new DataItemDetailBLL(); List <DataItemModel> data = dataItemDetailBLL.GetDataItemListByItemCode("'CarNo'").ToList(); for (int i = 0; i < dt.Rows.Count; i++) { //车牌号 string CarNo = dt.Rows[i][0].ToString(); //车辆品牌系列 string Model = dt.Rows[i][1].ToString(); ////驾驶人 //string Dirver = dt.Rows[i][2].ToString(); ////驾驶人电话 //string Phone = dt.Rows[i][3].ToString(); //最近年检日期 string Time = dt.Rows[i][2].ToString(); //起始时间 string StartTime = dt.Rows[i][3].ToString(); ////结束时间 string EndTime = dt.Rows[i][4].ToString(); //荷载人数 string Num = dt.Rows[i][5].ToString(); if (CarNo.Trim() == "") { falseMessage += "</br>" + "第" + (i + 2) + "车牌号为空,未能导入."; error++; continue; } CarNo = CarNo.Trim().ToUpper();//英文转换为大写 string s = CarNo.Substring(0, 1); bool flag = false; foreach (var d in data) { if (d.ItemName == s) { flag = true; break; } } if (flag == false) { falseMessage += "</br>" + "第" + (i + 2) + "车牌号输入格式错误,第一位请输入正确的省缩写,未能导入."; error++; continue; } //判断车牌号位数是否合法 if (!Regex.IsMatch(CarNo.Trim(), "(^[京津沪渝冀豫云辽黑湘皖鲁新苏浙赣鄂桂甘晋蒙陕吉闽贵粤青藏川宁琼使领A-Z]{1}[A-Z]{1}[A-Z0-9]{4}[A-Z0-9挂学警港澳]{1}$)")) { falseMessage += "</br>" + "第" + (i + 2) + "车牌号填写错误,未能导入."; error++; continue; } if (Model.Trim() == "") { falseMessage += "</br>" + "第" + (i + 2) + "内部编号为空,未能导入."; error++; continue; } //if (Dirver.Trim() == "") //{ // falseMessage += "</br>" + "第" + (i + 2) + "驾驶人为空,未能导入."; // error++; // continue; //} //if (Phone.Trim() == "") //{ // falseMessage += "</br>" + "第" + (i + 2) + "驾驶人电话为空,未能导入."; // error++; // continue; //} if (Time.Trim() == "") { falseMessage += "</br>" + "第" + (i + 2) + "最近年检日期为空,未能导入."; error++; continue; } if (StartTime.Trim() == "") { falseMessage += "</br>" + "第" + (i + 2) + "起始时间为空,未能导入."; error++; continue; } if (EndTime.Trim() == "") { falseMessage += "</br>" + "第" + (i + 2) + "结束时间为空,未能导入."; error++; continue; } if (Num.Trim() == "") { falseMessage += "</br>" + "第" + (i + 2) + "荷载人数为空,未能导入."; error++; continue; } DateTime dtime; try { DateTime.TryParse(Time, out dtime); if (dtime.ToString("yyyy-MM-dd") == "0001-01-01") { falseMessage += "</br>" + "第" + (i + 2) + "最近年检日期格式不对,请输入yyyy-MM-dd格式,未能导入."; error++; continue; } } catch (Exception e) { falseMessage += "</br>" + "第" + (i + 2) + "最近年检日期格式不对,请输入yyyy-MM-dd格式,未能导入."; error++; continue; } DateTime Stime; DateTime.TryParse(StartTime, out Stime); if (Stime.ToString("yyyy-MM-dd") == "0001-01-01") { falseMessage += "</br>" + "第" + (i + 2) + "起始时间格式不对,请输入yyyy-MM-dd格式,未能导入."; error++; continue; } DateTime Etime; DateTime.TryParse(StartTime, out Etime); if (Etime.ToString("yyyy-MM-dd") == "0001-01-01") { falseMessage += "</br>" + "第" + (i + 2) + "结束时间格式不对,请输入yyyy-MM-dd格式,未能导入."; error++; continue; } if (carinfobll.GetCarNoIsRepeat(CarNo, "")) { falseMessage += "</br>" + "第" + (i + 2) + "车牌号数据已存在,请勿重复录入."; error++; continue; } Etime = Convert.ToDateTime(Etime.ToString("yyyy-MM-dd 23:59:59")); Stime = Convert.ToDateTime(Stime.ToString("yyyy-MM-dd 00:00:00")); CarinfoEntity hf = new CarinfoEntity(); hf.CarNo = CarNo.Trim(); hf.GpsId = ""; hf.GpsName = ""; //hf.Dirver = Dirver; hf.InsperctionDate = dtime; hf.Model = Model; hf.NumberLimit = Convert.ToInt32(Num); hf.Endtime = Etime; hf.Starttime = Stime; //hf.Phone = Phone; hf.NextInsperctionDate = dtime.AddDays(-1).AddYears(1); hf.Type = 0; try { DataItemDetailBLL pdata = new DataItemDetailBLL(); var pitem = pdata.GetItemValue("Hikappkey"); //海康服务器密钥 var url = pdata.GetItemValue("HikBaseUrl"); //海康服务器地址 carinfobll.SaveForm("", hf, pitem, url); } catch { error++; } } count = dt.Rows.Count; message = "共有" + count + "条记录,成功导入" + (count - error) + "条,失败" + error + "条"; message += "</br>" + falseMessage; } return(message); }
private bool XingYe(string BankId, string FileName) { try { DataTable dt = new DataTable(); dt = ExcelToDataTable(System.Web.HttpContext.Current.Server.MapPath("~/UpLoadFiles/ApplyCreditCard/" + FileName), "Sheet1", true); if (dt == null) { return(false); } ApplyCreditCard ApplyCreditCard; DateTime Stime; DateTime Etime; string Name = string.Empty; string Birthday = string.Empty; foreach (DataRow row in dt.Rows) { Stime = Convert.ToDateTime(row[0].ToString()); Etime = Stime.AddDays(1); Name = row[3].ToString(); Birthday = row[5].ToString().Replace("-", "").Replace("/", ""); string state = row[8].ToString(); switch (state) { case "拒件": ApplyCreditCard = Entity.ApplyCreditCard.FirstOrDefault(f => f.UserName == Name && f.UserIdCard.Contains(Birthday) && f.AddTime > Stime && f.AddTime < Etime); if (ApplyCreditCard == null) { continue; } ApplyCreditCard.State = 5; Entity.SaveChanges(); break; case "待转人工审核": ApplyCreditCard = Entity.ApplyCreditCard.FirstOrDefault(f => f.UserName == Name && f.UserIdCard.Contains(Birthday) && f.AddTime > Stime && f.AddTime < Etime); if (ApplyCreditCard == null) { continue; } ApplyCreditCard.State = 6; Entity.SaveChanges(); break; case "过件未发卡": ApplyCreditCard = Entity.ApplyCreditCard.FirstOrDefault(f => f.UserName == Name && f.UserIdCard.Contains(Birthday) && f.AddTime > Stime && f.AddTime < Etime); if (ApplyCreditCard == null) { continue; } ApplyCreditCard.State = 3; Entity.SaveChanges(); break; case "过件已发卡": ApplyCreditCard = Entity.ApplyCreditCard.FirstOrDefault(f => f.UserName == Name && f.UserIdCard.Contains(Birthday) && f.AddTime > Stime && f.AddTime < Etime); if (ApplyCreditCard == null) { continue; } ApplyCreditCard.State = 4; Entity.SaveChanges(); break; case "转人工审核中": ApplyCreditCard = Entity.ApplyCreditCard.FirstOrDefault(f => f.UserName == Name && f.UserIdCard.Contains(Birthday) && f.AddTime > Stime && f.AddTime < Etime); if (ApplyCreditCard == null) { continue; } ApplyCreditCard.State = 2; Entity.SaveChanges(); break; default: break; } } return(true); } catch (Exception) { return(false); } }