快速学习POI-POI报表导入

4 POI报表导入

4.1 需求分析

实现批量导入员工功能，页面端上传excel表格，服务端解析表格获取数据，批量新增用户

4.2 员工导入

4.2.1 搭建环境

父模块pom文件添加依赖

 		<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>

4.2.2 实现Excel上传

（1）用户实体类配置构造方法

//objs数据位置和excel上传位置一致。
 public User(Object []objs,String companyId,String companyName) {
        //默认手机号excel读取为字符串会存在科学记数法问题，转化处理
        this.mobile = new DecimalFormat("#").format(objs[2]);
        this.username = objs[1].toString();
        this.createTime = new Date();
        this.timeOfEntry = (Date) objs[5];
        this.formOfEmployment = ((Double) objs[4]).intValue() ;
        this.workNumber = new DecimalFormat("#").format(objs[3]).toString();
        this.companyId = companyId;
        this.companyName = companyName;
   }

（2）在系统微服务 UserController 中添加上传方法

//批量导入数据
    @RequestMapping(value="/user/import", method = RequestMethod.POST)
    public Result importExcel(@RequestParam(name = "file") MultipartFile attachment) 
throws Exception {
        //根据上传流信息创建工作簿
        Workbook workbook = WorkbookFactory.create(attachment.getInputStream());
        //获取第一个sheet
        Sheet sheet = workbook.getSheetAt(0);
        List<User> users = new ArrayList<>();
        //从第二行开始获取数据
        for (int rowNum = 1; rowNum <sheet.getLastRowNum(); rowNum++) {
            Row row = sheet.getRow(rowNum);
            Object objs[] = new Object[row.getLastCellNum()];
            //从第二列获取数据
            for(int cellNum = 1; cellNum < row.getLastCellNum();cellNum++) {
                Cell cell = row.getCell(cellNum);
                objs[cellNum] = getValue(cell);
           }
            //根据每一列构造用户对象
            User user = new User(objs,companyId,companyName);
            user.setDepartmentId(objs[objs.length-1].toString());
            users.add(user);
       }
        //第一个参数：用户列表，第二个参数：部门编码
        userService.save(users,objs[objs.length-1].toString());
        return Result.SUCCESS();
   }

4.2.3 调用企业微服务获取部门数据

（1）在Ihrm_system模块创建com.ihrm.system.client包，包下创建接口

//远程调用企业微服务，根据企业编码code和企业名称获取企业信息
@FeignClient(value = "ihrm-company")
public interface DepartmentFeignClient {
    @RequestMapping(value = "/company/departments/search/", method =
RequestMethod.POST)
    public Department findById(@RequestParam(value = "code") String code,
                               @RequestParam(value = "companyId") String companyId) 
throws Exception; }

（2）修改 UserService ，注入 DepartmentFeignClient

@Autowired
private DepartmentFeignClient departmentFeignClient;

4.2.4 保存全部用户

UserService 中添加保存全部的方法

 @Transactional
    public void save(List<User> users) throws Exception {
        for (User user : users) {
            //配置密码
            user.setPassword(new Md5Hash("123456",user.getMobile(),3).toString());
            //配置id
            user.setId(idWorker.nextId()+"");
            //其他基本属性
            user.setInServiceStatus(1);
            user.setEnableState(1);
            user.setLevel("user");
            //获取部门信息
            Department dept =
departmentFeignClient.findById(user.getDepartmentId(),user.getCompanyId());
            if(dept != null) {
                user.setDepartmentId(dept.getId());
                user.setDepartmentName(dept.getName());
           }
            userDao.save(user);
             }
   }