前言: 本文旨在记录项目中多次出现的需求，实现Excel动态列及对应数据的导出。为了方便处理类似问题，在此记录

• 实现该功能基于easypoi,首先要在项目中导入

 <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-base</artifactId>
            <version>3.2.0</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-web</artifactId>
            <version>3.2.0</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-annotation</artifactId>
            <version>3.2.0</version>
        </dependency>

• 介绍如何实现动态列导出之前，先看下前端实现页面展示动态列所需的格式

  {
        "tableColumn": [
            {
                "property": "date",
                "label": "日期"
            },
            {
                "property": "shift",
                "label": "班次"
            },
            {
                "property": "L1",
                "label": "L1"
            },
            {
                "property": "L2",
                "label": "L2"
            },
            {
                "property": "L3",
                "label": "L3"
            },
            {
                "property": "汇总",
                "label": "汇总"
            }
        ],
        "tableData": [
            {
                "date": "2023-12-06",
                "shift": "白班",
                "L1": "87.61%",
                "L2": "86.78%",
                "L3": "86.95%",
                "汇总": "84.15%"
            },
            {
                "date": "2023-12-06",
                "shift": "夜班",
                "L1": "85.58%",
                "L2": "86.77%",
                "L3": "88.76%",
                "汇总": "82.81%"
            }
        ]
    }

• 使用easypoi的 ExcelExportUtil.exportExcel(ExportParams entity, List entityList, Collection<?> dataSet)方法。
  参考文档地址：
  easypoi
• 基于上述json的java代码如下

        DynamicDataVO dynamicDataVO = robotWholeRateRecordService.getList(queryVO);
        //构造ExcelExportEntity list
        List<ExcelExportEntity> beanList = new ArrayList<ExcelExportEntity>();
        for (Map<String, Object> map : dynamicDataVO.getTableColumn()) {
            //构造对象等同于@Excel
            beanList.add(new ExcelExportEntity(map.get("label").toString(), map.get("property").toString()));
        }
        String fileName = "测试生成动态列.xls";
        Sting excelUrl = ExcelUtils.exportDynamicRow(new ExportParams("这是标题名称","这是sheet名称"), beanList, dynamicDataVO.getTableData(),fileName)

• 其中 ExcelUtils.exportDynamicRow是一个封装的方法,生成excel后上传至minio，并返回excelUrl地址

/**
     * 生成动态列Excel地址并上传至minio
     * @param exportParams
     * @param beanList
     * @param dataSet
     * @param fileName
     * @return
     * @throws Exception
     */
    public static String exportDynamicRow(ExportParams exportParams, List<ExcelExportEntity> beanList, Collection<?> dataSet, String fileName) throws Exception {

        File file = new File(ReviewConfig.getProfile()+fileName);
        OutputStream outputStream = null;
        Workbook workbook = null;
        try {
            outputStream= new FileOutputStream(file);
            workbook = ExcelExportUtil.exportExcel(exportParams, beanList, dataSet);
            workbook.write(outputStream);
        } catch (Exception e) {
            log.error(e.getMessage());
        } finally {
            try {
                if (outputStream != null) {
                    outputStream.close();
                }
            } catch (IOException e) {
                log.error(e.getMessage());
            }
        }
        return MinioUtils.upload2MinIO(new File(ReviewConfig.getProfile()+fileName));

 public static String upload2MinIO(File file) throws Exception {
        //本地生成成功后 上传文件至minion
        MinioUtils.putObject(SpringUtils.getBean(MinioConfig.class).getBucketName(),file.getName(),new FileInputStream(file));
        return SpringUtils.getBean(MinioConfig.class).getEndpoint()+"/"+SpringUtils.getBean(MinioConfig.class).getBucketName()+"/"+file.getName();
    }

• 上述方法exportDynamicRow可以拆分生成excel和上传minio2个步骤，具体实现根据实际情况处理

本文由 权鸣 创作，采用 知识共享署名4.0 国际许可协议进行许可。
本站文章除注明转载/出处外，均为本站原创或翻译，转载前请务必署名。