easypoi生成动态excel

一.使用ExcelExportEntity 方式

1.设置数据库字段表，用来存储excel表头

@Data
public class DynamicPo {
    //展示列名
    private String label;
    //key值
    private String prop;
    //是否可以编辑
    private Boolean disable;
    //是否展示
    private Boolean show;
    //排序
    private Integer sortNo;
}

2.根据数据库表的数据，获取动态列

 /**
     * @Description //TODO 设置动态列
     * @Param [roomShow]
     * @return java.util.List<cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity>
     * @Date 2022/11/23 15:14
     * @Author jiangb
     **/
    public List<ExcelExportEntity> dynaCol(List<DynamicPo> dynamicPoList) {
        List<ExcelExportEntity> colList = new ArrayList<>();
        dynamicPoList.forEach(info->{
            if(info.getShow()){
                colList.add(new ExcelExportEntity(info.getLabel(), info.getProp()));
            }
        });
        return colList;
    }

3.获取每个字段需要的数据，list是需要导出的数据数组

/**
     * @Description //TODO 根据需要获取数据
     * @Param [roomShow, list]
     * @return java.util.List<java.util.Map<java.lang.String,java.lang.Object>>
     * @Date 2022/11/24 15:19
     * @Author jiangb
     **/
    public List<Map<String,Object>> getListMap(List<DynamicPo> dynamicPoList,List<T> list){
        //获取需要导出的字段和排序
        Map<String,DynamicPo> courtyardWarn = dynamicPoList.stream().collect(Collectors.toMap(DynamicPo::getProp, a -> a, (k1, k2) -> k1));
        List<Map<String,Object>> mapList=new ArrayList<>();
        //迭代数据，获取导出字段所需要的数据和排序
        list.forEach(info->{
            Map<String,Object>map=new HashMap<>();
            //获取所有树形
            Field[] fields = info.getClass().getDeclaredFields();
            for (Field field : fields) {
                field.setAccessible(true);
                try {
                    //获取配置字段的树形，show为false的字段不需要赋值
                    if(courtyardWarn.get(field.getName())!=null&&courtyardWarn.get(field.getName()).getShow()){
                        map.put(field.getName(), field.get(info));
                    }
                } catch (IllegalAccessException e) {
                    throw new RuntimeException(e);
                }
            }
            mapList.add(map);
        });
        return mapList;
    }

4.导出

    /**
     * @Description //TODO 导出动态excel
     * @Param [dynamicPoList, list, title, fileName, response]
     * @return void
     * @Date 2022/11/24 15:23
     * @Author jiangb
     **/
    public void exportDynamicExcel(List<DynamicPo> dynamicPoList, List<T>list, String title, String fileName, HttpServletResponse response){
        ExcelDynamicUtils excelDynamicUtils=new ExcelDynamicUtils();
        List<ExcelExportEntity> beanList = excelDynamicUtils.dynaCol(dynamicPoList);
        List<Map<String,Object>>maplits=excelDynamicUtils.getListMap(dynamicPoList,list);
        HSSFWorkbook workbook = (HSSFWorkbook) ExcelExportUtil.exportExcel(new ExportParams(title,"1"), beanList, maplits);
        EasyPoiUtils.downLoadExcel(fileName, response, workbook);
    }

二.使用动态注解的方式

1.设置动态注解

 /**
     * @Description //TODO 设置动态注解
     * @Param [columnName, target, sortNo, t]
     * @return void
     * @Date 2022/11/24 16:44
     * @Author jiangb
     **/
    public void dynamicColumn(String columnName, Boolean target,String sortNo,T t) throws Exception {
        // 获取对象树形
        Field field = t.getClass().getDeclaredField(columnName);
        // 获取excel注解对象
        Excel excelAnnon = field.getAnnotation(Excel.class);
        // 获取代理
        InvocationHandler invocationHandler = Proxy.getInvocationHandler(excelAnnon);
        Field excelField = invocationHandler.getClass().getDeclaredField("memberValues");
        // 打开权限
        excelField.setAccessible(true);
        Map memberValues = (Map) excelField.get(invocationHandler);
        // 是否隐藏
        memberValues.put("isColumnHidden", target);
        //排序值
        memberValues.put("orderNum", sortNo);
    }

2.给每个对象设置注解

/**
     * @Description //TODO 动态列设置
     * @Param [list, roomShow]
     * @return void
     * @Date 2022/11/23 15:11
     * @Author jiangb
     **/
    public void dynamicExcel(List<T> list, List<DynamicPo> dynamicPoList) {
        if (CollectionUtil.isNotEmpty(list)) {
            for (T vo : list) {
                ExcelDynamicUtils easyPoiUtil = new ExcelDynamicUtils();
                try {
                    for(int i=0;i<dynamicPoList.size();i++){
                        DynamicPo dynamicPo=dynamicPoList.get(i);
                        //在页面上true代表显示，但是在注解中，true代表隐藏
                        easyPoiUtil.dynamicColumn(dynamicPo.getProp(), !dynamicPo.getShow().equals(true),dynamicPo.getSortNo()+"",vo);
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
    }

3.导出

        ExcelDynamicUtils.getInstance().dynamicExcel(list,dynamicPoList);
        EasyPoiUtils.exportExcel(list, title, "1", User.class, fileName, response);

注：第二种方式所有字段都会导出，设置隐藏的列会在EXCEL中隐藏，但是操作excel还是能让隐藏的列显示，第一种方式隐藏的列不会导出