EasyExcel自定义导出实现

39 阅读4分钟

一、前言

在实际开发中,常常会遇到需要根据用户需求自定义导出字段的场景。用户希望在前端界面上自由选择需要导出的字段(如下图所示)。本文档将深入讲解如何基于RuoYi-Vue-Plus框架,结合EasyExcel库,实现自定义导出功能,满足企业多样化的数据导出需求。

image-20250306141353641.png

二、实现

1.添加导出前方法

对于熟悉EasyExcel的小伙伴来说,都知道导出时关键的一步是为实体属性添加@ExcelProperty注解。在导出前,我们需要获取这些带有注解的字段。这决定了导出的Excel表格将包含哪些数据列。接下来,我直接为大家展示获取这些注解字段的代码实现。话不多说,直接上代码!!!

1.Controller层代码

    /**
     * 导出前方法
     */
    @SaCheckPermission("mqtt:devices:export")
    @Log(title = "考勤设备", businessType = BusinessType.EXPORT)
    @GetMapping("/prepareDataForExport")
    public R<List<Map<String, Object>>> prepareDataForExport() {
        List<Map<String, Object>> entityHeaders = CustomExcelTool.extractHeadersFromEntity(AttendanceDevicesVo.class);
        return R.ok(entityHeaders);
    }

2.CustomExcelTool工具类代码

/**
 * 自定义Excel工具
 *
 * @Author: 陈江灿
 * @CreateTime: 2025-03-06
 */
public class CustomExcelTool {
    /**
     * 从实体类中提取Excel表头
     * @param attendanceDevicesVoClass
     * @return
     */
    public static List<Map<String, Object>> extractHeadersFromEntity(Class<?> attendanceDevicesVoClass) {
        List<Map<String, Object>> headers = new ArrayList<>();
        Field[] fields = attendanceDevicesVoClass.getDeclaredFields();
        for (Field field : fields) {
            if (field.isAnnotationPresent(ExcelProperty.class)) {
                ExcelProperty excelProperty = field.getAnnotation(ExcelProperty.class);
                String[] headerNames = excelProperty.value();
                if (headerNames.length > 0) {
                    Map<String, Object> headerMap = new HashMap<>();
                    headerMap.put("value", field.getName());
                    headerMap.put("label", headerNames[0]);
                    headerMap.put("isshow", true);
                    headers.add(headerMap);
                }
            }
        }
        return headers;
    }
}

重启项目我们可以测试看看(如下图),我们可以看到我们成功获取了带@ExcelProperty注解的实体类字段。

PS:这里大家也可以建一个实体去存value,label,isshow更好一点,我是图方便。

image-20250306142336469.png

2.修改导出方法

在上一步我们获取了带@ExcelProperty注解的实体类字段,到后面的工作就简单了,我们只需要把用户选择好要导出的字段返回给后端就好了。还是老规矩直接上代码!!!

1.Controller层

    /**
     * 导出考勤设备列表
     */
    @SaCheckPermission("mqtt:devices:export")
    @Log(title = "考勤设备", businessType = BusinessType.EXPORT)
    @PostMapping("/export")
    public void export(AttendanceDevicesBo bo, HttpServletResponse response) throws Exception  {
        List<AttendanceDevicesVo> list = attendanceDevicesService.queryList(bo);
        List<List<String>> head = CustomExcelTool.GetHead(bo.getDtoList());
        List<List<Object>> data = CustomExcelTool.GetData(bo.getDtoList(), list);
        ExcelUtil.exportExcel(head, data, "考勤设备",  response);
    }

2.获取表头

    /**
     * 获取表头
     * @param head
     * @return
     * @throws Exception
     * @Author: 陈江灿
     * @CreateTime: 2025-03-06
     */
    public static List<List<String>> GetHead(String head) throws Exception {
        ObjectMapper objectMapper = new ObjectMapper();
        JsonNode jsonArray = objectMapper.readTree(head);
        List<List<String>> heads = new ArrayList<>();
        for (JsonNode node : jsonArray) {
            if (node.get("isshow").asBoolean()) {
                heads.add(Collections.singletonList(node.get("label").asText()));
            }
        }
        return heads;
    }

3.获取数据

    /**
     * 获取数据
     * @param head
     * @return
     * @Author: 陈江灿
     * @CreateTime: 2025-03-06
     * @throws Exception
     */
    public static List<List<Object>> GetData(String head, List<?> list) throws Exception {
        ObjectMapper objectMapper = new ObjectMapper();
        JsonNode jsonArray = objectMapper.readTree(head);
        List<List<Object>> data = new ArrayList<>();
        for (int i = 0; i < list.size(); i++) {
            data.add(new ArrayList<>());
        }
        for (JsonNode node : jsonArray) {
            if (node.get("isshow").asBoolean()) {
                String valueKey = node.get("value").asText();
                for (int i = 0; i < list.size(); i++) {
                    Object vo = list.get(i);
                    String value = getValueFromVo(vo, valueKey);
                    data.get(i).add(value);
                }
            }
        }
        return data;
    }

    /**
     * 从给定对象的指定字段检索值,应用任何必要的格式
     * @param vo
     * @param key
     * @Author: 陈江灿
     * @CreateTime: 2025-03-06
     * @return
     */
    private static String getValueFromVo(Object vo, String key) {
    try {
        Field field = vo.getClass().getDeclaredField(key);
        field.setAccessible(true);
        Object value = field.get(vo);

        // 获取字段上的 @ExcelDictFormat 注解
        ExcelDictFormat dictFormat = field.getAnnotation(ExcelDictFormat.class);
        if (dictFormat != null) {
            ExcelDictConvert dictConvert = new ExcelDictConvert();
            ExcelContentProperty contentProperty = new ExcelContentProperty();
            Field contentPropertyField = ExcelContentProperty.class.getDeclaredField("field");
            contentPropertyField.setAccessible(true);
            contentPropertyField.set(contentProperty, field);
            WriteCellData<String> cellData = dictConvert.convertToExcelData(value, contentProperty, null);
            return cellData != null ? cellData.getStringValue() : "";
        }
        return value != null ? value.toString() : "";
    } catch (NoSuchFieldException | IllegalAccessException e) {
        e.printStackTrace();
        return "";
    }
}

3.导出代码

    /**
     * 自定义表头导出
     * @Author: 陈江灿
     * @CreateTime: 2025-03-06
     * @param head
     * @param data
     * @param sheetName
     * @param response
     * @param <T>
     */
    public static <T> void exportExcel(List<List<String>> head, List<List<Object>> data, String sheetName, HttpServletResponse response) {
        try {
            resetResponse(sheetName + ".xlsx", response);
            ServletOutputStream os = response.getOutputStream();
            ExcelWriter excelWriter = EasyExcel.write(os)
                .head(head) // 动态表头
                .registerWriteHandler(new CustomCellWriteHandler())
                .registerWriteHandler(new CustomColumnWidthStyleStrategy())
                .build();
            WriteSheet writeSheet = EasyExcel.writerSheet(sheetName).build();
            excelWriter.write(data, writeSheet);
            excelWriter.finish();
            os.flush();
        } catch (IOException e) {
            throw new RuntimeException("导出Excel异常", e);
        }
    }

扩展部分

在ruoyi-vue-plus中还自定义了字典转换器和枚举转换器,GetData方法操控的是list,无法进行转换,我们需要修改一下getValueFromVo方法。

    /**
     * 从给定对象的指定字段检索值,应用任何必要的格式
     * @Author: 陈江灿
     * @CreateTime: 2025-03-06
     * @param vo
     * @param key
     * @return
     */
    private static String getValueFromVo(Object vo, String key) {
        try {
            Field field = vo.getClass().getDeclaredField(key);
            field.setAccessible(true);
            Object value = field.get(vo);
            ExcelDictFormat dictFormat = field.getAnnotation(ExcelDictFormat.class);
            if (dictFormat != null) {
                ExcelDictConvert dictConvert = new ExcelDictConvert();
                ExcelContentProperty contentProperty = new ExcelContentProperty();
                Field contentPropertyField = ExcelContentProperty.class.getDeclaredField("field");
                contentPropertyField.setAccessible(true);
                contentPropertyField.set(contentProperty, field);
                WriteCellData<String> cellData = dictConvert.convertToExcelData(value, contentProperty, null);
                return cellData != null ? cellData.getStringValue() : "";
            }
            ExcelEnumFormat enumFormat = field.getAnnotation(ExcelEnumFormat.class);
            if (dictFormat != null) {
                Class<?> enumClass = enumFormat.enumClass();
                String textField = enumFormat.textField();
                for (Object enumConstant : enumClass.getEnumConstants()) {
                    Field enumField = enumConstant.getClass().getDeclaredField(textField);
                    enumField.setAccessible(true);
                    String enumValue = enumField.get(enumConstant).toString();
                    if (Objects.equals(value, enumConstant.toString())) {
                        return enumValue;
                    }
                }
            }
            return value != null ? value.toString() : "";
        } catch (NoSuchFieldException | IllegalAccessException e) {
            e.printStackTrace();
            return "";
        }
    }

总结

方法就是通过前后端约定,将字段的显示名称(headName)与实体属性名称(fieldName)对应起来。前端用户勾选所需导出的字段后,将这些字段的fieldName传递给后端。后端接收后,利用Java反射机制动态匹配实体类中的属性,根据用户选择决定导出哪些字段。此方法提高了系统的灵活性,允许用户自定义导出内容。后端根据配置动态构建导出数据,最终生成并输出符合用户需求的Excel文件。

vx:chenbai0511

有什么不懂的也可以加微交流哦qwq

17c8bc62cbd5b58c27775e0c2ff83bd.jpg

推荐文章: