EasyExcel实现导入导出

一、准备

1.pom.xml引入easyexcel依赖

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

2.pom.xml引入poi依赖，poi版本对应引入的easyexcel中poi依赖的版本

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.17</version>
        </dependency>

二、导入

1.准备一个导入实体类

@ContentRowHeight(15)
@HeadRowHeight(22)
@ColumnWidth(15)
@ApiModel
public class RuleIndexImportDTO {
    @ApiModelProperty(value = "指标名称")
    @ExcelProperty(value = "指标名称*", index = 0)
    private String indexName;

    @ApiModelProperty(value = "指标编码")
    @ExcelProperty(value = "指标编码*", index = 1)
    private String indexCode;

    @ApiModelProperty(value = "指标类型")
    @ExcelProperty(value = "指标类型*", index = 2)
    private String dataType;

    @ApiModelProperty(value = "指标形式")
    @ExcelProperty(value = "指标形式*", index = 3)
    private String indexAct;

    @ColumnWidth(50)
    @ApiModelProperty(value = "指标描述")
    @ExcelProperty(value = "指标描述*", index = 4)
    private String indexDesc;

    public String getIndexName() {
        return indexName;
    }

    public void setIndexName(String indexName) {
        this.indexName = indexName;
    }

    public String getIndexCode() {
        return indexCode;
    }

    public void setIndexCode(String indexCode) {
        this.indexCode = indexCode;
    }

    public String getDataType() {
        return dataType;
    }

    public void setDataType(String dataType) {
        this.dataType = dataType;
    }

    public String getIndexAct() {
        return indexAct;
    }

    public void setIndexAct(String indexAct) {
        this.indexAct = indexAct;
    }

    public String getIndexDesc() {
        return indexDesc;
    }

    public void setIndexDesc(String indexDesc) {
        this.indexDesc = indexDesc;
    }
}

2.编写导入模板接口相关代码

(1).controller层代码

    @GetMapping("/template")
    @ApiOperation("下载指标导入模板")
    public void downloadIndexTemplate(HttpServletResponse response) {
        ruleIndexService.downloadIndexTemplate(response);
    }

(2).service层代码

    /**
     * 下载指标导入模板
     *
     * @param response
     */
    void downloadIndexTemplate(HttpServletResponse response);

    /**
     * 下载指标导入模板
     *
     * @param response
     */
    @Override
    public void downloadIndexTemplate(HttpServletResponse response) {
        try {
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            String fileName = URLEncoder.encode("指标导入模板", "UTF-8");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
            //查询字典数据
            EasyExcel.write(response.getOutputStream())
                    .head(RuleIndexImportDTO.class)
                    .registerWriteHandler(new SelectSheetWriteHandler())
                    .sheet("指标导入模板")
                    .doWrite(new ArrayList<>());
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

3.编写导入接口相关代码

(1).controller层代码

    @PostMapping("/{indexSetId}/import")
    @ApiOperation("导入指标")
    public int importIndex(@PathVariable("indexSetId") @ApiParam("指标集id") Integer indexSetId,
                           HttpServletRequest request) {
        if (null == indexSetId) {
            throw new RuleException("指标集id不能为空");
        }
        StandardServletMultipartResolver resolver = new StandardServletMultipartResolver();
        MultipartHttpServletRequest multipart = resolver.resolveMultipart(request);
        Map<String, MultipartFile> fileMap = multipart.getFileMap();
        if (MapUtils.isEmpty(fileMap)) {
            throw new RuleException("未找到要上传的文件!");
        }
        return ruleIndexService.importIndex(indexSetId, fileMap);
    }

(2).service层代码

    /**
     * 导入指标
     *
     * @param indexSetId
     * @param fileMap
     * @return
     */
    @Transactional(rollbackFor = Exception.class)
    int importIndex(Integer indexSetId, Map<String, MultipartFile> fileMap);
    /**
     * 导入指标
     *
     * @param indexSetId
     * @param fileMap
     * @return
     */
    @Override
    public int importIndex(Integer indexSetId, Map<String, MultipartFile> fileMap) {
        int result = 1;
        if (fileMap.size() > 1) {
            throw new RuleException("只支持导入单个文件！");
        }
        MultipartFile file = null;
        for (Map.Entry<String, MultipartFile> temp : fileMap.entrySet()) {
            file = temp.getValue();
        }
        String fileName = file.getOriginalFilename();
        if (StringUtils.isNotBlank(fileName) && !fileName.endsWith(".xlsx")) {
            throw new RuleException("文件格式不正确！");
        }
        // 检查模板是否正确
        try {
            EasyExcel.read(file.getInputStream(), RuleIndexImportDTO.class, new EasyExcelListener()).sheet().doRead();
        } catch (Exception e) {
            throw new RuleException("模板错误，请检查导入模板！");
        }
        List<RuleIndexImportDTO> list = null;
        try {
            list = EasyExcel.read(file.getInputStream())
                    .head(RuleIndexImportDTO.class)
                    .sheet(0)
                    .doReadSync();
        } catch (IOException e) {
            throw new RuleException("文件解析失败！");
        }
        if (!CollectionUtils.isEmpty(list)) {
            // 判断数据是否有重复
            // 判断指标名称是否有重复
            Map<String, List<RuleIndexImportDTO>> indexNameMap = list.stream()
                    .collect(Collectors.groupingBy(RuleIndexImportDTO::getIndexName));
            for (Map.Entry<String, List<RuleIndexImportDTO>> entry : indexNameMap.entrySet()) {
                if (!CollectionUtils.isEmpty(entry.getValue()) && entry.getValue().size() > 1) {
                    RuleIndexImportDTO index = entry.getValue().get(0);
                    throw new RuleException("指标名称 [ " + index.getIndexName() + " ] 重复!");
                }
            }
            // 判断指标编码是否重复
            Map<String, List<RuleIndexImportDTO>> indexCodeMap = list.stream()
                    .collect(Collectors.groupingBy(RuleIndexImportDTO::getIndexCode));
            for (Map.Entry<String, List<RuleIndexImportDTO>> entry : indexCodeMap.entrySet()) {
                if (!CollectionUtils.isEmpty(entry.getValue()) && entry.getValue().size() > 1) {
                    RuleIndexImportDTO index = entry.getValue().get(0);
                    throw new RuleException("指标编码 [ " + index.getIndexCode() + " ] 重复!");
                }
            }
            RuleIndexSet indexSet = ruleIndexSetDao.queryById(indexSetId);
            if (Objects.isNull(indexSet)) {
                throw new RuleException("指标集不存在！");
            }
            List<RuleIndex> indices = new ArrayList<>();
            for (RuleIndexImportDTO importedIndex : list) {
                // 校验
                importedIndex.checkParam();
                // 保证指标名称和指标编码唯一
                RuleIndex indexName = new RuleIndex();
                indexName.setName(importedIndex.getIndexName());
                indexName.setIndexSetId(indexSetId);
                indexName.setEnable(RuleIndex.Constant.ENABLE);
                RuleIndex indexByName = ruleIndexDao.queryIndex(indexName);
                RuleIndex indexCode = new RuleIndex();
                indexCode.setCode(importedIndex.getIndexCode());
                indexCode.setIndexSetId(indexSetId);
                indexCode.setEnable(RuleIndex.Constant.ENABLE);
                RuleIndex indexByCode = ruleIndexDao.queryIndex(indexCode);
                if (Objects.nonNull(indexByName) || Objects.nonNull(indexByCode)) {
                    continue;
                }
                RuleIndex ruleIndex = new RuleIndex();
                ruleIndex.setId(null);
                ruleIndex.setCode(importedIndex.getIndexCode());
                ruleIndex.setName(importedIndex.getIndexName());
                ruleIndex.setDataType(importedIndex.getDataType());
                ruleIndex.setIndexAct(importedIndex.getIndexAct());
                ruleIndex.setIndexSetId(indexSetId);
                ruleIndex.setIndexDesc(importedIndex.getIndexDesc());
                ruleIndex.setCreateBy(RuleIndex.Constant.USER_NAME);
                ruleIndex.setCreateTime(new Date());
                ruleIndex.setUpdateBy(RuleIndex.Constant.USER_NAME);
                ruleIndex.setUpdateTime(new Date());
                ruleIndex.setEnable(RuleIndex.Constant.ENABLE);
                indices.add(ruleIndex);
            }
            // 批量新增
            if (!CollectionUtils.isEmpty(indices)) {
                ruleIndexDao.insertBatch(indices);
            }
            log.info("导入完成");
            log.info("本次共导入" + indices.size() + "条指标数据");
        } else {
            throw new RuleException("导入的数据不能为空！");
        }

        return result;
    }

(3).dao层代码

dao层省略

(4).SelectSheetWriteHandler实现SheetWriteHandler接口重写afterSheetCreate方法实现导入模板中的下拉选择框

public class SelectSheetWriteHandler implements SheetWriteHandler {

    @Override
    public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {

    }

    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        /** CHECKSTYLE:OFF:MagicNumber */
        Map<Integer, String[]> map = new HashMap<>();
        // 指标类型
        String[] dataType = {"String", "Integer", "Enum", "Date", "Boolean", "BigDecimal", "List"};
        // 指标形式
        String[] indexAct = {"In", "Out", "InOut"};
        // key->需要添加下拉框的列 value->下拉框中数据
        map.put(2, dataType);
        map.put(3, indexAct);
        // 获取工作簿
        Sheet sheet = writeSheetHolder.getSheet();
        // 设置下拉框
        DataValidationHelper helper = sheet.getDataValidationHelper();
        for (Map.Entry<Integer, String[]> entry : map.entrySet()) {
            // 表头不设置下拉框
            CellRangeAddressList addressList = new CellRangeAddressList(1, 65535, entry.getKey(), entry.getKey());
            // 设置下拉框数据
            DataValidationConstraint constraint = helper.createExplicitListConstraint(entry.getValue());
            DataValidation dataValidation = helper.createValidation(constraint, addressList);
            sheet.addValidationData(dataValidation);
        }
        /** CHECKSTYLE:ON:MagicNumber*/
    }
}

效果如下：

(5).EasyExcelListener继承AnalysisEventListener重写invokeHeadMap方法实现对模板的校验

public class EasyExcelListener extends AnalysisEventListener<RuleIndexImportDTO> {

    @Override
    public void invoke(RuleIndexImportDTO ruleIndexImportDTO, AnalysisContext analysisContext) {

    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {

    }

    @Override
    public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
        int count = 0;
        Field[] fields = RuleIndexImportDTO.class.getDeclaredFields();
        // 遍历字段进行判断
        for (Field field : fields) {
            // 获取字段上ExcelProperty注解信息
            ExcelProperty annotation = field.getAnnotation(ExcelProperty.class);
            if (null != annotation) {
                ++count;
                String headName = headMap.get(annotation.index());
                // 如果表头为空或者表头和当前设置的表头名称不同这模板错误
                if (StringUtils.isBlank(headName) || !headName.equals(annotation.value()[0])) {
                    throw new RuleException("模板错误，请检查导入模板！");
                }
            }
        }
        if (count != headMap.size()) {
            throw new RuleException("模板错误，请检查导入模板！");
        }
    }
}

三、导出

1.准备一个导出实体类

@ContentRowHeight(15)
@HeadRowHeight(22)
@ColumnWidth(15)
@ApiModel
public class RuleIndexExportDTO {
    @ApiModelProperty(value = "指标集名称")
    @ExcelProperty(value = "指标集名称", index = 0)
    private String indexSetName;

    @ApiModelProperty(value = "指标名称")
    @ExcelProperty(value = "指标名称", index = 1)
    private String indexName;

    @ApiModelProperty(value = "指标编码")
    @ExcelProperty(value = "指标编码", index = 2)
    private String indexCode;

    @ApiModelProperty(value = "指标类型")
    @ExcelProperty(value = "指标类型", index = 3)
    private String dataType;

    @ApiModelProperty(value = "指标形式")
    @ExcelProperty(value = "指标形式", index = 4)
    private String indexAct;

    @ColumnWidth(50)
    @ApiModelProperty(value = "指标描述")
    @ExcelProperty(value = "指标描述", index = 5)
    private String indexDesc;

    public String getIndexSetName() {
        return indexSetName;
    }

    public void setIndexSetName(String indexSetName) {
        this.indexSetName = indexSetName;
    }

    public String getIndexName() {
        return indexName;
    }

    public void setIndexName(String indexName) {
        this.indexName = indexName;
    }

    public String getIndexCode() {
        return indexCode;
    }

    public void setIndexCode(String indexCode) {
        this.indexCode = indexCode;
    }

    public String getDataType() {
        return dataType;
    }

    public void setDataType(String dataType) {
        this.dataType = dataType;
    }

    public String getIndexAct() {
        return indexAct;
    }

    public void setIndexAct(String indexAct) {
        this.indexAct = indexAct;
    }

    public String getIndexDesc() {
        return indexDesc;
    }

    public void setIndexDesc(String indexDesc) {
        this.indexDesc = indexDesc;
    }
}

2.编写导出接口相关代码

(1).controller层代码

    @GetMapping("/{indexSetId}/export")
    @ApiOperation("导出指标")
    public void exportIndex(@PathVariable("indexSetId") @ApiParam("指标集id") Integer indexSetId,
                              HttpServletResponse response) {
        if (null == indexSetId) {
            throw new RuleException("指标集id不能为空");
        }
        ruleIndexService.exportIndex(indexSetId, response);
    }

(2).service层代码

    /**
     * 导出指标
     *
     * @param indexSetId
     * @param response
     */
    void exportIndex(Integer indexSetId, HttpServletResponse response);

    /**
     * 导出指标
     *
     * @param indexSetId
     * @param response
     */
    @Override
    public void exportIndex(Integer indexSetId, HttpServletResponse response) {
        RuleIndexSet indexSet = ruleIndexSetDao.queryById(indexSetId);
        if (Objects.isNull(indexSet)) {
            throw new RuleException("指标集不存在！");
        }
        String fileName = indexSet.getName() + "指标集数据";
        List<RuleIndexExportDTO> indexExportDTOS = ruleIndexDao.queryByIndexSetId(indexSetId);
        try {
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            fileName = URLEncoder.encode(fileName, "UTF-8");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
            EasyExcel.write(response.getOutputStream())
                    .head(RuleIndexExportDTO.class)
                    .sheet(indexSet.getName() + "指标集数据")
                    .doWrite(indexExportDTOS);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

(3).dao层代码

dao层代码省略