EasyExcel web动态表格导出

使用EasyExcel动态导出无对象模型的数据

原创已于 2022-06-13 17:54:06 修改 · 402 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#java #开发语言

于 2022-06-13 17:52:49 首次发布

该博客主要介绍了如何在Java中利用EasyExcel库实现动态表头的Excel导出，无需预先创建实体类。内容包括从JSON数据中解析表单字段，封装表头和数据，以及处理动态无对象的数据写入Excel的过程。示例代码详细展示了整个流程，适用于需要动态导出数据的场景。

场景：

需求：动态表头，web导出
实体类不适应，可以用EasyExcel提供的不创建对象写出

表单字段表：

结果表：
在这里插入图片描述
form_content内容实例：(需要取出的是value的值)

[
    {
        "id": 45,
        "activityId": 165353669805091,
        "formField": "姓名",
        "inputType": 0,
        "value": "1",
        "ztAppValue": "姓名:1\n"
    },
    {
        "id": 46,
        "activityId": 165353669805091,
        "formField": "联系方式",
        "inputType": 0,
        "value": "1",
        "ztAppValue": "联系方式:1\n"
    },
    {
        "id": 47,
        "activityId": 165353669805091,
        "formField": "爱好",
        "inputType": 2,
        "optionContent": [
            "吃饭",
            "睡觉",
            "打豆豆"
        ],
        "value": [
            "睡觉"
        ],
        "ztAppValue": "爱好:睡觉"
    },
    {
        "id": 48,
        "activityId": 165353669805091,
        "formField": "性别",
        "inputType": 1,
        "optionContent": [
            "男",
            "女"
        ],
        "value": "女",
        "ztAppValue": "性别:女\n"
    },
    {
        "id": 49,
        "activityId": 165353669805091,
        "formField": "学科",
        "inputType": 2,
        "optionContent": [
            "语",
            "数",
            "英",
            "物",
            "化"
        ],
        "value": [
            "数"
        ],
        "ztAppValue": "学科:数"
    },
    {
        "id": 50,
        "activityId": 165353669805091,
        "formField": "不良嗜好",
        "inputType": 1,
        "optionContent": [
            "睡觉",
            "打豆豆",
            "磨牙"
        ],
        "value": "打豆豆",
        "ztAppValue": "不良嗜好:打豆豆\n"
    },
    {
        "id": 51,
        "activityId": 165353669805091,
        "formField": "学生身份证",
        "inputType": 0,
        "optionContent": "352314200305028375",
        "value": "1",
        "ztAppValue": "学生身份证:1\n"
    },
    {
        "id": 52,
        "activityId": 165353669805091,
        "formField": "父亲姓名",
        "inputType": 0,
        "optionContent": "张三",
        "value": "1",
        "ztAppValue": "父亲姓名:1\n"
    },
    {
        "id": 53,
        "activityId": 165353669805091,
        "formField": "父亲电话",
        "inputType": 0,
        "optionContent": "12387324635",
        "value": "1",
        "ztAppValue": "父亲电话:1\n"
    }
]

注意点：

表头、数据封装成以下形式

//封装表头
List<List<String>> headList = new ArrayList<>();
//封装数据
List<List<String>> data = new ArrayList<>();

表单list长度必须和一条数据List长度对应

代码：

@RequestMapping(value = "/exportData", method = RequestMethod.GET)
    public Map<String, Object> exportData(Long activityId,HttpServletResponse response) throws Exception {

        try {
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
            String fileName = URLEncoder.encode("活动报名表", "UTF-8").replaceAll("\\+", "%20");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");

            // 取表头
            // 查出数据库的表头字段
            List<ActivityForm> activityFormList = activityFormService.getByActivityId(activityId);
            List<String> fieldList = new ArrayList<>();
            for (ActivityForm activityForm : activityFormList) {
                fieldList.add(activityForm.getFormField());
            }

            // 封装表头
            List<List<String>> headList = new ArrayList<>();
            for (String str : fieldList) {
                List<String> head = new ArrayList<>();
                head.add(str);
                headList.add(head);
            }
            List<String> head0 = new ArrayList<>();
            head0.add("报名状态");
            List<String> head1 = new ArrayList<>();
            head1.add("签到");
            List<String> head2 = new ArrayList<>();
            head2.add("签退");
            headList.add(head0);
            headList.add(head1);
            headList.add(head2);


            // 动态无对象封装数据
            List<List<String>> data = new ArrayList<>();

            // 将所有的formContent数据封装成一个list
            List<ActivityFormResult> resultList = activityFormResultService.getResultListExcel(activityId);
            List<String> resultString = new ArrayList<>();
            for (ActivityFormResult activityFormResult : resultList) {
                String formContent = activityFormResult.getFormContent();
                resultString.add(formContent);
            }


            for (int i = 0; i < resultList.size(); i++) {
                // 处理formContent数据
                JSONArray jsonArray = JSONArray.fromObject(resultString.get(i));
                List<String> valueList = new ArrayList<>();
                for (int j = 0; j < jsonArray.size(); j++) {
                    JSONObject jsonObject = jsonArray.getJSONObject(j);
                    Object value = jsonObject.get("value");
                    valueList.add(value.toString());

                }
                // 处理固定字段：报名状态、签到、签退
                valueList.add(resultList.get(i).getAuthState().toString());
                if (resultList.get(i).getSignIn() != null) {
                    valueList.add(resultList.get(i).getSignIn().toString());
                } else {
                    valueList.add("");
                }
                if (resultList.get(i).getSignOff() != null) {
                    valueList.add(resultList.get(i).getSignOff().toString());
                } else {
                    valueList.add("");
                }
                data.add(valueList);
            }

            // 写出Excel
            EasyExcel.write(response.getOutputStream())
                    .autoCloseStream(Boolean.FALSE)
                    .head(headList)
                    .sheet("data")
                    .doWrite(data);

            resultMap.put("status", 200);
            resultMap.put("message", "请求成功!");
        } catch (Exception e) {
            response.reset();
            response.setContentType("application/json");
            response.setCharacterEncoding("utf-8");
            resultMap.put("status", 500);
            resultMap.put("message", "请求失败!");
            LoggerUtils.fmtError(getClass(), e, "请求失败。");
        }

        return resultMap;
    }