将复杂的 JSON 导出到 EXCEL 表格
最编程
2024-05-12 20:24:33
...
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
**/
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);
}
}
public static class Result {
/**
* 表头
*/
private Map<String, Map<String, String>> headerMap;
/**
* 数据
*/
private Map<String, List<Map<String, String>>> dataListMap;
}
}