Controller控制器
1 /// <summary> 2 /// 上传模板文件 3 /// </summary> 4 /// <returns></returns> 5 public ActionResult BulkInserCustomer() 6 { 7 HttpPostedFileBase fileData = Request.Files[0]; 8 if (fileData.ContentLength == 0) 9 { 10 return Json(new { result = 0, Errmsg = "请选择上传文件" }); 11 } 12 13 if (fileData.FileName != "客户人员信息导入-精简模板.xls") 14 { 15 return Json(new { result = 0, Errmsg = "文件有误" }); 16 } 17 18 string strExtension = Path.GetExtension(fileData.FileName).ToLower(); 19 20 if (strExtension == ".xls" || strExtension == ".xlsx") 21 { 22 double dFileSiz = fileData.ContentLength; 23 if (dFileSiz > 1024 * 1024 * 4) 24 { 25 return Json(new { result = 0, Errmsg = "文件过大" }); 26 } 27 28 string new_fileName = Guid.NewGuid().ToString() + "_" + Path.GetFileName(fileData.FileName); 29 30 //哈希码解决生成多个文件夹，提高上传效率 31 int hash_code = new_fileName.GetHashCode(); 32 int dir1 = hash_code & 0xf; 33 hash_code = hash_code >> 4; 34 int dir2 = hash_code & 0xf; 35 36 string targetFilePath = Path.Combine(Request.MapPath("~/UploadFile/BulkImport/"), dir1.ToString(), dir2.ToString()); 37 if (!Directory.Exists(targetFilePath))//文件夹不存在的时候创建 38 { 39 Directory.CreateDirectory(targetFilePath); 40 } 41 targetFilePath = Path.Combine(targetFilePath, new_fileName);//文件名与目录拼接 42 fileData.SaveAs(targetFilePath); 43 return Json(new { result = 1, Errmsg = targetFilePath, _fileName = fileData.FileName }); 44 45 } 46 return Json(new { result = 0, Errmsg = "文件格式错误" }); 47 } 48 49 private static List<TempMember> coopList; 50 51 public ActionResult CheckDataResult() 52 { 53 string fileName = Request["FilePath"]; 54 List<BuliImportHelp> help = null; 55 coopList = CTMBLL.CheckCustomerData(fileName, curCompanyId, curStaffLgAccID, ref help); 56 57 return Json(new { CoopList = coopList, Help = help }); 58 } 59 60 public ActionResult BulkInsertResult() 61 { 62 int result = 0; 63 result = CTMBLL.BulkCustomerInfo(coopList); 64 65 return Json(new { result = result }); 66 }
BLL业务逻辑层
1 /// <summary> 2 /// 验证客户信息 3 /// </summary> 4 /// <param name="fileName"></param> 5 /// <param name="companyID"></param> 6 /// <param name="Uplist"></param> 7 /// <returns></returns> 8 public List<TempMember> CheckCustomerData(string fileName, long companyID, long TMAddUserId, ref List<BuliImportHelp> Uplist) 9 { 10 List<TempMember> list = new List<TempMember>(); 11 Uplist = new List<BuliImportHelp>(); 12 List<string> rTCNOTMPhone = new List<string>(); 13 14 IWorkbook workbook; 15 using (FileStream fsRead = File.OpenRead(fileName)) 16 { 17 workbook = new HSSFWorkbook(fsRead); 18 ISheet sheet = workbook.GetSheetAt(0); 19 20 for (int r = 2; r <= sheet.LastRowNum; r++) 21 { 22 IRow currentRow = sheet.GetRow(r); 23 24 Vehicle veModel = new Vehicle(); 25 BuliImportHelp upModel = new BuliImportHelp(); 26 27 if (currentRow == null) { break; } 28 29 string TCNO = currentRow.GetCell(0) == null ? "" : currentRow.GetCell(0).ToString().Trim(); 30 string TCShortName = currentRow.GetCell(1) == null ? "" : currentRow.GetCell(1).ToString().Trim(); 31 string TMName = currentRow.GetCell(2) == null ? "" : currentRow.GetCell(2).ToString().Trim(); 32 string TMPhone = currentRow.GetCell(3) == null ? "" : currentRow.GetCell(3).ToString().Trim(); 33 var TCID = TMDAL.GetTempCompanyID(TCNO, companyID); 34 if (TCNO == "" && TCShortName == "" && TMName == "" && TMPhone == "") { break; } 35 36 if (TCNO == "" || TCShortName == "" || TMName == "" || TMPhone == "") 37 { 38 BuliImportHelp BuHelp = new BuliImportHelp(); 39 BuHelp.Row = (r + 1).ToString(); 40 BuHelp.TCNO = TCNO; 41 BuHelp.TCShortName = TCShortName; 42 BuHelp.TMName = TMName; 43 BuHelp.TMPhone = TMPhone; 44 if (TCNO == "") { BuHelp.MrrEsg = "客户编号不能为空"; } 45 else if (TCShortName == "") { BuHelp.MrrEsg = "客户简称不能为空"; } 46 else if (TMName == "") { BuHelp.MrrEsg = "成员姓名不能为空"; } 47 else if (TMPhone == "") { BuHelp.MrrEsg = "成员电话不能为空"; } 48 Uplist.Add(BuHelp); 49 } 50 else if (!UtilityHelp.isCellPhoneNumber(TMPhone)) 51 { 52 BuliImportHelp BuHelp = new BuliImportHelp(); 53 BuHelp.Row = (r + 1).ToString(); 54 BuHelp.TCNO = TCNO; 55 BuHelp.TCShortName = TCShortName; 56 BuHelp.TMName = TMName; 57 BuHelp.TMPhone = TMPhone; 58 BuHelp.MrrEsg = "手机号码格式有误"; 59 Uplist.Add(BuHelp); 60 } 61 else 62 { 63 bool count = true; 64 if (rTCNOTMPhone.IndexOf(TCNO + TMPhone) != -1) 65 { 66 BuliImportHelp BuHelp = new BuliImportHelp(); 67 BuHelp.Row = (r + 1).ToString(); 68 BuHelp.TCNO = TCNO; 69 BuHelp.TCShortName = TCShortName; 70 BuHelp.TMName = TMName; 71 BuHelp.TMPhone = TMPhone; 72 BuHelp.MrrEsg = "导入模板中手机号码重复"; 73 Uplist.Add(BuHelp); 74 count = false; 75 } 76 else { rTCNOTMPhone.Add(TCNO + TMPhone); } 77 78 79 if (TCID == 0) 80 { 81 BuliImportHelp BuHelp = new BuliImportHelp(); 82 BuHelp.Row = (r + 1).ToString(); 83 BuHelp.TCNO = TCNO; 84 BuHelp.TCShortName = TCShortName; 85 BuHelp.TMName = TMName; 86 BuHelp.TMPhone = TMPhone; 87 BuHelp.MrrEsg = "客户编号不存在"; 88 Uplist.Add(BuHelp); 89 count = false; 90 } 91 //if (TMDAL.IsExistTMPhone(TCID, TMPhone) >= 1) 92 //{ 93 // BuliImportHelp BuHelp = new BuliImportHelp(); 94 // BuHelp.Row = (r + 1).ToString(); 95 // BuHelp.TCNO = TCNO; 96 // BuHelp.TCShortName = TCShortName; 97 // BuHelp.TMName = TMName; 98 // BuHelp.TMPhone = TMPhone; 99 // BuHelp.MrrEsg = "手机号码已存在"; 100 // Uplist.Add(BuHelp); 101 // count = false; 102 //} 103 if (count) 104 { 105 TempMember TM = new TempMember(); 106 TM.TMSerialNo = "";//编号生成 107 TM.TMAddUserId = TMAddUserId;//操作人登陆ID 108 TM.TMName = TMName;//客户成员姓名 109 TM.TMPhone = TMPhone;//客户成员手机号 110 TM.TMTCComID = TCID;//客户的公司ID 111 TM.TMAddTime = DateTime.Now;//客户成员加入日期 112 list.Add(TM); 113 } 114 } 115 } 116 } 117 return list; 118 } 119 120 /// <summary> 121 /// 批量导入客户信息 122 /// </summary> 123 /// <param name="listModel"></param> 124 /// <returns></returns> 125 public int BulkCustomerInfo(List<TempMember> listModel) 126 { 127 return TMDAL.BulkCustomerInfo(listModel); 128 }
DAL数据访问层
1 /// <summary> 2 /// 根据客户编号获取客户公司ID 3 /// </summary> 4 /// <returns></returns> 5 public long GetTempCompanyID(string TCNO, long companyID) 6 { 7 string sql = @"SELECT TCID FROM TempCompany WHERE TCNO=@TCNO AND TCBelongComID = @companyID"; 8 try 9 { 10 using(IDbCon) 11 { 12 long TCID = IDbCon.ExecuteScalar<long>(sql, new { TCNO = TCNO, companyID = companyID }); 13 return TCID; 14 } 15 } 16 catch(Exception ex) 17 { 18 UtilityHelp.WriteLog(ex, ex.ToString()); 19 return 0; 20 } 21 } 22 23 /// <summary> 24 /// 是否存在该客户 25 /// </summary> 26 /// <param name="customerNum"></param> 27 /// <param name="customerAbbreviation"></param> 28 /// <param name="coopID"></param> 29 /// <returns></returns> 30 public int IsHaveCustomer(string TCNO, long companyID) 31 { 32 string sql = "SELECT COUNT(*) FROM TempCompany WHERE TCNO=@TCNO AND TCBelongType=1 AND TCBelongComID=@companyID"; 33 34 35 using (IDbCon) 36 { 37 try 38 { 39 return IDbCon.Query<int>(sql, new 40 { 41 TCNO = TCNO, 42 companyID = companyID 43 }).FirstOrDefault(); 44 } 45 catch (Exception ex) 46 { 47 UtilityHelp.WriteLog(ex, ex.ToString()); 48 return -1; 49 } 50 } 51 52 } 53 54 /// <summary> 55 /// 批量导入客户人员信息 56 /// </summary> 57 /// <param name="listModel"></param> 58 /// <returns></returns> 59 public int BulkCustomerInfo(List<TempMember> listModel) 60 { 61 string sql = @"INSERT INTO TempMember(TMSourceCome,TMAuditState,TMIsUsable,TMTCComID,TMSerialNo,TMName,TMSex,TMPhone,TMAddUserId,TMIsPhone,TMAddTime) 62 VALUES(2,1,1,@TMTCComID,@TMSerialNo,@TMName,1,@TMPhone,@TMAddUserId,'FALSE',@TMAddTime)"; 63 64 int result = 0; 65 IDbConnection iDbcon = base.IDbCon; 66 if (iDbcon.State == ConnectionState.Closed) 67 { 68 iDbcon.Open(); 69 } 70 71 using (var trans = iDbcon.BeginTransaction()) 72 { 73 try 74 { 75 result = iDbcon.Execute(sql, listModel, trans, null, CommandType.Text); 76 if (result == listModel.Count()) 77 { 78 trans.Commit(); 79 return result; 80 } 81 else 82 { 83 trans.Rollback(); 84 return 0; 85 } 86 } 87 catch (Exception ex) 88 { 89 trans.Rollback(); 90 UtilityHelp.WriteLog(ex, ex.ToString()); 91 return -1; 92 } 93 finally 94 { 95 iDbcon.Close(); 96 } 97 } 98 99 }
BLL层需要引用NOPI
using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel;
1、NuGet搜索Npoi并安装
2、添加引用将包引用进来
