实现Excel动态字段导出数据且实现模板提示内容下载

罗_亮

已于 2023-10-12 15:14:16 修改

阅读量206

点赞数 1

文章标签： java 开发语言

于 2023-10-08 17:06:39 首次发布

本文链接：https://blog.csdn.net/qq_49132126/article/details/133683531

版权

需求：

1.要将可展示字段于前端展示供用户选择后传入后端，后端依据用户选择字段进行导出。

2.下载的模板需要有提示（示例）信息

实现：

依赖：

        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-spring-boot-starter</artifactId>
            <version>4.4.0</version>
        </dependency>

1.自定义注解：用于标识需要导出类的字段

/**
 * @author luoliang
 * @description
 * @date 2023/10/7 16:04
 */
@Target({FIELD})
@Retention(RUNTIME)
public @interface EzFiled {
    int sort() default 0;

    String name() default "";

    String example() default "";
}

2.定义字段映射基类：用于前端展示、用户选择类的字段信息

/**
 * @author luoliang
 * @description
 * @date 2023/10/7 16:19
 */
@Data
@AllArgsConstructor
@NoArgsConstructor
@Builder
public class EzFiledRo {
    private String englishFiledName;

    private String chineseTitleName;
}

3.字段展示工具类：用于前端展示字段信息，工具类依据EzFiledRo 进行展示

/**
 * @author luoliang
 * @description
 * @date 2023/10/8 10:57
 */
public class EzFiledUtil {
    public static <T> List<EzFiledRo> exportPageListDynamicFieldList(Class<T> objectClass){
        Field[] declaredFields = objectClass.getDeclaredFields();
        List<EzFiledRo> dynamicFieldList = new ArrayList<>();
        for (Field declaredField : declaredFields) {
            declaredField.setAccessible(true);
            EzFiled dynamicExportAnnotation = declaredField.getAnnotation(EzFiled.class);
            String chineseTitle = dynamicExportAnnotation.name();
            dynamicFieldList.add(EzFiledRo.builder().englishFiledName(declaredField.getName()).chineseTitleName(chineseTitle).build());
        }
        return dynamicFieldList;
    }
}

4.字段映射工具类

/**
 * @author luoliang
 * @description 字段工具类
 * @date 2023/10/8 10:34
 */
public class EzMappingUtil {
    public static List<EzFiledRo> filter(Object exportBeanFrom) {
        List<EzFiledRo> list = new ArrayList<>();
        if (exportBeanFrom instanceof List) {
            List exportList = ((List<?>) exportBeanFrom);
            for (Object o : exportList) {
                EzFiledRo ezFiledRo = new EzFiledRo();
                Class<?> clazz = o.getClass();
                Field[] fields = clazz.getDeclaredFields();
                for (Field field : fields) {
                    field.setAccessible(true);
                    try {
                        if (field.getName().equals("englishFiledName")) {
                            ezFiledRo.setEnglishFiledName((String) field.get(o));
                        } else {
                            ezFiledRo.setChineseTitleName((String) field.get(o));
                        }
                    } catch (IllegalAccessException e) {
                        e.printStackTrace();
                    }
                }
                list.add(ezFiledRo);
            }
        }
        return list;
    }

    public static String[] getExportTitleAndSortArr(List<EzFiledRo> beanExportFieldMap, Class<?> clazz) {
        Collections.sort(
                beanExportFieldMap, Comparator.comparingInt(ro ->
                        getFieldSort(ro.getEnglishFiledName(), clazz)));
        String[] titles = beanExportFieldMap.stream()
                .map(EzFiledRo::getChineseTitleName)
                .toArray(String[]::new);
        return titles;
    }

    public static String[] getExportFieldAndSortArr(List<EzFiledRo> beanExportFieldMap, Class<?> clazz) {
        Collections.sort(beanExportFieldMap, Comparator.comparingInt(ro -> getFieldSort(ro.getEnglishFiledName(), clazz)));
        String[] values = beanExportFieldMap.stream()
                .map(EzFiledRo::getEnglishFiledName)
                .toArray(String[]::new);
        return values;
    }

    private static int getFieldSort(String fieldName, Class<?> clazz) {
        try {
            Field field = clazz.getDeclaredField(fieldName);
            EzFiled ezFiled = field.getAnnotation(EzFiled.class);
            if (ezFiled != null) {
                return ezFiled.sort();
            }
        } catch (NoSuchFieldException e) {
            e.printStackTrace();
        }
        return 0;
    }
}

5.导出工具类更改字体、列宽等等可自行在此类更改

/**
 * @author: luoliang
 * @description: 导出excel工具类
 * @date: 2023/8/8 16:29
 */
public class EzExportUtil {
   
    /**
     * @param response:  传个response
     * @param tableName: excel中的表名
     * @param list:      数据源
     * @param clazz:     配置的映射对象
     * @return void
     * @author luoliang
     * @description 批量导出
     * @date 2023/8/8 16:30
     */
    public static void exportExcel(HttpServletResponse response, String excelName, String tableName, List<?> list, Class<?> clazz) throws IOException {
        // 批量导出
        ExportParams exportParams = new ExportParams();
        exportParams.setSheetName(tableName);
        int totalPage = (list.size() / 1000) + 1;
        int pageSize = 1000;
        Workbook workbook = ExcelExportUtil.exportBigExcel(exportParams, clazz, (queryParams, page) -> {
            if (page > totalPage) {
                return null;
            }
            // fromIndex开始索引，toIndex结束索引
            int fromIndex = (page - 1) * pageSize;
            int toIndex = page != totalPage ? fromIndex + pageSize : list.size();
            return new ArrayList<>(list.subList(fromIndex, toIndex));
        }, totalPage);
        // 设置响应头
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setHeader("content-disposition",
                "attachment;fileName=" + URLEncoder.encode(excelName + "-" + new SimpleDateFormat("yyyyMMdd").format(new Date()) + ".xlsx", "UTF-8"));
        ServletOutputStream outputStream = response.getOutputStream();
        // 输出
        workbook.write(outputStream);
        // 关闭资源
        outputStream.close();
    }

    /**
     * <p>@Description: 根据 所选动态列，以及数据集进行导出excel</p >
     * <p>@param [exportBeanFrom, dataList, fileName]</p >
     * <p>@return void</p >
     * <p>@throws </p >
     */
    public static <T> void exportByTitleAndData(Object exportBeanFrom, List<T> dataList, String fileName,String sheetName, Class clazz) {
        // 处理参数: 需要导出的英文字段名 exportFieldArr, 中文表头 title
        List<EzFiledRo> filter = EzMappingUtil.filter(exportBeanFrom);
        String[] title = EzMappingUtil.getExportTitleAndSortArr(filter, clazz);
        String[] exportFieldArr = EzMappingUtil.getExportFieldAndSortArr(filter, clazz);
        // 根据参数 生成工作簿，并写入文件流
        if (title.length > 0 && exportFieldArr.length > 0) {
            Workbook workbook = EzExportUtil.getWorkbook(dataList, exportFieldArr, title,sheetName);
            EzExportUtil.writeToResponse(workbook, fileName);
        }
    }

    /**
     * <p>@Description: 将文件流写会回 response 中</p >
     * <p>@param [workbook, fileName]</p >
     * <p>@return void</p >
     * <p>@throws </p >
     */
    public static void writeToResponse(Workbook workbook, String fileName) {
        ServletRequestAttributes attributes = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
        HttpServletResponse response = attributes.getResponse();
        response.setContentType("application/vnd.ms-excel;charset=UTF-8");
        // 假设fileName是原始的文件名
        String encodedFileName;
        try {
            encodedFileName = URLEncoder.encode(fileName, "UTF-8");
        } catch (UnsupportedEncodingException e) {
            // 处理编码异常
            e.printStackTrace();
            return;
        }
        response.setHeader("Content-Disposition", "attachment;filename=" + encodedFileName + ".xlsx");
        OutputStream outputStream = null;
        try {
//            outputStream = new FileOutputStream("D:/"+fileName+".xlsx");
            outputStream = response.getOutputStream();
            workbook.write(outputStream);
            outputStream.flush();
            outputStream.close();
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e.getMessage());
        }
    }


    /**
     * <p>@Description: 根据 数据集、导出列、表头汉字 创建工作簿</p >
     * <p>@param [dataSet 数据集, exportFieldArr 需要导出的字段列, titles 导出列对应的中文表头]</p >
     * <p>@return org.apache.poi.xssf.usermodel.XSSFWorkbook</p >
     * <p>@date 15:19 15:19</p >
     */
    public static <T> XSSFWorkbook getWorkbook(Collection<T> dataSet, String[] exportFieldArr, String[] titles,String sheetName) {
        // 校验变量和预期输出excel列数是否相同
        if (exportFieldArr.length != titles.length) {
            return null;
        }
        // 存储每一行的数据
        List<String[]> list = new ArrayList<>();
        if (dataSet != null && !dataSet.isEmpty()) {
            for (Object obj : dataSet) {
                // 获取到每一行的属性值数组
                list.add(getValues(obj, exportFieldArr));
            }
        }
        return getWorkbook(titles, list,sheetName);
    }

    public static XSSFWorkbook getWorkbook(String[] titles, List<String[]> list,String sheetName) {
        // 定义表头
        String[] title = titles;
        // 创建excel工作簿
        XSSFWorkbook workbook = new XSSFWorkbook();
        // 创建工作表sheet
        XSSFSheet sheet = workbook.createSheet(sheetName);
        // 创建第一行
        XSSFRow row = sheet.createRow(0);
        XSSFCell cell = null;
        // 插入第一行数据的表头
        row.setHeight((short) (24 * 20));
        CellStyle headerCommonStyle = getHeaderCommonStyle(workbook);
        for (int i = 0; i < title.length; i++) {
            cell = row.createCell(i);
            cell.setCellValue(title[i]);
            cell.setCellStyle(headerCommonStyle);
        }
        // 数据行渲染
//        ExportCommonService.getAllExcelField()
        CellStyle bodyStyle = getBodyStyle(workbook);
        int idx = 1;
        if (list != null && !list.isEmpty()) {
            for (String[] strings : list) {
                XSSFRow nrow = sheet.createRow(idx++);
                XSSFCell ncell = null;
                for (int i = 0; i < strings.length; i++) {
                    ncell = nrow.createCell(i);
                    ncell.setCellValue(strings[i]);
                    ncell.setCellStyle(bodyStyle);
                }
            }
        }
        // 设置固定列宽
        setColumnWidth(titles, sheet);
        return workbook;
    }

    // 设置固定列宽
    public static void setColumnWidth(String[] titles, Sheet sheet) {
        for (int i = 0; i < titles.length; i++) {
            sheet.setColumnWidth(i, 20 * 256);
        }
    }

    private static CellStyle getHeaderCommonStyle(Workbook workbook) {
        CellStyle header = workbook.createCellStyle();
        Font font = workbook.createFont();
        font.setBold(Boolean.TRUE);
        font.setFontHeightInPoints((short) 14);
        font.setFontName("宋体");
        header.setFont(font);
        header.setBorderTop(BorderStyle.THIN);
        header.setBorderLeft(BorderStyle.THIN);
        header.setBorderBottom(BorderStyle.THIN);
        header.setBorderRight(BorderStyle.THIN);
        header.setAlignment(HorizontalAlignment.CENTER);
        header.setVerticalAlignment(VerticalAlignment.CENTER);
        header.setFillPattern(FillPatternType.SOLID_FOREGROUND);
//        header.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        return header;
    }

    private static CellStyle getBodyStyle(Workbook workbook) {
        CellStyle body = workbook.createCellStyle();
        Font font = workbook.createFont();
        font.setFontHeightInPoints((short) 12);
        font.setFontName("宋体");
        body.setFont(font);
        body.setWrapText(Boolean.TRUE);
        body.setBorderTop(BorderStyle.THIN);
        body.setBorderLeft(BorderStyle.THIN);
        body.setBorderBottom(BorderStyle.THIN);
        body.setBorderRight(BorderStyle.THIN);
        body.setAlignment(HorizontalAlignment.LEFT);
        body.setVerticalAlignment(VerticalAlignment.CENTER);
        body.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        body.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        return body;
    }

    /**
     * <p>@Description: object就是每一行的数据</p >
     * <p>@param [rowData, exportFieldArr]</p >
     * <p>@return java.lang.String[]</p >
     * <p>@throws </p >
     */
    public static String[] getValues(Object rowData, String[] exportFieldArr) {
        String[] values = new String[exportFieldArr.length];
        try {
            for (int i = 0; i < exportFieldArr.length; i++) {

                Field field = null;

                try {
                    field = rowData.getClass().getDeclaredField(exportFieldArr[i]);
                } catch (Exception e) {
                    field = rowData.getClass().getField(exportFieldArr[i]);
                }
                // 设置访问权限为true
                field.setAccessible(true);
                values[i] = setCellValue(field, rowData);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return values;
    }

    public static String setCellValue(Field field, Object data) {
        Class<?> fieldType = field.getType();
        String result = "";
        try {
            Method method = data.getClass().getMethod(getMethodNameByCamel("get", field.getName()));
            Object fieldValue = method.invoke(data);
            if (fieldType == String.class) {
                result = (method.invoke(data) == null ? null : method.invoke(data).toString());
            } else if (fieldType == Short.class) {
                result = returnStringFromNumber(fieldValue);
            } else if (fieldType == Integer.class) {
                result = returnStringFromNumber(fieldValue);
            } else if (fieldType == Long.class) {
                result = returnStringFromNumber(fieldValue);
            } else if (fieldType == Float.class) {
                result = returnStringFromNumber(fieldValue);
            } else if (fieldType == Double.class) {
                result = returnStringFromNumber(fieldValue);
            } else if (fieldType == BigDecimal.class) {
                result = returnStringFromNumber(fieldValue);
            } else if (fieldType == Boolean.class) {
                result = returnStringFromNumber(fieldValue);
            } else if (fieldType == LocalDate.class) {
                String pattern = "yyyy-MM-dd";
                LocalDate date = method.invoke(data) == null ? null : (LocalDate) method.invoke(data);
                if (date != null) {
                    result = (date.format(DateTimeFormatter.ofPattern(pattern)));
                }
            } else if (fieldType == LocalDateTime.class) {
                String pattern = "yyyy-MM-dd HH:mm:ss";
                LocalDateTime date = method.invoke(data) == null ? null : (LocalDateTime) method.invoke(data);
                if (date != null) {
                    result = (date.format(DateTimeFormatter.ofPattern(pattern)));
                }
            } else {
                result = (method.invoke(data) == null ? null : method.invoke(data).toString());
            }
            return result;
        } catch (NoSuchMethodException | IllegalAccessException | InvocationTargetException e) {
            e.printStackTrace();
        }
        return result;
    }

    // 将数字转换为 保留两位小数的字符串
    public static String returnStringFromNumber(Object data) {
        if (data == null || StrUtil.isBlank(data.toString())) {
            return "";
        }
        Double aDouble = Double.valueOf(data.toString());
        DecimalFormat decimalFormat = new DecimalFormat("0.00");
        String result = decimalFormat.format(aDouble);
        return result;
    }


    /**
     * <p>@Description: 拼接前缀以及方法名，驼峰形式</p >
     * <p>@param [prefix, fieldName]</p >
     * <p>@return java.lang.String</p >
     * <p>@throws </p >
     */
    private static String getMethodNameByCamel(String prefix, String fieldName) {
        StringBuilder builder = new StringBuilder()
                .append(prefix)
                .append(fieldName.substring(0, 1).toUpperCase())
                .append(fieldName.substring(1));
        return builder.toString();
    }
}

6.模板下载工具类：用于生成模板第一行示例数据

/**
 * @author luoliang
 * @description 导出模板工具类
 * @date 2023/10/11 14:07
 */
public class EzTemplateUtil {
    public static <T> List<Object> getTemplateExample(Class<T> objectClass) {
        List<Object> objects = new ArrayList<>();
        Field[] declaredFields = objectClass.getDeclaredFields();
        T objectInstance = null;
        try {
            objectInstance = objectClass.newInstance();
            for (Field declaredField : declaredFields) {
                declaredField.setAccessible(true);
                EzFiled dynamicExportAnnotation = declaredField.getAnnotation(EzFiled.class);
                String example = dynamicExportAnnotation.example();
                String fieldName = declaredField.getName();
                String setterMethodName = "set" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
                Method setterMethod = objectClass.getMethod(setterMethodName, String.class);
                setterMethod.invoke(objectInstance, example);

            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        objects.add(objectInstance);
        return objects;
    }
}

使用：

定义导入导出实体类如下图

/**
 * @author luoliang
 * @description 导出对象信息类
 * @date 2023/8/8 10:26
 */
@Data
@AllArgsConstructor
@NoArgsConstructor
public class CodeTableExportVO implements Serializable {
    @EzFiled(sort = 1, name = "专业代码", example = "专业代码 如100000")
    private String professionalCode;
    @EzFiled(sort = 0, name = "专业名称", example = "专业名称 如哲学类")
    private String professionalName;
    @EzFiled(sort = 2, name = "创建时间", example = "专业创建的年份 如2000")
    private String createTime;
}

当需要返回给前端可选项时

List<EzFiledRo> ezFiledRos = EzFiledUtil.exportPageListDynamicFieldList(CodeTableExportVO.class);

输出：

当需要导出数据时假设文件名为luo

EzExportUtil.exportByTitleAndData("需要导出的数据列表","前端传入的选择数组","luo","sheetName",CodeTableExportVO.class);

输入：

输出：

会按照定义的排序输出列

当需要下载模板时

EzExportUtil.exportByTitleAndData(EzFiledUtil.exportPageListDynamicFieldList(CodeTableExportVO.class), EzTemplateUtil.getTemplateExample(CodeTableExportVO.class), "码表导出模板","sheetName", CodeTableExportVO.class);

输出：

扩展：

1.使用该自定义注解（EzFiled）实现导入为自定对象列表

实现Excel动态导入且导入为自定对象列表_罗_亮的博客-CSDN博客

2.实现记录导入导出日志以及记录错误数据（待更新）

罗_亮

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
实现Excel动态字段导出数据且实现模板提示内容下载

需求为要将可展示字段于前端展示供用户选择后传入后端，后端依据用户选择字段进行导出。3.增加用于前端展示的工具类，工具类依据EzFiledRo 进行展示。1.先自定义一个注解用于标识需要导出类的字段。2.定义一个用于前端展示、用户选择的基类。当需要导出数据时假设文件名为luo。定义导入导出工具类如下图。当需要返回给前端可选项时。
复制链接

扫一扫