<!--POI-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.0.1</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.0.1</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>4.0.1</version>
</dependency>
package com.learn.system.controller;
import com.learn.common.controller.BaseController;
import com.learn.common.entity.PageResult;
import com.learn.common.entity.Result;
import com.learn.common.entity.ResultCode;
import com.learn.common.exception.CommonException;
import com.learn.common.utils.JwtUtils;
import com.learn.common.utils.PermissionConstants;
import com.learn.domain.system.Permission;
import com.learn.domain.system.Role;
import com.learn.domain.system.response.ProfileResult;
import com.learn.domain.system.User;
import com.learn.domain.system.response.UserResult;
import com.learn.system.client.DepartmentFeignClient;
import com.learn.system.service.PermissionService;
import com.learn.system.service.RoleService;
import com.learn.system.service.UserService;
import io.jsonwebtoken.Claims;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.shiro.SecurityUtils;
import org.apache.shiro.authc.UsernamePasswordToken;
import org.apache.shiro.authz.annotation.RequiresPermissions;
import org.apache.shiro.crypto.hash.Md5Hash;
import org.apache.shiro.subject.PrincipalCollection;
import org.apache.shiro.subject.Subject;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.util.StringUtils;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletRequest;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
//1.解决跨域
@CrossOrigin
//2.声明restContoller
@RestController
//3.设置父路径
@RequestMapping(value="/sys")
public class UserController extends BaseController {
@Autowired
private UserService userService;
/**
* 导入Excel,添加用户
* 文件上传:springboot
*/
@RequestMapping(value="/user/import",method = RequestMethod.POST)
public Result importUser(@RequestParam(name="file") MultipartFile file) throws Exception {
//1.解析Excel
//1.1.根据Excel文件创建工作簿
Workbook wb = new XSSFWorkbook(file.getInputStream());
//1.2.获取Sheet
Sheet sheet = wb.getSheetAt(0);//参数:索引
//1.3.获取Sheet中的每一行,和每一个单元格
//2.获取用户数据列表
List<User> list = new ArrayList<>();
System.out.println(sheet.getLastRowNum());
for (int rowNum = 1; rowNum<= sheet.getLastRowNum() ;rowNum ++) {
Row row = sheet.getRow(rowNum);//根据索引获取每一个行
Object [] values = new Object[row.getLastCellNum()];
for(int cellNum=1;cellNum< row.getLastCellNum(); cellNum ++) {
Cell cell = row.getCell(cellNum);
Object value = getCellValue(cell);
values[cellNum] = value;
}
User user = new User(values);
list.add(user);
}
//3.批量保存用户
userService.saveAll(list,companyId,companyName);
return new Result(ResultCode.SUCCESS);
}
public static Object getCellValue(Cell cell) {
//1.获取到单元格的属性类型
CellType cellType = cell.getCellType();
//2.根据单元格数据类型获取数据
Object value = null;
switch (cellType) {
case STRING:
value = cell.getStringCellValue();
break;
case BOOLEAN:
value = cell.getBooleanCellValue();
break;
case NUMERIC:
if(DateUtil.isCellDateFormatted(cell)) {
//日期格式
value = cell.getDateCellValue();
}else{
//数字
value = cell.getNumericCellValue();
}
break;
case FORMULA: //公式
value = cell.getCellFormula();
break;
default:
break;
}
return value;
}
}
package com.learn.domain.system;
import com.fasterxml.jackson.annotation.JsonIgnore;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;
import javax.persistence.*;
import java.io.Serializable;
import java.text.DecimalFormat;
import java.util.Date;
import java.util.HashSet;
import java.util.Set;
/**
* 用户实体类
*/
@Entity
@Table(name = "bs_user")
@Getter
@Setter
@NoArgsConstructor
public class User implements Serializable {
private static final long serialVersionUID = 4297464181093070302L;
/**
* ID
*/
@Id
private String id;
/**
* 手机号码
*/
private String mobile;
/**
* 用户名称
*/
private String username;
/**
* 密码
*/
private String password;
/**
* 启用状态 0为禁用 1为启用
*/
private Integer enableState;
/**
* 创建时间
*/
private Date createTime;
private String companyId;
private String companyName;
/**
* 部门ID
*/
private String departmentId;
/**
* 入职时间
*/
private Date timeOfEntry;
/**
* 聘用形式
*/
private Integer formOfEmployment;
/**
* 工号
*/
private String workNumber;
/**
* 管理形式
*/
private String formOfManagement;
/**
* 工作城市
*/
private String workingCity;
/**
* 转正时间
*/
private Date correctionTime;
/**
* 在职状态 1.在职 2.离职
*/
private Integer inServiceStatus;
private String departmentName;
/**
* level
* String
* saasAdmin:saas管理员具备所有权限
* coAdmin:企业管理(创建租户企业的时候添加)
* user:普通用户(需要分配角色)
*/
private String level;
public User(Object [] values) {
//用户名 手机号 工号 聘用 形式 入职 时间 部门编码
this.username = values[1].toString();
this.mobile = values[2].toString();
this.workNumber = new DecimalFormat("#").format(values[3]).toString();
this.formOfEmployment =((Double) values[4]).intValue();
this.timeOfEntry = (Date) values[5];
this.departmentId = values[6].toString(); //部门编码 != 部门id
}
/**
* JsonIgnore
* : 忽略json转化
*/
@JsonIgnore
@ManyToMany
@JoinTable(name="pe_user_role",joinColumns={@JoinColumn(name="user_id",referencedColumnName="id")},
inverseJoinColumns={@JoinColumn(name="role_id",referencedColumnName="id")}
)
private Set<Role> roles = new HashSet<Role>();//用户与角色 多对多
}
package com.learn.system.service;
import com.learn.common.utils.IdWorker;
import com.learn.domain.company.Department;
import com.learn.domain.system.Role;
import com.learn.domain.system.User;
import com.learn.system.client.DepartmentFeignClient;
import com.learn.system.dao.RoleDao;
import com.learn.system.dao.UserDao;
import org.apache.shiro.crypto.hash.Md5Hash;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.StringUtils;
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;
import java.lang.annotation.Target;
import java.util.*;
@Service
public class UserService {
@Autowired
private UserDao userDao;
@Autowired
private RoleDao roleDao;
@Autowired
private IdWorker idWorker;
@Autowired
private DepartmentFeignClient departmentFeignClient;
/**
* 批量保存用户
*/
@Transactional
public void saveAll(List<User> list ,String companyId,String companyName){
for (User user : list) {
//默认密码
user.setPassword(new Md5Hash("123456",user.getMobile(),3).toString());
//id
user.setId(idWorker.nextId()+"");
//基本属性
user.setCompanyId(companyId);
user.setCompanyName(companyName);
user.setInServiceStatus(1);
user.setEnableState(1);
user.setLevel("user");
//填充部门的属性
Department department = departmentFeignClient.findByCode(user.getDepartmentId(), companyId);
if(department != null) {
user.setDepartmentId(department.getId());
user.setDepartmentName(department.getName());
}
userDao.save(user);
}
}
}