easy-poi动态列导出注解方式

一杯美式toDay

已于 2024-08-29 09:44:35 修改

阅读量135

点赞数 4

文章标签： java

于 2024-08-29 09:34:49 首次发布

本文链接：https://blog.csdn.net/weixin_43345808/article/details/140203222

版权

导出效果图

1.创建实体类

package com.pdm.srm.pojo.order.dto;

import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.annotation.ExcelCollection;
import lombok.Data;

import java.io.Serializable;
import java.math.BigDecimal;
import java.util.List;

/**
 * @Author: 
 * @version: V1.0
 * @Date: 2024-04-24 16:53
 * @Package: com.pdm.srm.pojo.order.dto
 */
@Data
public class OrderProgressExportDTO {
    @Excel(name = "序号", width = 5)
    private Integer itemNo;

    @Excel(name = "采购单号", width = 20)
    private String purNo;

    @Excel(name = "项次", width = 5)
    private Integer itemSeq;

    @Excel(name = "样品单号", width = 20)
    private String srfNo;

    @Excel(name = "型体名称", width = 20)
    private String modelName;

    @Excel(name = "配色", width = 20)
    private String articNo;

    @Excel(name = "季节", width = 8)
    private String seasonName;

    @Excel(name = "备注", width = 20)
    private String remark;

    @Excel(name = "付款情况", width = 15)
    private String payableAuditTimeStr;

    @Excel(name = "采购员", width = 8)
    private String purName;

    @Excel(name = "来源单据", width = 20)
    private String refTypeStr;

    /**
     * size
     */
    @ExcelCollection(name = "size")
    private List<OrderProgressExportDTO.DynamicColumnData> dynamicColumnDataList;
    @Data
    public static class DynamicColumnData implements Serializable {
        /**
         * sizeSeq（动态标题名称,此处 name = "sizeSeq"可以随便填，以方法调用时传入的为准）
         */
        @Excel(name = "sizeSeq", width = 10)
        private String sizeSeq;

        /**
         * size（动态标题内容,此处 name = "size"可以随便填，以方法调用时传入的为准）
         */
        @Excel(name = "size", width = 10)
        private String size="";
    }
}

2.创建导出工具类ExcelPoiUtils 该类复制即可用自行添加依赖

package com.pdm.common.utils;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.annotation.ExcelCollection;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity;
import cn.hutool.core.annotation.AnnotationUtil;
import cn.hutool.core.util.ReflectUtil;
import com.alibaba.fastjson.JSONObject;
import com.baomidou.mybatisplus.core.toolkit.CollectionUtils;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.beanutils.BeanUtils;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.BufferedOutputStream;
import java.io.UnsupportedEncodingException;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Optional;
import java.util.concurrent.CompletableFuture;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.concurrent.TimeUnit;
import java.util.stream.Collectors;

/**
 * @Author: 一杯美式
 * @version: V1.0
 * @Date: 2024-04-23 10:38
 * @Package: com.victory.jfe.pdm.srm.config
 */
@Slf4j
@Service
public class ExcelPoiUtils {
    /**
     * 动态列导出
     * @param response    响应
     * @param fileName    文件名
     * @param title       标题
     * @param sheetName   sheet名称
     * @param dataList    导出数据
     * @param headerName  动态列标题
     * @param headerValue 动态列值
     * @param <T>
     * @throws Exception
     */
    public static <T> void exportExcel(HttpServletResponse response, String fileName, String title, String sheetName,
                                       List<T> dataList, String headerName, String headerValue) throws Exception {
        if (CollectionUtils.isNotEmpty(dataList)) {
            T dataInstance = dataList.get(0);
            List<ExcelExportEntity> entityList = buildExcelExportEntityList(dataInstance, headerName, "");
            List<Object> objList = convertDataListToObjList(dataList, headerName,headerValue);
            downloadExcelEntityDynamic(response, entityList, objList, fileName, title, sheetName);
        }
    }

    public static <T> void exportExcel(HttpServletResponse response, String fileName, String title, String sheetName,
                                       List<T> dataList, String headerName, String headerValue, String ignoreCol) throws Exception {
        if (CollectionUtils.isNotEmpty(dataList)) {
            T dataInstance = dataList.get(0);
            List<ExcelExportEntity> entityList = buildExcelExportEntityList(dataInstance, headerName, ignoreCol);
            List<Object> objList = convertDataListToObjList(dataList, headerName,headerValue);
            downloadExcelEntityDynamic(response, entityList, objList, fileName, title, sheetName);
        }
    }

    /**
     * 构建Excel导出实体列表
     *
     * @param t          取数据集第一条数据 做实体列表构建
     * @param headerName 动态列标题
     * @param <T>        数据类型
     * @return Excel导出实体列表
     * @throws IllegalAccessException 如果无法访问字段
     */
    private static <T> List<ExcelExportEntity> buildExcelExportEntityList(T t, String headerName, String ignoreCol) throws IllegalAccessException {
        List<ExcelExportEntity> entityList = new ArrayList<>();
        Field[] fields = t.getClass().getDeclaredFields();
        int index = 0;

        for (Field field : fields) {
            field.setAccessible(true);
            Optional<Excel> excelOpt = Optional.ofNullable(field.getAnnotation(Excel.class));
            Optional<ExcelCollection> excelCollectionOpt = Optional.ofNullable(field.getAnnotation(ExcelCollection.class));

            if (excelOpt.isPresent()) {
                // 处理固定导出列
                if (ignoreCol.contains(field.getName())) {
                    index++;
                } else {
                    index = handleFixedExportColumn(entityList, field, excelOpt.get(), index);
                }
            } else if (excelCollectionOpt.isPresent() && List.class.isAssignableFrom(field.getType())) {
                // 处理自定义导出列
                index = handleCustomExportColumn(t, entityList, field, headerName, index);
            }
        }

        return entityList;
    }

    /**
     * 处理固定导出列
     *
     * @param entityList 实体列表
     * @param field      字段
     * @param excel      Excel注解
     * @param index      索引
     * @return 更新后的索引
     */
    private static int handleFixedExportColumn(List<ExcelExportEntity> entityList, Field field, Excel excel, int index) {
        Object name = AnnotationUtil.getAnnotationValue(field, Excel.class, "name");
        ExcelExportEntity entity = createExcelExportEntity(field, name.toString(), field.getName(), index);
        entityList.add(entity);
        return index + 1;
    }

    /**
     * 处理自定义导出列
     *
     * @param t          数据对象
     * @param entityList 实体列表
     * @param field      字段
     * @param headerName 动态列标题
     * @param index      索引
     * @param <T>        数据类型
     * @return 更新后的索引
     * @throws IllegalAccessException 如果无法访问字段
     */
    private static <T> int handleCustomExportColumn(T t, List<ExcelExportEntity> entityList, Field field, String headerName, int index) throws IllegalAccessException {
        List<?> dynamicColl = (List<?>) field.get(t);
        for (Object arr : dynamicColl) {
            Field[] typeFields = arr.getClass().getDeclaredFields();
            for (Field typeField : typeFields) {
                typeField.setAccessible(true);
                Excel excelItem = typeField.getAnnotation(Excel.class);
                if (excelItem != null && headerName.equals(typeField.getName())) {
                    Object value = typeField.get(arr);
                    ExcelExportEntity entity = createExcelExportEntity(typeField, value.toString(), value.toString(), index);
                    entityList.add(entity);
                    index++;
                }
            }
        }
        return index;
    }

    /**
     * 将数据对象列表转换为对象列表，通过异步处理每个项。
     *
     * @param dataList 需要处理的数据对象列表。
     * @param headerName 用于从对象中提取特定值的标题名称。
     * @param headerValue 用于从对象中提取特定值的标题值。
     * @param <T> 数据对象列表中的对象类型。
     * @return 表示处理过的数据对象的映射列表。
     */
    public static <T> List<Object> convertDataListToObjList(List<T> dataList, String headerName, String headerValue) {
        // 创建一个固定大小的线程池 =处理器数量
        ExecutorService executorService = Executors.newFixedThreadPool(Runtime.getRuntime().availableProcessors());
        long start = System.currentTimeMillis();
        List<CompletableFuture<Object>> futures = new ArrayList<>();
        // 提交每个数据对象进行异步处理
        for (T data : dataList) {
            futures.add(CompletableFuture.supplyAsync(() -> processData(data, headerName, headerValue), executorService));
        }
        // 收集处理结果
        List<Object> objList = futures.stream()
                .map(CompletableFuture::join)
                .collect(Collectors.toList());
        // 关闭线程池
        executorService.shutdown();
        try {
            if (!executorService.awaitTermination(2, TimeUnit.MINUTES)) {
                executorService.shutdownNow();
            }
        } catch (InterruptedException e) {
            executorService.shutdownNow();
            Thread.currentThread().interrupt();
        }

        log.info("数据处理时间:" + (System.currentTimeMillis() - start) + "ms");
        return objList;
    }

    /**
     * 处理单个数据对象以提取相关字段及其值。
     *
     * @param data 要处理的数据对象。
     * @param headerName 用于从对象中提取特定值的标题名称。
     * @param headerValue 用于从对象中提取特定值的标题值。
     * @param <T> 数据对象的类型。
     * @return 表示数据对象的映射。
     */
    private static <T> Object processData(T data, String headerName, String headerValue) {
        Map<String, Object> dataMap = new HashMap<>();
        Field[] fields = ReflectUtil.getFields(data.getClass());

        for (Field field : fields) {
            field.setAccessible(true);
            try {
                // 将字段名和值放入dataMap中
                dataMap.put(field.getName(), field.get(data));
                ExcelCollection excelCollection = field.getAnnotation(ExcelCollection.class);

                // 如果字段是ExcelCollection并且是List类型，进一步处理
                if (excelCollection != null && field.getType().getName().equals(List.class.getName())) {
                    List<?> dynamicColl = (List<?>) field.get(data);

                    for (Object arr : dynamicColl) {
                        String key = null;
                        String val = null;
                        Field[] typeFields = arr.getClass().getDeclaredFields();

                        for (Field typeField : typeFields) {
                            typeField.setAccessible(true);
                            Excel excelItem = typeField.getAnnotation(Excel.class);
                            // 根据注解提取key和value
                            if (excelItem != null) {
                                if (headerName.equals(typeField.getName())) {
                                    key = String.valueOf(typeField.get(arr));
                                } else if (headerValue.equals(typeField.getName())) {
                                    val = String.valueOf(typeField.get(arr));
                                }
                            }
                        }
                        dataMap.put(key, val);
                    }
                }
            } catch (IllegalAccessException e) {
                log.error("无法访问字段值：", e.getMessage());
                return null;
            }
        }

        return dataMap;
    }


    /**
     * 动态生成并下载Excel文件。
     *
     * @param response HttpServletResponse对象，用于发送响应。
     * @param entityList Excel导出实体列表。
     * @param list 数据列表。
     * @param fileName 文件名。
     * @param title 标题。
     * @param sheetName 工作表名称。
     * @throws Exception 可能抛出的异常。
     */
    public static void downloadExcelEntityDynamic(HttpServletResponse response, List<ExcelExportEntity> entityList,
                                                  List<Object> list, String fileName, String title,
                                                  String sheetName) throws Exception {
        setResponseHeadersForDownload(response, fileName);
        ExportParams exportParams = StringUtils.hasText(title) ? new ExportParams(title, sheetName) : new ExportParams();
        if (!StringUtils.hasText(title)) {
            exportParams.setSheetName(sheetName);
        }

        int pageSize = 12000;
        int totalPages = (list.size() + pageSize - 1) / pageSize;
        Workbook workbook = ExcelExportUtil.exportBigExcel(exportParams, entityList, (obj, page) -> {
            if (((int) obj) < page) {
                return null;
            }
            log.info("当前查询第{}页数据", page);
            int fromIndex = (page - 1) * pageSize;
            int toIndex = Math.min(page * pageSize, list.size());
            return list.subList(fromIndex, toIndex);
        }, totalPages);

        try (ServletOutputStream output = response.getOutputStream();
             BufferedOutputStream bufferedOutPut = new BufferedOutputStream(output)) {
            workbook.write(bufferedOutPut);
            bufferedOutPut.flush();
        }
    }

    private static void setResponseHeadersForDownload1(HttpServletResponse response, String fileName) throws UnsupportedEncodingException {
        response.setCharacterEncoding("UTF-8");
        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8") + ".xls");
    }


    public static void setResponseHeadersForDownload(HttpServletResponse response, String fileName) throws UnsupportedEncodingException {
        response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName + ".xlsx", "utf-8"));
        response.setContentType("application/octet-stream;charset=utf-8");
    }

    /**
     * 将@Excel或者@ExcelCollection修饰的字段转为ExcelExportEntity
     */
    private static ExcelExportEntity createExcelExportEntity(Field typeField, String name, String key, int index) {
        Map<String, Object> annotationValueMap = AnnotationUtil.getAnnotationValueMap(typeField, Excel.class);
        ExcelExportEntity entity = JSONObject.parseObject(JSONObject.toJSONBytes(annotationValueMap), ExcelExportEntity.class);
        // 字段名和@Excel的name一致，视为动态表头列
        entity.setName(name);
        entity.setKey(key);
        entity.setOrderNum(index);
        return entity;
    }


    private static ExcelExportEntity createExcelExportEntity1(Field typeField, String name, String key, int index) {
        ExcelExportEntity entity = new ExcelExportEntity();
        // 设置基本信息
        entity.setName(name);
        entity.setKey(key);
        entity.setOrderNum(index);
        // 设置注解中的属性
        Excel excel = typeField.getAnnotation(Excel.class);
        if (excel != null) {
            try {
                BeanUtils.copyProperties(entity, excel); // 复制注解中的属性到ExcelExportEntity对象
            } catch (Exception e) {
                log.error(e.getMessage());
                throw new RuntimeException("无法从Excel注解复制属性：", e);
            }
        }

        return entity;
    }

}

3.调用EcelPoiUtils.exportExcel方法进行导出 (根据自己的业务填充数据即可)

 @PostMapping(value = "/exportExcel")
    @ApiOperation(value = "采购进度追踪导出,",
            notes = "采购进度追踪导出后;示例: {\"pageNo\":1,\"pageSize\":10,\"sortOrder\":\"\",\"sortField\":\"\",\"params\":{\"key\":\"value\"}} ")
    @MethodDefine(title = "条件查询接口", path = "/getProcurementProgress", method = HttpMethodConstants.POST,
            params = {@ParamDefine(title = "查询条件", varName = "queryData"),
            })
    public void exportExcel(@RequestBody VQueryData<PoOrderMQO> queryData, HttpServletResponse response) throws Exception{
        if (ObjectUtils.isEmpty(queryData)) {
            return;
        }
        queryData.setPageNo(1);
        queryData.setPageSize(-1);
        PoOrderMQO qo = queryData.getParams();
        qo.setFactId(getCurrentFactoryId());
        queryData.setParams(qo);
        IPage<PoOrderMVO> page = poOrderMService.selectProcurementProgress(queryData);
        List<PoOrderMVO> records = page.getRecords();
        if (CollectionUtils.isEmpty(records)){
            return;
        }
        List<String> sizeNoList = this.sizeNoListOrderByAsc(records);
        List<OrderProgressExportDTO> exportDTOList = new ArrayList<>();
        //填充数据
        for (PoOrderMVO vo:records){
            OrderProgressExportDTO progressExportDTO = this.setOrderProgressExportDTO(vo);
            List<OrderProgressExportDTO.DynamicColumnData> dynamicColumnData = new ArrayList<>();
            progressExportDTO.setPurNo(vo.getPurNo());
            Map<String, Object> stringObjectMap = com.redxun.common.tool.BeanUtil.convertFieldToMap(vo);
            stringObjectMap.put("name",vo.getPurNo());
            for (String size : sizeNoList) {
                OrderProgressExportDTO.DynamicColumnData columnData = new OrderProgressExportDTO.DynamicColumnData();
                if(CollectionUtils.isNotEmpty(vo.getPoOrderSizeVOList())){
                    Map<String, PoOrderSizeVO> poOrderSizeVOMap = vo.getPoOrderSizeVOList().stream().collect(Collectors.toMap(PoOrderSizeVO::getSizeNo, Function.identity(), (key1, key2) -> key1));
                    PoOrderSizeVO orderSizeVO = poOrderSizeVOMap.get(size);
                    if(ObjectUtils.isNotEmpty(orderSizeVO)){
                        columnData.setSizeSeq(size);
                        columnData.setSize(orderSizeVO.getLQty()+" / "+orderSizeVO.getRQty());
                    }else{
                        columnData.setSizeSeq(size);
                        columnData.setSize("");
                    }
                }else{
                    columnData.setSizeSeq(size);
                    columnData.setSize("");
                }
                dynamicColumnData.add(columnData);
            }
            progressExportDTO.setDynamicColumnDataList(dynamicColumnData);
            exportDTOList.add(progressExportDTO);
        }
        // 动态标头名称字段
        final String headerName = "sizeSeq";
        // 动态标头值字段
        final String headerValue = "size";
        try {
            String time = LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyy_MM_dd_HH_mm_ss"));
            ExcelPoiUtils.exportExcel(response,  "采购进度_"+time, "采购材料进度追踪表",
                    "sheet", exportDTOList, headerName, headerValue);
        } catch (Exception e) {
            log.error("导出错误，", e.getMessage());
            throw new RuntimeException(e.getMessage());
        }
    }