两种方式导入excel

第一种 easyExcel

pom文件导入

com.alibaba
easyexcel
2.2.3

然后
/**
* 导入用户excel
* @param
* @return
*/
@PostMapping(“manage/imporAcc”)
Message<?> imporAcc(@RequestParam(“accountFile”) MultipartFile file,@RequestParam(“role”)String role,@RequestParam(“password”)String password) throws IOException {

    log.info("导入用户数据文件,角色为:{},密码为:{}",role,password);if(file.isEmpty()){return R.error(Msg.ERROR_CHECK_EMPTY,"导入数据文件");}return userAccService.imporAcc(file,role,password);
}

在这里插入图片描述

controller层

然后
service层

/**
* 导入账户数据文件
* @param file
* @return
*/
public Message<?> imporAcc(MultipartFile file,String role,String password) {

    InputStream inputStream = null;Integer errNum=0;try {inputStream = file.getInputStream();} catch (IOException e) {e.printStackTrace();}EasyExcel.read(inputStream, UserAccountImportEntity.class, new AnalysisEventListener<UserAccountImportEntity>() {@Overridepublic void invoke(UserAccountImportEntity data, AnalysisContext context) {System.out.println("--------------------------");System.out.println(data);String dataContextt = data.getDataRange();if("全部数据".equals(dataContextt)){dataContextt="all";}else if("泛渠道".equals(dataContextt)){dataContextt="tx_channel";}else{}AccountEntity account = userAccService.findAccountByNum(data.getAccountNum());//校验归属渠道是否和渠道ID匹配String hallNo = data.getHallNo();//渠道名称String channleId = data.getChannleId();//渠道IdTbPlatformChannelVo tbPlatformChannelVo = channelFeign.findByHallNo(channleId).getBody();String branchName = tbPlatformChannelVo.getBranchName();if(hallNo.equals(branchName)){if (account == null) {account = new AccountEntity();BeanUtils.copyProperties(data, account);account.setState(StatusCons.E);account.setPassword(password);//如果不绑定用户 则新建用户if (account.getUserId() == null) {UserEntity user = new UserEntity();user.setState(StatusCons.E);user.setUserName(data.getUserName());user.setDataContext(dataContextt);userAccService.addUser(user);account.setUserId(user.getUserId());}//如果角色不为空 设置角色ArrayList<Long> list=new ArrayList<Long>();list.add(Long.parseLong(role));Long[] newRoles = new Long[list.size()];Long[] roles = list.toArray(newRoles);if (roles != null) {userAccService.setRole(account.getUserId(), roles);}account.setPassword(passwordEncoder.encode(account.getPassword()));account.setHallNo(channleId);Message<?> message = userAccService.addAccount(account);//根据地市的名称和区县的名称查询对应的编码String cityName = data.getCityName();String areaName = data.getAreaName();String cityCode="";String areaCode="";List<String> stringList = trAccountAreaService.queryCodeByCityName(cityName);if(!CollectionUtils.isEmpty(stringList)){cityCode=stringList.get(0);}List<String> stringList1 = trAccountAreaService.queryCodeByCityName(areaName);if(!CollectionUtils.isEmpty(stringList1)){areaCode=stringList.get(0);}ArrayList<Long> areas=new ArrayList<Long>();areas.add(Long.parseLong(cityCode));areas.add(Long.parseLong(areaCode));Long[] areaList = new Long[areas.size()];for(int i = 0; i < areas.size();i++){areaList[i] = areas.get(i);}// Long[] areanews = list.toArray(areaList);//账户区域关联表新增if(areaList.length>0){userAccController.saveAreaByAccount(Long.parseLong(String.valueOf(message.getBody())),areaList);}}else{}}else{}}@Overridepublic void doAfterAllAnalysed(AnalysisContext context) {System.out.println("导入账户数据文件完成");}}).sheet().doRead();return R.success(errNum);
}

在这里插入图片描述

需要注意的是实体类

public class UserAccountImportEntity {

@ApiModelProperty("账号")
@ExcelProperty("账号")
@ColumnWidth(15)
private String accountNum;
@ExcelProperty("手机号码")
@ColumnWidth(15)
private String phone;
@ApiModelProperty("用户名称")
@ExcelProperty("用户名称")
@ColumnWidth(15)
private String userName;
@ApiModelProperty("Boss工号")
@ExcelProperty("Boss工号")
@ColumnWidth(15)
private String bossJobNum;
@ApiModelProperty("归属地市")
@ExcelProperty("归属地市")
@ColumnWidth(15)
private String cityName;
@ApiModelProperty("归属区县")
@ExcelProperty("归属区县")
@ColumnWidth(15)
private String  areaName;
@ExcelProperty("归属网格")
@ApiModelProperty("归属网格")
@ColumnWidth(15)
private String excludeNet;
@ExcelProperty("商家/连锁店")
@ApiModelProperty("商家/连锁店")
@ColumnWidth(25)
private String shopKeeper;
@ApiModelProperty("归属渠道")
@ExcelProperty("归属渠道")
@ColumnWidth(15)
private String hallNo;
@ApiModelProperty("渠道ID")
@ExcelProperty("渠道ID")
private String channleId;
@ExcelProperty("数据范围")
@ApiModelProperty("数据范围")
@ColumnWidth(15)
private String dataRange;
@ApiModelProperty("密码")
@ExcelIgnore
private String password;
@ApiModelProperty("角色")
@ExcelIgnore
private String roleId;public UserAccountImportEntity(){}public UserAccountImportEntity(String accountNum, String userName, String bossJobNum, String cityName, String areaName, String excludeNet, String shopKeeper, String hallNo, String channleId, String dataRange, String password, String roleId,String phone) {this.accountNum = accountNum;this.phone = phone;this.userName = userName;this.bossJobNum = bossJobNum;this.cityName = cityName;this.areaName = areaName;this.excludeNet = excludeNet;this.shopKeeper = shopKeeper;this.hallNo = hallNo;this.channleId = channleId;this.dataRange = dataRange;this.password = password;this.roleId = roleId;
}public String getAccountNum() {return accountNum;
}public void setAccountNum(String accountNum) {this.accountNum = accountNum;
}public String getPhone() {return phone;
}public void setPhone(String phone) {this.phone = phone;
}public String getUserName() {return userName;
}public void setUserName(String userName) {this.userName = userName;
}public String getBossJobNum() {return bossJobNum;
}public void setBossJobNum(String bossJobNum) {this.bossJobNum = bossJobNum;
}public String getCityName() {return cityName;
}public void setCityName(String cityName) {this.cityName = cityName;
}public String getAreaName() {return areaName;
}public void setAreaName(String areaName) {this.areaName = areaName;
}public String getExcludeNet() {return excludeNet;
}public void setExcludeNet(String excludeNet) {this.excludeNet = excludeNet;
}public String getShopKeeper() {return shopKeeper;
}public void setShopKeeper(String shopKeeper) {this.shopKeeper = shopKeeper;
}public String getHallNo() {return hallNo;
}public void setHallNo(String hallNo) {this.hallNo = hallNo;
}public String getChannleId() {return channleId;
}public void setChannleId(String channleId) {this.channleId = channleId;
}public String getDataRange() {return dataRange;
}public void setDataRange(String dataRange) {this.dataRange = dataRange;
}public String getPassword() {return password;
}public void setPassword(String password) {this.password = password;
}public String getRoleId() {return roleId;
}public void setRoleId(String roleId) {this.roleId = roleId;
}@Override
public String toString() {return "UserAccountImportEntity{" +"accountNum='" + accountNum + '\'' +", phone='" + phone + '\'' +", userName='" + userName + '\'' +", bossJobNum='" + bossJobNum + '\'' +", cityName='" + cityName + '\'' +", areaName='" + areaName + '\'' +", excludeNet='" + excludeNet + '\'' +", shopKeeper='" + shopKeeper + '\'' +", hallNo='" + hallNo + '\'' +", channleId='" + channleId + '\'' +", dataRange='" + dataRange + '\'' +", password='" + password + '\'' +", roleId='" + roleId + '\'' +'}';
}

}

下面是用POI原始方法做excel

controller层

/**
* 备用接口用POI导出用户excel
* @param
* @return
*/
@PostMapping(“manage/imporPoiAcc”)
Message<?> imporPoiAcc(@RequestParam(“accountFile”) MultipartFile file,@RequestParam(“role”)String role,@RequestParam(“password”)String password) throws IOException {

    log.info("导入用户数据文件,角色为:{},密码为:{}",role,password);if (!FileUtil.checkExtension(file)){return R.error(Msg.ERROR_CHECK_EMPTY,"导入数据文件");}if(!FileUtil.isOfficeFile(file)){return R.error(Msg.ERROR_CHECK_EMPTY,"导入数据文件");}if(StringUtils.isBlank(role)){return R.error(Msg.ERROR_CHECK_EMPTY,"输入角色");}if(StringUtils.isBlank(password)){return R.error(Msg.ERROR_CHECK_EMPTY,"输入密码");}return userAccService.imporPoiAcc(file,role,password);
}

下面是工具类
FileUtil

public class FileUtil {

public static Boolean isOfficeFile(InputStream inputStream){boolean result = false;try {FileMagic fileMagic = FileMagic.valueOf(inputStream);if (Objects.equals(fileMagic,FileMagic.OLE2)||Objects.equals(fileMagic,fileMagic.OOXML)){result = true;}} catch (IOException e) {e.printStackTrace();}return result;
}/*** 判断是否office文件* @param file* @return* @throws IOException*/
public static Boolean isOfficeFile(MultipartFile file) throws IOException {BufferedInputStream bufferedInputStream = new BufferedInputStream(file.getInputStream());boolean result = false;result = isOfficeFile(bufferedInputStream);return result;
}/*** 判断扩展名是否是excel扩展名* @param extension* @return*/
public static Boolean checkExtension(String extension){return Lists.newArrayList("xls","xlsx","XLS","XLSX").contains(extension);
}/*** 判断扩展名是否是excel扩展名* @param file* @return*/
public static Boolean checkExtension(MultipartFile file){String fileName = file.getOriginalFilename();String extension = fileName.substring(fileName.lastIndexOf(".")+1);return checkExtension(extension);
}/*** 自动判断文件类型* @param file* @return* @throws IOException*/
public static Workbook getWorkbookAuto(MultipartFile file) throws IOException {/** 判断文件的类型，是2003还是2007 */boolean isExcel2003 = true;if (isExcel2007(file.getOriginalFilename())) {isExcel2003 = false;}BufferedInputStream is = new BufferedInputStream(file.getInputStream());Workbook wb;if (isExcel2003) {wb = new HSSFWorkbook(is);} else {wb = new XSSFWorkbook(is);}return wb;
}public static boolean isExcel2003(String filePath) {return filePath.matches("^.+\\.(?i)(xls)$");
}public static boolean isExcel2007(String filePath) {return filePath.matches("^.+\\.(?i)(xlsx)$");
}

}

然后是service层
public Message<?> imporPoiAcc(MultipartFile file, String role, String password) {

    //未进入数据库行数Integer errNum=0;try {//正确的文件类型 自动判断2003或者2007Workbook workbook = FileUtil.getWorkbookAuto(file);Sheet sheet = workbook.getSheetAt(0);//默认只有一个sheet//判断这个sheet的列数Row row0 = sheet.getRow(0);short lastCellNum = row0.getLastCellNum();if(lastCellNum<11){return R.error("sheet列数不正确");}int rows = sheet.getPhysicalNumberOfRows();//获得sheet有多少行//读第一个sheetfor (int i = 1;i<rows;i++){Row row = sheet.getRow(i);Cell cell0 = row.getCell(0);String accountNum="";if(cell0!=null){cell0.setCellType(CellType.STRING);accountNum = cell0.getStringCellValue();}Cell cell1 = row.getCell(1);String phone="";if(cell1!=null){cell1.setCellType(CellType.STRING);phone = cell1.getStringCellValue();}Cell cell2 = row.getCell(2);String userName="";if(cell2!=null){cell2.setCellType(CellType.STRING);userName = cell2.getStringCellValue();}Cell cell3 = row.getCell(3);String bossJobNum="";if(cell3!=null){cell3.setCellType(CellType.STRING);bossJobNum = cell3.getStringCellValue();}Cell cell4 = row.getCell(4);String cityName="";if(cell4!=null){cell4.setCellType(CellType.STRING);cityName = cell4.getStringCellValue();}Cell cell5 = row.getCell(5);String areaName ="";if(cell5!=null){cell5.setCellType(CellType.STRING);areaName = cell5.getStringCellValue();}Cell cell6 = row.getCell(6);String excludeNet="";if(cell6!=null){cell6.setCellType(CellType.STRING);excludeNet = cell6.getStringCellValue();}Cell cell7 = row.getCell(7);String shopKeeper="";if(cell7!=null){cell7.setCellType(CellType.STRING);shopKeeper = cell7.getStringCellValue();}Cell cell8 = row.getCell(8);String hallNo="";if(cell8!=null){cell8.setCellType(CellType.STRING);hallNo = cell8.getStringCellValue();}Cell cell9 = row.getCell(9);String channleId="";if(cell9!=null){cell9.setCellType(CellType.STRING);channleId = cell9.getStringCellValue();//根据渠道ID查询渠道名称TbPlatformChannelVo tbPlatformChannelVo = channelFeign.findByHallNo(channleId).getBody();if(tbPlatformChannelVo==null){errNum++;continue;}else{if(!tbPlatformChannelVo.getBranchName().equals(hallNo)){errNum++;continue;}}}Cell cell10 = row.getCell(10);String dataRange="";if(cell10!=null){cell10.setCellType(CellType.STRING);dataRange = cell10.getStringCellValue();if("全部数据".equals(dataRange)){dataRange="all";}else if("泛渠道".equals(dataRange)){dataRange="tx_channel";}else{errNum++;continue;}}//根据地市的名称和区县的名称查询对应的编码String cityCode="";String areaCode="";List<String> stringList = trAccountAreaService.queryCodeByCityName(cityName);if(!CollectionUtils.isEmpty(stringList)){cityCode=stringList.get(0);}else{errNum++;continue;}List<String> stringList1 = trAccountAreaService.queryCodeByCityName(areaName);if(!CollectionUtils.isEmpty(stringList1)){areaCode=stringList1.get(0);}else{errNum++;continue;}//判断是否有此账号之前AccountEntity accountEntity = userAccService.findAccountByNum(accountNum);Long userId=0L;Message<?> accMessage=null;if(accountEntity==null){//数据库导入userUserEntity user = new UserEntity();user.setState(StatusCons.E);user.setUserName(userName);user.setDataContext(dataRange);userAccService.addUser(user);//数据库新增账号userId=user.getUserId();accountEntity=new AccountEntity();accountEntity.setState(StatusCons.E);accountEntity.setAccountNum(accountNum);accountEntity.setUserId(userId);accountEntity.setPassword(passwordEncoder.encode(password));accountEntity.setHallNo(channleId);accountEntity.setBossJobNum(bossJobNum);accountEntity.setPhone(phone);accMessage = userAccService.addAccount(accountEntity);}else{errNum++;continue;}//如果角色不为空 设置角色ArrayList<Long> list=new ArrayList<Long>();list.add(Long.parseLong(role));Long[] newRoles = new Long[list.size()];Long[] roles = list.toArray(newRoles);if (roles != null) {userAccService.setRole(userId, roles);}ArrayList<Long> areas=new ArrayList<Long>();areas.add(Long.parseLong(cityCode));areas.add(Long.parseLong(areaCode));Long[] areaList = new Long[areas.size()];for(int k = 0; k < areas.size();k++){areaList[k] = areas.get(k);}//账户区域关联表新增if(areaList.length>0){userAccController.saveAreaByAccount(Long.parseLong(String.valueOf(accMessage.getBody())),areaList);}}} catch (IOException e) {e.printStackTrace();}if(errNum>0){return R.error(Msg.ERROR_BUSS_SMS_CM_IMPORT,errNum+"行");}else{return R.success();}
}

在这里插入图片描述