EasyExcel工具类

2024-01-17 调整：

• 新增多Sheet导出方法
• 解除单元格长度限制
• 导出实体类通过继承ExcelBaseRow，自动创建序号列，无需手动赋值序号

EasyExcel 通用工具类

/**
 * EasyExcel 通用工具类
 *
 * @author wjd
 * @date: 2023/9/13 17:38
 */
public class EasyExcelUtil {

    private static final Logger logger = LoggerFactory.getLogger(EasyExcelUtil.class);

    private static final Validator validator = Validation.buildDefaultValidatorFactory().getValidator();

    private static final short FONT_SIZE_TEN = 10;

    /**
     * easyExcel 导出文件
     *
     * @param response    http响应
     * @param exportParam 导出参数
     */
    public static <T> void export(HttpServletResponse response, ExportParam<T> exportParam) {
        try {
            // 解除Cell内容长度32767的限制
            initCellMaxTextLength();
            // 响应设置
            response.setHeader("Content-Type", "application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "attachment;filename=" +
                    URLEncoder.encode(exportParam.fileName, "UTF-8") + ExcelTypeEnum.XLSX.getValue());
            response.setCharacterEncoding("UTF-8");
            // 设置导出对象类型和返回类型
            ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream(), exportParam.clazz)
                    .excelType(ExcelTypeEnum.XLSX)
                    .build();
            // 设置sheet名称和普通默认样式
            WriteSheet writeSheet = EasyExcel.writerSheet(exportParam.sheetName)
                    .registerWriteHandler(getDefaultStyleStrategy())
                    .build();
            // 填充序号值
            if (exportParam.clazz.getSuperclass().equals(ExcelBaseRow.class)) {
                AtomicInteger index = new AtomicInteger(0);
                exportParam.dataList.forEach(data -> ((ExcelBaseRow) data).setIndex(index.incrementAndGet()));
            }
            // 导出数据写入
            excelWriter.write(exportParam.dataList, writeSheet);
            // EasyExcel 导出完成自动关闭流，也是可以再手动关一次的（官网这么写的）
            excelWriter.finish();
        } catch (Exception e) {
            String errMsg = ExceptionUtils.parseException(e.getMessage());
            logger.error("{}导出失败，失败原因为: {}", exportParam.fileName, errMsg);
            throw new BusinessException(CommonEnums.BUSINESS_ERROR, msg -> errMsg, e);
        }
    }

    /**
     * easyExcel 导出多Sheet文件
     *
     * @param response http响应
     * @param param    导出参数
     */
    public static <T> void exportMultiSheet(HttpServletResponse response, ExportMultiSheetParam param) {
        try {
            if (ObjectUtils.isEmpty(param) || ObjectUtils.isEmpty(param.sheets)) {
                return;
            }
            // 解除Cell内容长度32767的限制
            initCellMaxTextLength();
            // 响应设置
            response.setHeader("Content-Type", "application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "attachment;filename=" +
                    URLEncoder.encode(param.fileName, "UTF-8") + ExcelTypeEnum.XLSX.getValue());
            response.setCharacterEncoding("UTF-8");
            ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream())
                    .excelType(ExcelTypeEnum.XLSX)
                    .build();
            param.sheets.forEach(sheet -> {
                // 设置sheet名称和普通默认样式
                WriteSheet writeSheet = EasyExcel.writerSheet(sheet.sheetName)
                        .head(sheet.clazz)
                        .registerWriteHandler(getDefaultStyleStrategy())
                        .build();
                // 填充序号值
                if (sheet.clazz.getSuperclass().equals(ExcelBaseRow.class)) {
                    AtomicInteger index = new AtomicInteger(0);
                    sheet.dataList.forEach(data -> ((ExcelBaseRow) data).setIndex(index.incrementAndGet()));
                }
                // 导出数据写入
                excelWriter.write(sheet.dataList, writeSheet);
            });
            // EasyExcel 导出完成自动关闭流，也是可以再手动关一次的（官网这么写的）
            excelWriter.finish();
        } catch (Exception e) {
            String errMsg = ExceptionUtils.parseException(e.getMessage());
            logger.error("{}导出失败，失败原因为: {}", param.fileName, errMsg);
            throw new BusinessException(CommonEnums.BUSINESS_ERROR, msg -> errMsg, e);
        }
    }

    private static short getNewCellNum(Row row) {
        return row.getLastCellNum() == (short) -1 ? 0 : row.getLastCellNum();
    }

    /**
     * easyExcel 导入解析
     *
     * @param file  导入文件
     * @param clazz 导入对象类
     */
    public static <T> List<T> parse(MultipartFile file, Class<T> clazz) {
        try {
            List<T> dataList = new ArrayList<>();
            ExcelDataImportListener<T> excelDataImportListener = new ExcelDataImportListener<T>(clazz, dataList);
            // 读取excel数据到result集合中
            EasyExcel.read(file.getInputStream(), clazz, excelDataImportListener)
                    .sheet()
                    // 去除文件中的一行标题行
                    .headRowNumber(1)
                    .doRead();

            return dataList;
        } catch (Exception e) {
            logger.error("Excel文件读取失败，失败原因为: {}", ExceptionUtils.parseException(e.getMessage()));
            throw new BusinessException(CommonEnums.BUSINESS_ERROR, msg -> "Excel文件读取失败", e);
        }
    }

    public static class ExcelDataImportListener<T> extends AnalysisEventListener<T> {

        private static final Logger logger = LoggerFactory.getLogger(ExcelDataImportListener.class);

        private final Class<T> clazz;

        private final List<T> result;

        public ExcelDataImportListener(Class<T> clazz, List<T> result) {
            this.clazz = clazz;
            this.result = result;
        }

        @Override
        public void onException(Exception e, AnalysisContext analysisContext) {
            logger.error("文件导入解析失败，失败原因:", e);
            // 更新导入状态
            String exceptionMsg = ExceptionUtils.parseException(e.getMessage());
            if (e instanceof ExcelDataConvertException) {
                ExcelDataConvertException excelDataConvertException = (ExcelDataConvertException) e;
                exceptionMsg = "导入文件解析失败，第" + (excelDataConvertException.getRowIndex() + 1) + "行,第" + (excelDataConvertException.getColumnIndex() + 1) + "列数据解析异常，数据为"
                        + excelDataConvertException.getCellData().getStringValue() + ",失败原因：" + parseExceptionMsg(e);
            }
            throw new BusinessException(CommonEnums.BUSINESS_ERROR, exceptionMsg);
        }

        /**
         * 转换ExcelDataConvertException中的英文错误信息
         *
         * @param e excel导入异常
         * @return 转换后的错误信息
         */
        private String parseExceptionMsg(Exception e) {
            if (StringUtils.contains(e.getMessage(), "to class java.time.LocalDate error")) {
                return "时间格式错误！";
            }

            if (StringUtils.contains(e.getMessage(), "to class java.math.BigDecimal error")) {
                return "数字格式错误！";
            }
            return ExceptionUtils.parseException(e.getMessage());
        }

        @Override
        public void invokeHead(Map<Integer, ReadCellData<?>> map, AnalysisContext analysisContext) {
            // 获取数据实体的字段列表
            Field[] fields = clazz.getDeclaredFields();
            // 导入模板对象表头列表
            List<String> tempHeadList = Arrays.stream(fields)
                    // 只选取有ExcelProperty注解的字段
                    .filter(field -> Objects.nonNull(field.getAnnotation(ExcelProperty.class)))
                    // 通过ExcelProperty注解获取规定的表头名列表
                    .map(field -> CollectionUtils.firstElement(Arrays.asList(field.getAnnotation(ExcelProperty.class).value())))
                    .collect(Collectors.toList());
            // 导入文件表头列表
            List<String> fileHeadList = map.values().stream().map(CellData::getStringValue).collect(Collectors.toList());
            // 判断用户导入表格的标题头是否完全包含模板的标题头
            if (!fileHeadList.containsAll(tempHeadList)) {
                tempHeadList.removeAll(fileHeadList);
                logger.error("文件导入校验失败，失败原因: {}[{}]", "导入文件与系统模板不匹配，缺少标题头", String.join(", ", tempHeadList));
                throw new BusinessException(CommonEnums.BUSINESS_ERROR,
                        String.format("Excel文件读取失败，失败原因: 导入文件与系统模板不匹配，缺少标题头[%s]", String.join(", ", tempHeadList)));
            }
        }

        @Override
        public void invoke(T data, AnalysisContext context) {
            // 字段注解校验
            String errMsgPrefix = "导入文件读取失败, 第" + context.readRowHolder().getRowIndex() + "行数据异常";
            validate(data, errMsgPrefix);
            result.add(data);
        }

        @Override
        public void extra(CellExtra cellExtra, AnalysisContext analysisContext) {

        }

        @Override
        public void doAfterAllAnalysed(AnalysisContext analysisContext) {

        }
    }

    /**
     * 导出参数
     *
     * @param <T> 导出对象类
     */
    public static class ExportParam<T> {

        /**
         * 导出对象类
         */
        private Class<T> clazz;

        /**
         * 导出数据列表
         */
        private List<T> dataList;

        /**
         * 导出文件名
         */
        private String fileName = "导出文件";

        /**
         * sheet名
         */
        private String sheetName = "sheet1";

        public Class<T> getClazz() {
            return clazz;
        }

        public void setClazz(Class<T> clazz) {
            this.clazz = clazz;
        }

        public List<T> getDataList() {
            return dataList;
        }

        public void setDataList(List<T> dataList) {
            this.dataList = dataList;
        }

        public String getFileName() {
            return fileName;
        }

        public void setFileName(String fileName) {
            this.fileName = fileName;
        }

        public String getSheetName() {
            return StringUtils.isBlank(sheetName) ? null : sheetName;
        }

        public void setSheetName(String sheetName) {
            this.sheetName = sheetName;
        }
    }

    public static class ExportMultiSheetParam {

        /**
         * 导出文件名
         */
        private String fileName = "导出文件";

        /**
         * sheet列表
         */
        private List<SheetParam> sheets;

        public String getFileName() {
            return fileName;
        }

        public void setFileName(String fileName) {
            this.fileName = fileName;
        }

        public List<SheetParam> getSheets() {
            return sheets;
        }

        public void setSheets(List<SheetParam> sheets) {
            this.sheets = sheets;
        }
    }

    public static class SheetParam<T> {
        /**
         * 导出对象类
         */
        private Class<T> clazz;

        /**
         * 导出数据列表
         */
        private List<T> dataList;

        /**
         * sheet名
         */
        private String sheetName = "sheet1";

        public Class<T> getClazz() {
            return clazz;
        }

        public void setClazz(Class<T> clazz) {
            this.clazz = clazz;
        }

        public List<T> getDataList() {
            return dataList;
        }

        public void setDataList(List<T> dataList) {
            this.dataList = dataList;
        }

        public String getSheetName() {
            return sheetName;
        }

        public void setSheetName(String sheetName) {
            this.sheetName = sheetName;
        }

    }
    /**
     * easyExcel样式模板
     * 格表头 11号、加粗、宋体
     * 内容 10号
     *
     * @return 样式模板
     */
    private static HorizontalCellStyleStrategy getDefaultStyleStrategy() {
        // 表头样式居中对齐、字体宋体11号不加粗（默认加粗）、自动换行（已默认）
        WriteFont writeFont = new WriteFont();
        writeFont.setBold(false);
        writeFont.setFontName("宋体");
        writeFont.setFontHeightInPoints(FONT_SIZE_TEN);

        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        headWriteCellStyle.setWriteFont(writeFont);
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        // 内容样式垂直、水平居中、字体宋体10号不加粗（默认不加粗）、自动换行
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
        contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
        contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
        contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        contentWriteCellStyle.setWrapped(true);
        contentWriteCellStyle.setWriteFont(writeFont);
        return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
    }

    /**
     * 参数校验
     *
     * @param obj 校验参数对象
     */
    public static void validate(Object obj, String errMsgPrefix) {
        Map<String, String> validMsg = new LinkedHashMap<>();
        Set<ConstraintViolation<Object>> constraintViolations = validator.validate(obj);
        for (ConstraintViolation<Object> c : constraintViolations) {
            validMsg.put(c.getPropertyPath().toString(), c.getMessage());
        }
        if (ObjectUtils.isNotEmpty(constraintViolations)) {
            throw new BusinessException(CommonEnums.BUSINESS_ERROR, errMsgPrefix + " " + validMsg.values().toString());
        }
    }

    /**
     * 初始化 cell 内容长度
     * cell 原本内容长度限制 32767  现修改为Integer.MAX_VALUE
     */
    public static void initCellMaxTextLength() {
        SpreadsheetVersion excel2007 = SpreadsheetVersion.EXCEL2007;
        if (Integer.MAX_VALUE != excel2007.getMaxTextLength()) {
            Field field;
            try {
                field = excel2007.getClass().getDeclaredField("_maxTextLength");
                field.setAccessible(true);
                field.set(excel2007, Integer.MAX_VALUE);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
}

导出对象

@ExcelIgnoreUnannotated
@ContentRowHeight(25)
@HeadRowHeight(25)
public class ExcelDTO extends ExcelBaseRow {

    /**
     * 本系统单位编码
     */
    @ColumnWidth(25)
    @ExcelProperty(value = "本系统单位编码")
    private String code;

    /**
     * 本系统单位名称
     */
    @ColumnWidth(30)
    @ExcelProperty(value = "本系统单位名称")
    private String name;

    /**
     * 转入单位编码
     */
    @ColumnWidth(25)
    @ExcelProperty(value = "转入单位编码")
    private String busCode;

    public String getCode() {
        return code;
    }

    public void setCode(String code) {
        this.code = code;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getBusCode() {
        return busCode;
    }

    public void setBusCode(String busCode) {
        this.busCode = busCode;
    }
}

ExcelBaseRow

public class ExcelBaseRow {

    /**
     * 序号
     */
    @ColumnWidth(7)
    @ExcelProperty(value = "序号", order = 0)
    private Integer index;

    public Integer getIndex() {
        return index;
    }

    public void setIndex(Integer index) {
        this.index = index;
    }
}

导入使用

@Override
public void batchSave(MultipartFile file) {
    // 读取导入文件
    List<ExcelDTO> excelDTOList = EasyExcelUtil.parse(file, ExcelDTO.class);
    // 业务逻辑处理
    ...
    // 批量插入
    excelMapper.insertMultiple(excelDTOList);
}

导出使用

@PostMapping("/excel/list")
public void batchImportAuthMachineTemplate(HttpServletResponse response) {
    EasyExcelUtil.ExportParam<AppInfoAuthMachineExcelDTO> exportParam = new EasyExcelUtil.ExportParam<>();
    exportParam.setClazz(AppInfoAuthMachineExcelDTO.class);
    exportParam.setDataList(Collections.nCopies(8, new AppInfoAuthMachineExcelDTO()));
    exportParam.setFileName("应用授权机器导入模板");
    exportParam.setSheetName("Sheet1");
    EasyExcelUtil.export(response, exportParam);
}

多Sheet导出使用

@PostMapping("/excel/list")
public void batchImportAuthMachineTemplate(HttpServletResponse response) {
	List<EasyExcelUtil.SheetParam> sheetParamList = new ArrayList<>();
	// 导出收费项目
    List<BasicContrastItemExcelDTO> itemContrastList = basicContrastItemService.listContrastItem(query).stream()
            .map(entity -> mapper.map(entity, BasicContrastItemExcelDTO.class)).collect(Collectors.toList());
    EasyExcelUtil.SheetParam<BasicContrastItemExcelDTO> itemParams = new EasyExcelUtil.SheetParam<>();
    itemParams.setClazz(BasicContrastItemExcelDTO.class);
    itemParams.setSheetName("收费项目");
    itemParams.setDataList(itemContrastList);
    sheetParamList.add(itemParams);

	// 其他Sheet
	// ...
	sheetParamList.add(dictParams);
	
	// 设置导出多Sheet参数
	EasyExcelUtil.ExportMultiSheetParam multiSheetParam = new EasyExcelUtil.ExportMultiSheetParam();
	multiSheetParam.setFileName(basicContrastTemplet.getFtempletcode() + "-" + basicContrastTemplet.getFtempletname());
	multiSheetParam.setSheets(sheetParamList);
	EasyExcelUtil.exportMultiSheet(response, multiSheetParam);
}