动态列导出（easyPOI）

package cn.netinnet.audit.mbg;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity;
import com.alibaba.fastjson.JSONObject;
import org.apache.poi.ss.usermodel.Workbook;
import org.junit.jupiter.api.Test;

import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

/**
 * @Description:
 * @Author Chenzb
 * @Date 2022/6/8 10:28
 * @Version 1.0
 */
public class excelTest {
    private static List<ExcelExportEntity> dynamicNewAddExcel(Map<String, PlatformStatisParamRespData> paramInfo) {
        //表头的集合，用于添加表头
        List<ExcelExportEntity> entityList = new ArrayList<>();
        //参数信息--[用于动态拼接列头]
        final Iterator<String> iterator = paramInfo.keySet().iterator();
        while (iterator.hasNext()) {
            final String paramKeyStr = iterator.next();
            final String paramNameStr = paramInfo.get(paramKeyStr).getDataName();
            //列头由参数汉字名称,参数key为列key
            entityList.add(new ExcelExportEntity(paramNameStr, paramKeyStr, 30));
        }
        return entityList;
    }



    private static List<Map<String, Object>> dynamicListDataByKey(PlatformIncomeRespDTO statisData) {
        //参数类型
        final Set<String> statisParamKey = statisData.getParamInfo().keySet();
        List<List<BiPlatformStatisRespDTO>> data = statisData.getStatisData();
        //最终的数据
        List<Map<String, Object>> datas = new ArrayList<>();
        for (int i = 0; i < data.size(); i++) {
                Map<String, Object> hashMap = new LinkedHashMap<>(10);
            List<BiPlatformStatisRespDTO> nums = data.get(i);
                //参数的验证
                for (String paramKey : statisParamKey) {
                    for (BiPlatformStatisRespDTO paramData : nums) {
                        if (paramKey.equals(paramData.getParamKey())) {
                            hashMap.put(paramData.getParamKey(), paramData.getValue());
                        }
                    }
                }
                datas.add(hashMap);
        }
        return datas;
    }

    @Test
    public void Administrator_84_20191226095523() throws IOException {
        System.out.println("欢迎使用单元测试方法【Administrator_84()_20191226095523】");
        System.out.println("此方法测试描述：【】");
//        //拼装第一个数据---------------------------------------------------------------------
//        final PlatformIncomeRespDTO platformIncomeRespDTO1 = new PlatformIncomeRespDTO();
//        //拼装时间维度
//        //拼装头信息
//        Map<String, PlatformStatisParamRespData> paramInfo1=new HashMap<>();
//        paramInfo1.put("userCount", new PlatformStatisParamRespData("用户数","userCount"));
//        paramInfo1.put("friendsCount", new PlatformStatisParamRespData("好友数","friendsCount"));
//        platformIncomeRespDTO1.setParamInfo(paramInfo1);
//        //拼装数据
//        final ArrayList<List<BiPlatformStatisRespDTO>> data1 = Lists.newArrayList();
//        data1.add(Lists.newArrayList(new BiPlatformStatisRespDTO("userCount","100"),new BiPlatformStatisRespDTO("friendsCount","200")));
//        platformIncomeRespDTO1.setStatisData(data1);
//
//
//        //拼装第二个数据---------------------------------------------------------------------
//        final PlatformIncomeRespDTO platformIncomeRespDTO2 = new PlatformIncomeRespDTO();
//        //拼装时间维度
//        //拼装头信息
//        Map<String, PlatformStatisParamRespData> paramInfo2=new HashMap<>();
//        paramInfo2.put("userCount", new PlatformStatisParamRespData("用户数","userCount"));
//        paramInfo2.put("friendsCount", new PlatformStatisParamRespData("好友数","friendsCount"));
//        platformIncomeRespDTO2.setParamInfo(paramInfo2);
//
//        //拼装数据
//        final ArrayList<List<BiPlatformStatisRespDTO>> data2 = Lists.newArrayList();
//        data2.add(Lists.newArrayList(new BiPlatformStatisRespDTO("userCount","500"),new BiPlatformStatisRespDTO("friendsCount","600")));
//        platformIncomeRespDTO2.setStatisData(data2);
//
//
//
//        // 第三组
//        //拼装第二个数据---------------------------------------------------------------------
//        final PlatformIncomeRespDTO platformIncomeRespDTO3 = new PlatformIncomeRespDTO();
//        //拼装时间维度
//        //拼装头信息
//        Map<String, PlatformStatisParamRespData> paramInfo3=new HashMap<>();
//        paramInfo3.put("userCount", new PlatformStatisParamRespData("用户数","userCount"));
//        paramInfo3.put("friendsCount", new PlatformStatisParamRespData("好友数","friendsCount"));
//        platformIncomeRespDTO3.setParamInfo(paramInfo3);
//
//        //拼装数据
//        final ArrayList<List<BiPlatformStatisRespDTO>> data3 = Lists.newArrayList();
//        data3.add(Lists.newArrayList(new BiPlatformStatisRespDTO("userCount","1500"),new BiPlatformStatisRespDTO("friendsCount","1600")));
//        platformIncomeRespDTO3.setStatisData(data3);
//
//
//        final ArrayList<PlatformIncomeRespDTO> platformIncomeRespDTOS = Lists.newArrayList(platformIncomeRespDTO1, platformIncomeRespDTO2,platformIncomeRespDTO3);
//        System.out.println(JSONObject.toJSONString(platformIncomeRespDTOS));
        String json = "\n" +
                "    {\n" +
                "        \"paramInfo\": {\n" +
                "            \"userCount\": {\n" +
                "                \"dataName\": \"用户数\", \n" +
                "                \"dateKey\": \"userCount\"\n" +
                "            }, \n" +
                "            \"friendsCount\": {\n" +
                "                \"dataName\": \"好友数\", \n" +
                "                \"dateKey\": \"friendsCount\"\n" +
                "            }, \n" +
                "            \"phoneNo\": {\n" +
                "                \"dataName\": \"电话号码\", \n" +
                "                \"dateKey\": \"phoneNo\"\n" +
                "            }\n" +
                "        }, \n" +
                "        \"statisData\": [\n" +
                "            [\n" +
                "                {\n" +
                "                    \"paramKey\": \"userCount\", \n" +
                "                    \"value\": \"100\"\n" +
                "                }, \n" +
                "                {\n" +
                "                    \"paramKey\": \"friendsCount\", \n" +
                "                    \"value\": \"200\"\n" +
                "                }, \n" +
                "                {\n" +
                "                    \"paramKey\": \"phoneNo\", \n" +
                "                    \"value\": \"11xx0\"\n" +
                "                }\n" +
                "            ], \n" +
                "            [\n" +
                "                {\n" +
                "                    \"paramKey\": \"userCount\", \n" +
                "                    \"value\": \"300\"\n" +
                "                }, \n" +
                "                {\n" +
                "                    \"paramKey\": \"friendsCount\", \n" +
                "                    \"value\": \"400\"\n" +
                "                }, \n" +
                "                {\n" +
                "                    \"paramKey\": \"phoneNo\", \n" +
                "                    \"value\": \"13xx0\"\n" +
                "                }\n" +
                "            ],\n" +
                "            [\n" +
                "                {\n" +
                "                    \"paramKey\": \"userCount\", \n" +
                "                    \"value\": \"800\"\n" +
                "                }, \n" +
                "                {\n" +
                "                    \"paramKey\": \"friendsCount\", \n" +
                "                    \"value\": \"900\"\n" +
                "                }, \n" +
                "                {\n" +
                "                    \"paramKey\": \"phoneNo\", \n" +
                "                    \"value\": \"11xxx0\"\n" +
                "                }\n" +
                "            ], \n" +
                "            [\n" +
                "                {\n" +
                "                    \"paramKey\": \"userCount\", \n" +
                "                    \"value\": \"1300\"\n" +
                "                }, \n" +
                "                {\n" +
                "                    \"paramKey\": \"friendsCount\", \n" +
                "                    \"value\": \"1400\"\n" +
                "                }, \n" +
                "                {\n" +
                "                    \"paramKey\": \"phoneNo\", \n" +
                "                    \"value\": \"xxx110\"\n" +
                "                }\n" +
                "            ]\n" +
                "        ]\n" +
                "    }\n" +
                "\n";

        PlatformIncomeRespDTO platformIncomeRespDTOS1 = JSONObject.parseObject(json, PlatformIncomeRespDTO.class);
        Map<String, PlatformStatisParamRespData> paramInfo = platformIncomeRespDTOS1.getParamInfo();
        //拼装列头
        List<ExcelExportEntity> colList = dynamicNewAddExcel(paramInfo);
        //数据拼装
        List<Map<String, Object>> list = dynamicListDataByKey(platformIncomeRespDTOS1);

        //文件名称
        final Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams(), colList, list);

        //此功能与【拼装列头】中的 platformXh.setMergeVertical(true);功能效果一样,可直接使用 platformXh.setMergeVertical(true);进行纵向合并
        //动态合并纵列[mergeMap key列索引(从0开始),value依赖的列,没有传空,startRow 开始行(从零开始)]
        //Map<Integer, int[]> mer = new HashMap<>();
        //mer.put(0, new int[]{});
        //PoiMergeCellUtil.mergeCells(workbook.getSheetAt(0), mer, 1);

        final FileOutputStream fileOutputStream = new FileOutputStream("E:\\cs\\1.xls");
        //导出excel
        downLoadExcel(null, fileOutputStream, workbook);
    }

    public static void downLoadExcel(String fileName, FileOutputStream outputStream, Workbook workbook)
            throws IOException {
        try {
            workbook.write(outputStream);
        } catch (IOException e) {
            throw new RuntimeException(e.getMessage());
        } finally {
            outputStream.close();
        }
    }
}

JSON测试样例

    {
        "paramInfo": {
            "userCount": {
                "dataName": "用户数", 
                "dateKey": "userCount"
            }, 
            "friendsCount": {
                "dataName": "好友数", 
                "dateKey": "friendsCount"
            }, 
            "phoneNo": {
                "dataName": "电话号码", 
                "dateKey": "phoneNo"
            }
        }, 
        "statisData": [
            [
                {
                    "paramKey": "userCount", 
                    "value": "100"
                }, 
                {
                    "paramKey": "friendsCount", 
                    "value": "200"
                }, 
                {
                    "paramKey": "phoneNo", 
                    "value": "11xx0"
                }
            ], 
            [
                {
                    "paramKey": "userCount", 
                    "value": "300"
                }, 
                {
                    "paramKey": "friendsCount", 
                    "value": "400"
                }, 
                {
                    "paramKey": "phoneNo", 
                    "value": "13xx0"
                }
            ],
            [
                {
                    "paramKey": "userCount", 
                    "value": "800"
                }, 
                {
                    "paramKey": "friendsCount", 
                    "value": "900"
                }, 
                {
                    "paramKey": "phoneNo", 
                    "value": "11xxx0"
                }
            ], 
            [
                {
                    "paramKey": "userCount", 
                    "value": "1300"
                }, 
                {
                    "paramKey": "friendsCount", 
                    "value": "1400"
                }, 
                {
                    "paramKey": "phoneNo", 
                    "value": "xxx110"
                }
            ]
        ]
    }