第一种 easyExcel
pom文件导入
com.alibaba
easyexcel
2.2.3
然后
@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层
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() {
@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 list=new ArrayList();
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 stringList = trAccountAreaService.queryCodeByCityName(cityName);
if(!CollectionUtils.isEmpty(stringList)){
cityCode=stringList.get(0);
}
List stringList1 = trAccountAreaService.queryCodeByCityName(areaName);
if(!CollectionUtils.isEmpty(stringList1)){
areaCode=stringList.get(0);
}
ArrayList areas=new ArrayList();
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层
@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;
}
public static Boolean isOfficeFile(MultipartFile file) throws IOException {
BufferedInputStream bufferedInputStream = new BufferedInputStream(file.getInputStream());
boolean result = false;
result = isOfficeFile(bufferedInputStream);
return result;
}
public static Boolean checkExtension(String extension){
return Lists.newArrayList("xls","xlsx","XLS","XLSX").contains(extension);
}
public static Boolean checkExtension(MultipartFile file){
String fileName = file.getOriginalFilename();
String extension = fileName.substring(fileName.lastIndexOf(".")+1);
return checkExtension(extension);
}
public static Workbook getWorkbookAuto(MultipartFile file) throws IOException {
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 stringList = trAccountAreaService.queryCodeByCityName(cityName);
if(!CollectionUtils.isEmpty(stringList)){
cityCode=stringList.get(0);
}else{
errNum++;
continue;
}
List 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 list=new ArrayList();
list.add(Long.parseLong(role));
Long[] newRoles = new Long[list.size()];
Long[] roles = list.toArray(newRoles);
if (roles != null) {
userAccService.setRole(userId, roles);
}
ArrayList areas=new ArrayList();
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();
}
}
欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）