复杂JSON导出EXCEL表格

maven依赖

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.1.2</version>
</dependency>

<dependency>
    <groupId>cn.hutool</groupId>
    <artifactId>hutool-all</artifactId>
    <version>5.8.6</version>
</dependency>

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>fastjson</artifactId>
    <version>2.0.12.graal</version>
</dependency>

工具类

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.json.JSONUtil;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.ObjectMapper;
import lombok.Data;
import lombok.extern.slf4j.Slf4j;

import java.util.*;

/**
 * JSON字符串解析
 *
 * @author 苦瓜不苦
 * @date 2023/2/14 9:34
 **/
@Slf4j
public class JsonAnalysisUtil {

    /**
     * 美化JSON格式
     *
     * @param text 文本
     * @return
     */
    public static String beautify(String text) {
        try {
            if (StrUtil.isBlank(text)) {
                text = "{}";
            }
            if (JSONUtil.isTypeJSON(text)) {
                ObjectMapper mapper = new ObjectMapper();
                Object object = mapper.readValue(text, Object.class);
                text = mapper.writerWithDefaultPrettyPrinter().writeValueAsString(object);
            }
        } catch (JsonProcessingException e) {
            log.info("\n", e);
        }
        return text;
    }


    /**
     * 复杂JSON导出EXCEL
     *
     * @param text 文本
     * @param path 路径
     * @return
     */
    public static void exportExcel(String text, String path) {
        Result result = analysis(text);
        if (Objects.isNull(result)) {
            return;
        }
        int index = 0;
        Map<String, Map<String, String>> headerMap = result.getHeaderMap();
        Map<String, List<Map<String, String>>> dataListMap = result.getDataListMap();
        ExcelWriter writer = ExcelUtil.getBigWriter(path);
        for (String sheet : headerMap.keySet()) {
            Map<String, String> header = headerMap.get(sheet);
            List<Map<String, String>> list = dataListMap.get(sheet);
            // sheet名称不能超出31字符
            int length = sheet.length();
            if (length > 31) {
                sheet = sheet.substring(length - 30);
                sheet = sheet.substring(sheet.indexOf("-") + 1);
            }
            // 写数据
            if (index == 0) {
                writer.renameSheet(sheet);
            } else {
                writer.setSheet(sheet);
            }
            writer.setHeaderAlias(header);
            writer.write(list);
            index++;
        }
        writer.flush();
        writer.close();
    }


    /**
     * 解析复杂JSON数
     *
     * @param text 文本
     * @return
     */
    public static Result analysis(String text) {
        if (!JSONUtil.isTypeJSON(text)) {
            return null;
        }
        Map<String, Map<String, String>> headerMap = new LinkedHashMap<>();
        Map<String, List<Map<String, String>>> dataListMap = new LinkedHashMap<>();
        String sheet = "sheet";
        parse(dataListMap, headerMap, text, sheet);
        Result result = new Result();
        result.setHeaderMap(headerMap);
        result.setDataListMap(dataListMap);
        return result;
    }

    /**
     * 递归JSON数
     *
     * @param dataListMap 数据
     * @param headerMap   表头
     * @param text        文本
     * @param sheet       标题
     */
    private static void parse(Map<String, List<Map<String, String>>> dataListMap, Map<String, Map<String, String>> headerMap, String text, String sheet) {
        if (!JSONUtil.isTypeJSON(text)) {
            return;
        }

        if (Objects.isNull(sheet)) {
            sheet = "sheet";
        }

        List<Map<String, String>> dataList = dataListMap.getOrDefault(sheet, new ArrayList<>());

        Map<String, String> header = headerMap.getOrDefault(sheet, new LinkedHashMap<>());


        if (JSONUtil.isTypeJSONArray(text)) {
            JSONArray array = JSON.parseArray(text);
            for (int i = 0; i < array.size(); i++) {
                String value = array.getString(i);
                // 判断是否为字符串
                if (!JSONUtil.isTypeJSON(value)) {
                    String key = CollUtil.getLast(StrUtil.split(sheet, "-"));
                    Map<String, String> data = new LinkedHashMap<>();
                    data.put(key, value);
                    header.put(key, key);
                    dataList.add(data);
                } else {
                    parse(dataListMap, headerMap, value, sheet);
                }
            }
        }

        if (JSONUtil.isTypeJSONObject(text)) {
            Map<String, String> data = new LinkedHashMap<>();
            JSONObject object = JSON.parseObject(text);
            for (String key : object.keySet()) {
                String value = object.getString(key);
                // 判断是否为字符串
                if (!JSONUtil.isTypeJSON(value)) {
                    data.put(key, value);
                    header.put(key, key);
                } else {
                    String sheetNew = StrUtil.strBuilder()
                            .append(sheet)
                            .append("-")
                            .append(key)
                            .toString();
                    parse(dataListMap, headerMap, value, sheetNew);
                }
            }
            if (!data.isEmpty()) {
                dataList.add(data);
            }
        }


        if (dataList.size() > 0) {
            dataListMap.put(sheet, dataList);
        }

        if (!header.isEmpty()) {
            headerMap.put(sheet, header);
        }


    }


    @Data
    public static class Result {

        /**
         * 表头
         */
        private Map<String, Map<String, String>> headerMap;
        /**
         * 数据
         */
        private Map<String, List<Map<String, String>>> dataListMap;

    }

}

测试功能

@Test
public void test() {

    String text = "[{\"name\":\"张三\",\"age\":\"18\",\"sex\":\"男\"},{\"name\":\"李四\",\"age\":\"23\",\"sex\":\"男\"},{\"name\":\"王五\",\"age\":\"19\",\"sex\":\"男\"}]";
    String path = "D:\\";
    String excel = JsonAnalysisUtil.exportExcel(text, path);
    System.out.println(excel);

}