Java使用EasyExcel导入导出数据表

添加依赖

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>3.2.1</version>
        </dependency>

导入，操作数据库的逻辑根据自己的业务修改

public class PersonListener extends AnalysisEventListener {

    List<PersonDO> personList = new ArrayList<>();

    @Override
    public void invoke(Object o, AnalysisContext analysisContext) {
        PersonDO personDO = new PersonDO();
        String devNo = ((PersonDO) o).getDevNo();
        personDO.setDevNo(devNo);
        String empName = ((PersonDO) o).getEmpName();
        personDO.setEmpName(empName);
        String empNo = ((PersonDO) o).getEmpNo();
        personDO.setEmpNo(empNo);
        String depName = ((PersonDO) o).getDepName();
        personDO.setDepName(depName);

        personList.add(personDO);
    }

    @Override
    public void onException(Exception exception, AnalysisContext context) throws Exception {
        super.onException(exception, context);
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        personList.clear();
    }
}

@RestController
@RequestMapping("/import/")
public class PersonImport {
    private final DeviceMapper deviceMapper;
    private final EmployeeMapper employeeMapper;

    private final DepMapper depMapper;

    public PersonImport(DepMapper depMapper, EmployeeMapper employeeMapper, DeviceMapper deviceMapper) {
        this.depMapper = depMapper;
        this.employeeMapper = employeeMapper;
        this.deviceMapper = deviceMapper;
    }

    @PostMapping("person")
    public CommonResult importExcel(MultipartFile file) {
        try {
            //获取文件的输入流
            InputStream inputStream = file.getInputStream();
            List<PersonDO> lst = EasyExcel.read(inputStream) //调用read方法
                                          //注册自定义监听器，字段校验可以在监听器内实现
                                          .registerReadListener(new PersonListener())
                                          .head(PersonDO.class) //对应导入的实体类
                                          .sheet(0) //导入数据的sheet页编号，0代表第一个sheet页，如果不填，则会导入所有sheet页的数据
                                          .headRowNumber(1) //列表头行数，1代表列表头有1行，第二行开始为数据行
                                          .doReadSync(); //开始读Excel，返回一个List<T>集合，继续后续入库操作

            //导入数据库操作
            for (PersonDO personDO : lst) {
                Optional<PersonDO> optionalPersonDO = Optional.ofNullable(personDO);
                EmployeeDTO employeeDTO = new EmployeeDTO();
                DeviceDTO deviceDTO = new DeviceDTO();
                //  判断是否存在该单位
                optionalPersonDO.map(PersonDO::getDepName).ifPresent(name -> {
                    Department depByName = depMapper.getDepByName(name);
                    if (ObjectUtils.isEmpty(depByName)) {
                        throw new CommonException(YIXGResultEnum.DEP_NOT_EXIST.getCode(),
                                YIXGResultEnum.DEP_NOT_EXIST.getMessage());
                    }
                    employeeDTO.setEmpDepID(depByName.getDepID());
                    deviceDTO.setDevDepID(depByName.getDepID());
                });
                //  判断人员编号是否重复
                optionalPersonDO.map(PersonDO::getEmpNo).ifPresent(no -> {
                    Employee empByNo = employeeMapper.getEmpByNo(null, no);
                    if (!ObjectUtils.isEmpty(empByNo)) {
                        throw new CommonException(YIXGResultEnum.EMPNO_CANNOT_REPEAT.getCode(),
                                YIXGResultEnum.EMPNO_CANNOT_REPEAT.getMessage());
                    }
                    employeeDTO.setEmpNo(no);
                    optionalPersonDO.map(PersonDO::getEmpName).ifPresent(name -> {
                        employeeDTO.setEmpName(name);
                        employeeDTO.setEmpCreateTime(new Date());
                        employeeDTO.setEmpEnable(true);
                        employeeMapper.addEmp(employeeDTO);
                        deviceDTO.setDevEmpID(employeeDTO.getEmpID());
                    });
                });
                //  判断设备编号是否重复
                optionalPersonDO.map(PersonDO::getDevNo).ifPresent(no -> {
                    Device devByNO = deviceMapper.getDevByNO(null, no);
                    if (!ObjectUtils.isEmpty(devByNO)) {
                        throw new CommonException(YIXGResultEnum.DEVICENO_CANNOT_REPEAT.getCode(),
                                YIXGResultEnum.DEVICENO_CANNOT_REPEAT.getMessage());
                    }
                    deviceDTO.setDevNo(no);
                    deviceDTO.setDevCreateTime(new Date());
                    deviceDTO.setDevEnable(true);
                    deviceMapper.addDev(deviceDTO);
                });
            }
            return new CommonResult().setCode(YIXGResultEnum.OPERATE_SUCCESS.getCode())
                                     .setMessage(YIXGResultEnum.OPERATE_SUCCESS.getMessage());
        } catch (IOException exception) {
            throw new RuntimeException(exception);
        }
    }
}

导出

@RestController
@RequestMapping("/export/")
public class PersonExport {
    private final EmployeeMapper employeeMapper;
    private final DeviceMapper deviceMapper;

    public PersonExport(DeviceMapper deviceMapper, EmployeeMapper employeeMapper) {
        this.deviceMapper = deviceMapper;
        this.employeeMapper = employeeMapper;
    }

    private void setExcelResponseProp(
            HttpServletResponse response, String rawFileName
    ) throws UnsupportedEncodingException {
        //  设置内容类型
        response.setContentType("application/vnd.vnd.ms-excel");
        //  设置编码格式
        response.setCharacterEncoding("UTF-8");
        //  设置导出文件名称
        String fileName = URLEncoder.encode(rawFileName.concat(".xlsx"), StandardCharsets.UTF_8);
        //  设置响应头
        response.setHeader("Content-disposition", "attachment;filename*=utf-8' '" + fileName + ".xlsx");
    }

    @GetMapping("person")
    public void exportPersonExcel(HttpServletResponse response) throws IOException {
        OutputStream outputStream = response.getOutputStream();
        try {
            this.setExcelResponseProp(response, "人员列表");
            // 模拟根据条件在数据库查询数据
            List<PersonDO> personList = new ArrayList<>();
            List<Employee> empList = employeeMapper.empListToAll(new EmployeeDTO());
            for (Employee employee : empList) {
                PersonDO personDO = new PersonDO();
                Optional<Employee> employeeOptional = Optional.ofNullable(employee);
                employeeOptional.map(Employee::getDevice).map(Device::getDevNo).ifPresent(personDO::setDevNo);
                employeeOptional.map(Employee::getEmpName).ifPresent(personDO::setEmpName);
                employeeOptional.map(Employee::getEmpNo).ifPresent(personDO::setEmpNo);
                employeeOptional.map(Employee::getDepartment).map(Department::getDepName).ifPresent(
                        personDO::setDepName);
                personList.add(personDO);
            }
            //这个实现方式非常简单直接，使用EasyExcel的write方法将查询到的数据进行处理，以流的形式写出即可
            EasyExcel.write(outputStream, PersonDO.class)//对应的导出实体类
                     .excelType(ExcelTypeEnum.XLSX)//excel文件类型，包括CSV、XLS、XLSX
                     .sheet("人员列表")//导出sheet页名称
                     .doWrite(personList); //查询获取的数据集合List<T>，转成excel
        } catch (IOException e) {
            throw new RuntimeException(e);
        } finally {
            outputStream.flush();
            outputStream.close();
        }
    }

    @GetMapping("device")
    public void exportDeviceExcel(HttpServletResponse response) throws IOException {
        OutputStream outputStream = response.getOutputStream();
        try {
            this.setExcelResponseProp(response, "设备列表");
            // 模拟根据条件在数据库查询数据
            List<PersonDO> devList = new ArrayList<>();
            List<Device> deviceList = deviceMapper.devListToAll(new DeviceDTO());
            for (Device device : deviceList) {
                PersonDO personDO = new PersonDO();
                Optional<Device> optionalDevice = Optional.ofNullable(device);
                optionalDevice.map(Device::getDevNo).ifPresent(personDO::setDevNo);
                optionalDevice.map(Device::getEmployee).map(Employee::getEmpName)
                              .ifPresent(personDO::setEmpName);
                optionalDevice.map(Device::getEmployee).map(Employee::getEmpNo).ifPresent(personDO::setEmpNo);
                optionalDevice.map(Device::getDepartment).map(Department::getDepName).ifPresent(
                        personDO::setDepName);
                devList.add(personDO);
            }
            //这个实现方式非常简单直接，使用EasyExcel的write方法将查询到的数据进行处理，以流的形式写出即可
            EasyExcel.write(outputStream, PersonDO.class)//对应的导出实体类
                     .excelType(ExcelTypeEnum.XLSX)//excel文件类型，包括CSV、XLS、XLSX
                     .sheet("设备列表")//导出sheet页名称
                     .doWrite(devList); //查询获取的数据集合List<T>，转成excel
        } catch (IOException e) {
            throw new RuntimeException(e);
        } finally {
            outputStream.flush();
            outputStream.close();
        }
    }
}