基于POI的可快速定制Excel导出脚本设想

基于POI的可快速定制Excel导出脚本设想

背景

  年关将至，业务人员提出了好多比较着急但是又不常用的取数需求。所谓不常用，大概了是由于业务人员前期调研产生的临时需求，后续也不会大面积铺开，没必要专门分配人员去开发。所谓比较着急，开发人员开发出来需要一段时间，等到投产窗口投产又得一段时间，一定程度上可能错过这个市场机遇，基于此，提出这个设想。

  目前从网络上所查到的方法都是基于poi去实现的，且实现方法比较简单，不太适用于比较复杂的业务场景（超大规模数据导出（一个excel装不下），大规模数据分组（以千为单位）导出等），秉承着“用户至上”的原则，急业务人员之所急，想业务人员之所想，于是便提出了如此设想。

  为了能够满足可快速定制的功能，参考了一些开源项目的配置文件和流程控制文件，这些大都是基于yml文件和json文件去实现的，因此决定通过json+sql来实现可定制的功能

可定制的json文件

目前json文件设想如下：

{
    "fileName":"文件名称",
    "titleTemplate" : [  //之所以用数组的形式是为了可以日后扩展为一个文件中多个sheet页
        {
            "sheetName" : "sheet页名称",
            "title":
            [
                {
                    "titleName": "表头的标题",
                    "headName": "与后续内容数据对应的一个key",
                    "heightSize": 单元格宽度
                }
            ]
        }
    ],
    "contentTemplate": [ //同样采用数组的方式是为了保证每个sheet都有对应的sql
        [
            {
                "execute":"select distinct street as street from epidemic_house",  //待执行的sql
                "args":"street",       //需要去传递的参数作为下个分组使用
                "split": true
            },
            {
                "execute":"select * from epidemic_house where street = ${street}" //需要导出文件用的sql，参数为上个sql传递过来的，根据上个sql执行结果分批执行，待生成文件sql无args参数（目前仅仅支持一个参数）
            }
        ]
    ]
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

json文件中的titleTemplate是为了生成导出excel中的标题信息，contentTemplate是通过sql去查询需要的数据，最后通过java代码去完成excel的拼接（目前只支持一个sheet页的处理，不支持多个sheet页的处理）

一个简单的样例

该案例是为了完成对待处理数据的超大规模分组，根据street参数生成每个street为分组的excel数据

{
    "fileName":"20221124高风险地区分类",
    "titleTemplate" : [
        {
            "sheetName" : "poiScript测试",
            "title":
            [
                {
                    "titleName": "街道",
                    "headName": "street",
                    "heightSize":3000
                },
                {
                    "titleName": "社区",
                    "headName": "village",
                    "heightSize":4000
                },
                {
                    "titleName": "具体地区",
                    "headName": "location",
                    "heightSize":4500
                },
                {
                    "titleName": "时间",
                    "headName": "time",
                    "heightSize":3000
                }
            ]
        }
    ],
    "contentTemplate": [
        [
            {
                "execute":"select distinct street as street from epidemic_house",
                "args":"street",
                "split": true
            },
            {
                "execute":"select * from epidemic_house where street = ${street}"
            }
        ]
    ]
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43

代码部分

代码部分并无特别惊艳之处，只是实现了两个最主要的功能

• 1、实现对超大规模数据拆分并生成多个excel
• 2、实现通过sql的迭代查询从而完成超大规模数据的分组导出
  代码一：实现对超大规模数据拆分并生成多个excel

    /**
     * 提前判断内容的数据连是否过大excel是否装得下，是否需要分成多个excel文件
     * @param sheetName
     * @param titleData
     * @param contentData
     * @param fileName
     */
    private void preCreateExcelFile(String sheetName, JSONArray titleData, JSONArray contentData, String fileName) {

        if(contentData.size() < EXCEL_ROE_LIMIT + 1)  {
            createExcelFile(sheetName, titleData, contentData, fileName);
        } else {
            int totalCount = contentData.size()/EXCEL_ROE_LIMIT + 1;
            for(int i=1;i<= totalCount; i++) {
                int fromIndex = (i-1)*EXCEL_ROE_LIMIT;
                JSONArray subContentData = new JSONArray();
                for(int j = fromIndex; j<((fromIndex+ + EXCEL_ROE_LIMIT)>contentData.size()?contentData.size():(fromIndex+ + EXCEL_ROE_LIMIT)); j++) {
                    subContentData.add(contentData.getJSONObject(j));
                }
                createExcelFile(sheetName, titleData, subContentData, fileName + "part" + i);
                subContentData.clear();
            }
        }
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

代码二：实现通过sql的迭代查询从而完成超大规模数据的分组导出

/**
     * 开始进行分文件处理
     * @param contentData 内容信息
     * @param titleData 标题信息
     */
    private void createSplitExcelByFile(JSONArray contentData, JSONObject titleData, String fileName) {
        String args = "";
        JSONArray titleName = titleData.getJSONArray("title");
        List> tempExecMap = Lists.newArrayList();
        for(int i=0;i