json数据转excel文件
本帖最后由 HK仅輝 于 2024-7-23 19:07 编辑修改bug ：
processJsonObject方法
136：row += arrLength; ====> row += (arrLength<=0?1:arrLength);
143: row += objSize; ===> row += row_n;
仓库：https://gitee.com/th8664484/json2xls/
使用：
/**
*json数据字符串
*文件类型
*保存文件路径
*/
Json2xlsHandle.convertToExcel(json, Json2xlsHandle.ExcelType.xls,path);
附件中 有一个json文件和一个生成的excel文件package cn.xenosp.json2xls;
import com.alibaba.fastjson2.JSON;
import com.alibaba.fastjson2.JSONArray;
import com.alibaba.fastjson2.JSONObject;
import org.apache.commons.io.FileUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.IOException;
import java.util.*;
/**
* 作者： TongHui
* 创建时间: 2024-07-03 15:57
* 描述: json 转 xls 处理器
* 版本: 1.0
*/
public class Json2xlsHandle {
private byte[] filebyte = null; //文件数据
private int maxCell = 0;// 最大单元格下标。用于渲染工作簿样式
private Workbook workbook;
private ExcelType excelType;
private Json2xlsHandle(){
}
private Json2xlsHandle(ExcelType excelType){
this.excelType = excelType;
if (excelType.code == 1){
this.workbook = new HSSFWorkbook();
}
if (excelType.code == 2){
this.workbook = new XSSFWorkbook();
}
}
public static void convertToExcel(String jsonStr,ExcelType excelType,String xlsFilePath){
if (jsonStr == null || jsonStr.length() == 0){
throw new IllegalStateException("jsonStr IS NULL");
}
if (xlsFilePath == null || xlsFilePath.length() == 0){
throw new IllegalStateException("xlsFilePath IS NULL");
}
/**
* 校验是否是json格式
*/
if (!JSON.isValid(jsonStr)) {
throw new IllegalStateException("Invalid JSON");
}
Json2xlsHandle json2xlsHandle = new Json2xlsHandle(excelType);
json2xlsHandle.json2xls(jsonStr);
json2xlsHandle.saveFile(xlsFilePath);
}
/**
* 入口方法
* @Param json json格式字符串
*/
private void json2xls(String json){
List<CellBean> list = new ArrayList<>();
System.out.println("************************解析json数据******************************");
if (JSON.isValidArray(json)){
JSONArray jsonArray = JSON.parseArray(json);
processJsonArray(jsonArray,0,0,0,list);
}else {
JSONObject jsonObject = JSON.parseObject(json);
processJsonObject(jsonObject,0,0,list,0);
}
System.out.println("************************解析json数据******************************");
// 获取文件
ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
try {
System.out.println("************************生成文件******************************");
createWorkbook(workbook,list);
workbook.write(outputStream);
filebyte = outputStream.toByteArray();
System.out.println("************************生成文件******************************");
} catch (IOException e) {
e.printStackTrace();
}finally {
try {
outputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
/**
* 处理json对象转为结果对象的过程
* 把每个最小 k:v 元素转为单元格对象
* @param jsonObject json数据
* @param x 行坐标
* @param y 列坐标
* @param list 结果数据
* @param isArrayObj 判断是否是数组下的一个对象
* @Return {
* "":{}
* "":[]
* "":""
* }=> 返回行数为 本身{}的行 + 子{}的行 + 子[]的行
* 一个 k:v 为一行
*/
private int processJsonObject(JSONObject jsonObject,int x,int y, List<CellBean> list,int isArrayObj){
System.out.println(">>>>>>>>>>>>>>>>>>>>>>>>>>>>>{进入josn对象解析}");
int row = x , cell = y;
Set<String> set = jsonObject.keySet();
int maxRow = set.size(),objSize = 0,arrLength = 0;
if (maxCell < y){
maxCell = y;
}
for (String key : set) {
String value_json = jsonObject.getString(key);
if (isArrayObj == 0){
addKeyCell(list,row,cell,key);
}
if (JSON.isValidArray(value_json)){
int row_n = processJsonArray(JSON.parseArray(value_json),row,cell+1,isArrayObj,list);
/**
*json数组中对象的key最大个数
*/
if (arrLength < row_n){
arrLength = row_n;
}
row += (arrLength<=0?1:arrLength);
}else if (JSON.isValidObject(value_json)){
JSONObject parseObject = JSON.parseObject(value_json);
int row_n = processJsonObject(parseObject,row,cell+1,list,isArrayObj);
if (objSize < row_n){
objSize = row_n;
}
row += row_n;
}else {
CellBean cellBean = new CellBean();
cellBean.row = row++;
cellBean.cell = cell+1;
cellBean.conent = value_json;
list.add(cellBean);
}
}
System.out.println(">>>>>>>>>>>>>>>>>>>>>>>>>>>>>{退出josn对象解析}");
return maxRow + objSize+ arrLength;
}
/**
* 处理json数组对象转为结果对象的过程
* 数组中的每个最小元素，转为单元格对象
* @param jsonArrayjson数组数据
* @param x 行坐标
* @param y 列坐标
* @param isArrayObj 是否为数组下的对象
* @param list 结果数据
* @return [
* []
* {}
* ""
* ] => 返回行数为 子[] 的行 + 子{} 的行
*/
private int processJsonArray(JSONArray jsonArray,int x, int y,int isArrayObj,List<CellBean> list){
System.out.println(">>>>>>>>>>>>>>>>>>>>>>>>>>>>>{进入josn数组解析}");
int row = x , cell = y;
int maxRow = 0, objSize = 0, arrLength = 0;
JSONObject parseObject = null;
for (int i = 0; i < jsonArray.size();i++) {
String json = jsonArray.getString(i);
if (JSON.isValidArray(json)){
int row_n = processJsonArray(JSON.parseArray(json),row,cell,0,list);
if (arrLength < row_n){
arrLength = row_n;
}
}else if (JSON.isValidObject(json)){
if (parseObject == null){
parseObject = JSON.parseObject(json);
}
/**
* 根据第一个 json对象，校验后续对象，缺少补充 “”
*/
JSONObject newparseObject = verifyJsonObject(parseObject,JSON.parseObject(json));
int row_n = processJsonObject(newparseObject,row,(cell+i),list,isArrayObj++);
if (objSize < row_n){
objSize = row_n;
}
}else {
CellBean cellBean = new CellBean();
cellBean.row = row;
cellBean.cell = cell+i;
cellBean.conent = json;
list.add(cellBean);
}
}
System.out.println(">>>>>>>>>>>>>>>>>>>>>>>>>>>>>{退出josn数组解析}");
maxRow = (maxRow + objSize + arrLength)-1;
return maxRow < 0 ? 0 :maxRow; }
/**
* 添加key单元格。针对是对象中的key
*/
private void addKeyCell(List<CellBean> list,int x,int y,String content){
CellBean cellBean = new CellBean();
cellBean.row = x;
cellBean.cell = y;
cellBean.conent = content;
cellBean.isKeyCell = true;
list.add(cellBean);
}
/**
* 校验jion对象数据是否一致
* 通过 json1 校验 json2 中是否有缺少的kye。
* 缺少时补充key并设置 ”“
* 返回新对象
* @param json1
* @param json2
* @return
*/
private JSONObject verifyJsonObject(JSONObject json1, JSONObject json2) {
JSONObject newJson = new JSONObject();
Set<String> set = json1.keySet();
for (String key : set) {
if (json2.containsKey(key)) {
newJson.put(key,json2.getString(key));
json2.remove(key);
}else {
newJson.put(key,"");
}
}
newJson.putAll(json2);
return newJson;
}
/**
* 生成工作簿并填充数据
* @param hssfWorkbook
* @param list
*/
private void createWorkbook(Workbook hssfWorkbook,List<CellBean> list){
Sheet sheet = hssfWorkbook.createSheet();
int maxCell = 0;
/**
* 填写数据
*/
for (CellBean bean : list) {
Row row = sheet.getRow(bean.row);
if (row == null) {
row = sheet.createRow(bean.row);
row.setHeight((short) 400);
}
Cell cell = row.getCell(bean.cell);
if (cell == null){
cell = row.createCell(bean.cell);
}else {
short lastCellNum = row.getLastCellNum();
cell =row.createCell(lastCellNum);
}
// 保存最大列
if (maxCell < cell.getColumnIndex()){
maxCell = cell.getColumnIndex();
}
cell.setCellValue(bean.conent);
CellStyle cellStyle = hssfWorkbook.createCellStyle();
if (bean.isKeyCell){
Font font = hssfWorkbook.createFont();
font.setBold(true);
cellStyle.setFont(font);
}
cell.setCellStyle(cellStyle);
}
/**
* 渲染样式
*/
int lastRowNum = sheet.getLastRowNum() ;
for (int i = 0; i <= lastRowNum; i++) {
Row row = sheet.getRow(i);
for (int j = 0; j <= maxCell; j++) {
Cell cell = row.getCell(j);
CellStyle cellStyle = null;
if (cell == null){
cell = row.createCell(j);
cellStyle = hssfWorkbook.createCellStyle();
}else {
cellStyle = cell.getCellStyle();
}
// 自适应列宽
sheet.autoSizeColumn(j);
// 设置单元格边框
cellStyle.setBorderBottom(BorderStyle.THIN);
cellStyle.setBottomBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
cellStyle.setBorderLeft(BorderStyle.THIN);
cellStyle.setLeftBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
cellStyle.setBorderRight(BorderStyle.THIN);
cellStyle.setRightBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
cellStyle.setBorderTop(BorderStyle.THIN);
cellStyle.setTopBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
cell.setCellStyle(cellStyle);
}
}
}
/**
* 传入文件保存路径进行保存
* @param path
*/
private void saveFile(String path){
try {
String filePath = "";
if (path.endsWith(".xls") && excelType.code != 1){
filePath = path.replace(".xls",".xlsx");
} else
if (path.endsWith(".xlsx") && excelType.code != 2){
filePath = path.replace(".xlsx",".xls");
}else if (!path.endsWith(".xls") && !path.endsWith(".xlsx")){
filePath = path + File.separator + new Date().getTime()+ (excelType.code == 1?".xls":".xlsx");
}else {
filePath = path;
}
System.out.println("保存文件：>>>>>>>>"+filePath);
FileUtils.writeByteArrayToFile(new File(filePath),filebyte);
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 单元格javaBean
*/
class CellBean{
int row;
int cell;
String conent;
boolean isKeyCell;
}
public static enum ExcelType{
xls(1),xlsx(2);
int code;
ExcelType(int code){
this.code = code;
}
}
}
附件怎么回事，没有设置收费{:301_971:} 随一套python的 以json路径提取节点数据的code
```python
import json
from typing import Any, List, Dict
class JsonUtils:
@staticmethod
def read_json_file(filename: str) -> Dict:
"""
读取JSON文件并返回其内容
:param filename: JSON文件的路径
:return: JSON文件的内容
"""
with open(filename, 'r', encoding='utf-8') as file:
data = json.load(file)
return data
@staticmethod
def write_json_file(filename: str, data: Dict | Any) -> None:
"""
将数据写入JSON文件
:param filename: JSON文件的路径
:param data: 要写入文件的数据
"""
with open(filename, 'w', encoding='utf-8') as file:
json.dump(data, file, indent=2)
@staticmethod
def extract_value(json_data: Dict, key_mapping: str) -> Dict | List | str:
"""
从JSON数据中提取值, 根据键映射路径
:param json_data: JSON数据对象
:param key_mapping: 键映射路径, 格式为 key1>key2>key3>..., 支持数组标记[], 示例: payload>Orders[]>OrderTotal>Amount
:return: 提取的值, 可以是单个值或者值列表
:raises ValueError: 如果无法找到路径中的键或路径中间存在数组标记但对应不是数组时
"""
current_data = json_data
path = key_mapping.split('>')
for idx, key in enumerate(path):
if key.endswith('[]'):# 处理数组标记 []
array_key = key[:-2]# 去掉[]后的键
current_data = current_data.get(array_key, [])
if not isinstance(current_data, list):
raise ValueError(f"路径中间存在数组标记但对应不是数组: {array_key}, 完整路径: {key_mapping}")
sub_key_mapping = '>'.join(path)
if len(sub_key_mapping) > 0:
return
return current_data
elif key.endswith(']') and '[' in key:# 处理带有索引的数组
array_key, index_str = key[:-1].split('[')
index = int(index_str)
current_data = current_data.get(array_key, [])
if not isinstance(current_data, list) and current_data is not None:
raise ValueError(f"路径中间存在数组标记但对应不是数组: {array_key}, 完整路径: {key_mapping}")
if current_data is None or index >= len(current_data):# 优化提取单个值的逻辑
return None
current_data = current_data
elif isinstance(current_data, dict) and key in current_data:
current_data = current_data.get(key)
else:
return None
return current_data
```
用法
``` python
total_key = 'result>total'
page_items_key = 'result>pageItems'
product_sku_id_key = 'productSkuSummaries[]>productSkuId'# 商品SkuID
ext_code_key = 'productSkuSummaries[]>extCode'# TemuSKU
supplier_price_key = 'productSkuSummaries[]>supplierPrice'# 供应商价格
virtual_stock_key = 'productSkuSummaries[]>virtualStock'# 虚拟库存
for pageItem in JsonUtils.extract_value(js, page_items_key):
product_sku_id_arr = JsonUtils.extract_value(pageItem, product_sku_id_key)
ext_code_arr = JsonUtils.extract_value(pageItem, ext_code_key)
supplier_price_arr = JsonUtils.extract_value(pageItem, supplier_price_key)
virtual_stock_arr = JsonUtils.extract_value(pageItem, virtual_stock_key)
```
测试数据
```json
{
"success": true,
"errorCode": 1000000,
"errorMsg": null,
"result": {
"total": 2,
"pageItems": [
{
"productSkuSummaries": [
{
"productSkuId": 123456,
"supplierPrice": 50000,
"extCode": "ABC",
"virtualStock": 20
}
],
"createdAt": 1720240000000
},
{
"productSkuSummaries": [
{
"productSkuId": 456789,
"supplierPrice": 50000,
"extCode": "BCD",
"virtualStock": 20
}
],
"createdAt": 1720240000000
}
]
}
}
``` json为什么要转成excel呢？目的是什么？ 看错了，以为是json转成exe，吓一跳。{:1_908:} 感谢分享，收藏备用 不错，收藏了 学到了，感谢分享 感谢分享 感谢分享,希望楼主多出一些这种小工具.
页:
[1]
2