第一种 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>() {
@Override
public 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();//渠道Id
TbPlatformChannelVo 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{
}
}
@Override
public 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或者2007
Workbook 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有多少行
//读第一个sheet
for (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){
//数据库导入user
UserEntity 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();
}
}