在Java中将json转换为excel [英] convert json to excel in java
本文介绍了在Java中将json转换为excel的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我必须将非常复杂的json文件转换为excel。
我的json文件是这样的：
I have to convert very complex json file to excel. my json file is something like that:
{
"expand": "schema,names",
"startAt": 2,
"maxResults": 120,
"total": 36,
"issues": [
{
"expand": "editmeta,renderedFields,transitions,changelog,operations",
"id": "13132",
"self": "http:// collaboration/rest/api/2/issue/13132",
"key": "SAV-119",
"fields": {
"worklog": {
"startAt": 0,
"maxResults": 0,
"total": 0,
"worklogs": []
}
}
},
{
"expand": "editmeta,renderedFields,transitions,changelog,operations",
"id": "13127",
"self": "http:// collaboration/rest/api/2/issue/13127",
"key": "SAV-114",
"fields": {
"worklog": {
"startAt": 0,
"maxResults": 123456789,
"total": 4,
"worklogs": [
{
"self": "http:// collaboration/rest/api/2/issue/13127/worklog/12614",
"author": {
"self": "http:// collaboration/rest/api/2/user?username=whatEver",
"name": "whatEver",
"emailAddress": "s_whatEver@company.co.ir",
"avatarUrls": {
"16x16": "http:// collaboration/secure/useravatar?size=xsmall&ownerId=whatEver&avatarId=10501",
"24x24": "http:// collaboration/secure/useravatar?size=small&ownerId=whatEver&avatarId=10501",
"32x32": "http:// collaboration/secure/useravatar?size=medium&ownerId=whatEver&avatarId=10501",
"48x48": "http:// collaboration/secure/useravatar?ownerId=whatEver&avatarId=10501"
},
"displayName": "soeyeh whatEver",
"active": true
},
"updateAuthor": {
"self": "http:// collaboration/rest/api/2/user?username=whatEver",
"name": "whatEver",
"emailAddress": "s_whatEver@company.co.ir",
"avatarUrls": {
"16x16": "http:// collaboration/secure/useravatar?size=xsmall&ownerId=whatEver&avatarId=10501",
"24x24": "http:// collaboration/secure/useravatar?size=small&ownerId=whatEver&avatarId=10501",
"32x32": "http:// collaboration/secure/useravatar?size=medium&ownerId=whatEver&avatarId=10501",
"48x48": "http:// collaboration/secure/useravatar?ownerId=whatEver&avatarId=10501"
},
"displayName": "soeyeh whatEver",
"active": true
},
"created": "2014-11-26T18:06:01.000+0330",
"updated": "2014-11-26T18:06:01.000+0330",
"started": "2014-11-26T18:05:00.000+0330",
"timeSpent": "1d 1h",
"timeSpentSeconds": 32400,
"id": "12614"
}
]
}
}
}
]
}
您会看到此json文件中包含jsonObject / jsonArray和key：value。在jsonArray内部，我可以将jsonObject作为数组元素，反之亦然。
在我的Excel输出中，每个jsonObject元素都有新行。
as you see this json file have jsonObject/jsonArray and key:value in it. Inside of jsonArray I can have jsonObject as array element and vise versa. in my excel output I have new row for each jsonObject element.
我期望的是：
here is what I expect:
这是我的代码：
public class JsonParseTest {
private static List<String> header = new ArrayList<String>();
private static List<Row> rows = new ArrayList<Row>();
private static Row row = new Row();
private static int rowsSize;
public static List<String> getHeader() {
return header;
}
public static List<Row> getRows() {
return rows;
}
public static void main(String[] args) {
try {
// 1.read the json file
JSONObject jsonObject = readJson();
//2.iterate json file
for (Iterator iterator = jsonObject.keySet().iterator(); iterator.hasNext(); ) {
String header = (String) iterator.next();
short type = getType(jsonObject, header);
if (type == (short) 0) {
iterateJsonObject(jsonObject, header);
} else if (type == (short) 1) {
iteratorJsonArray((JSONArray) jsonObject.get(header), header);
row = getRows().get(rowsSize);
} else if (type == (short) 2) {
createHeader(header);
addFieldToRow(String.valueOf(jsonObject.get(header)), header);
}
}
createExcelFile();
} catch (FileNotFoundException ex) {
ex.printStackTrace();
} catch (IOException ex) {
ex.printStackTrace();
} catch (ParseException ex) {
ex.printStackTrace();
} catch (NullPointerException ex) {
ex.printStackTrace();
} catch (InstantiationException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
}
}
public static void iterateJsonObject(JSONObject jsonObject, String header) {
for (Iterator outerIterate = jsonObject.keySet().iterator(); outerIterate.hasNext(); ) {
String key = (String) outerIterate.next();
short type = getType(jsonObject, key);
String newHeader = header + "__" + key;
if (type == (short) 0) {
iterateJsonObject((JSONObject) jsonObject.get(key), newHeader);
} else if (type == (short) 1) {
iteratorJsonArray((JSONArray) jsonObject.get(key), newHeader);
if (getRows().size() != 0) {
row = getRows().get(rowsSize);
}
} else if (type == (short) 2) {
createHeader(newHeader);
addFieldToRow(String.valueOf(jsonObject.get(key)), key);
}
}
}
public static void iteratorJsonArray(JSONArray jsonArray, String header) {
if (jsonArray != null) {
int index = 0;
for (Iterator iterator = jsonArray.iterator(); iterator.hasNext(); ) {
List<String> beforeItrFields = new ArrayList<String>();
for (String field : row.getField()) {
beforeItrFields.add("");
}
if (index == 0) {
rowsSize = getRows().size();
}
JSONObject jsonObject = (JSONObject) iterator.next();
iterateJsonObject(jsonObject, header);
if (!getRows().contains(row)) {
getRows().add(row);
}
reInitializeObj(row);
row.setField(beforeItrFields);
index++;
}
}
}
public static void reInitializeObj(Object o) {
if (o instanceof Row) {
row = null;
row = new Row();
}
}
//0:jsonObject,1:jsonArray ,2:key/value
public static Short getType(JSONObject jsonObject, String key) {
if (jsonObject.get(key) instanceof JSONObject)
return (short) 0;
else if (jsonObject.get(key) instanceof JSONArray)
return (short) 1;
else
return (short) 2;
}
public static void createHeader(String key) {
if (!getHeader().contains(key))
getHeader().add(key);
}
public static void addFieldToRow(String value, String key) {
row.addField(value);
}
public static JSONObject readJson() throws IOException, ParseException {
String filePath = "E:\\1.json";
FileReader reader = new FileReader(filePath);
JSONParser jsonParser = new JSONParser();
return (JSONObject) jsonParser.parse(reader);
}
public static void createExcelFile() throws IOException, IllegalAccessException, InstantiationException {
FileOutputStream fileOut = new FileOutputStream("Jira.xls");
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet worksheet = workbook.createSheet("work log");
HSSFRow row1 = worksheet.createRow((short) 0);
short index = 0;
//create header
for (String header : getHeader()) {
HSSFCell cellA1 = row1.createCell(index);
cellA1.setCellValue(header);
HSSFCellStyle cellStyle = workbook.createCellStyle();
cellStyle.setFillForegroundColor(HSSFColor.GOLD.index);
cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
cellA1.setCellStyle(cellStyle);
index++;
}
//create rows
index = 1;
for (Row row : getRows()) {
HSSFRow excelRow = worksheet.createRow(index);
short flag = 0;
for (String field : row.getField()) {
HSSFCell cellA1 = excelRow.createCell(flag);
cellA1.setCellValue(field);
flag++;
}
index++;
}
workbook.write(fileOut);
fileOut.flush();
fileOut.close();
}
}
excel标头设置良好，但不幸的是行的字段不在正确的列中，它们会被替换。
excel header set fine, but unfortunately fields of rows aren't in correct column they are displaced.
我应该怎么做才能解决这个问题。谢谢
what should I do to solve that.thank you
推荐答案
推荐使用诸如Jackson或Gson之类的库来帮助解决此问题。想法是使用该库使用JSON，并将其转换为 Map< String，Object> ，然后使用Java代码深入该映射以创建所需的输出作为与Excel完全兼容的.csv文件。是否有意义？此链接可能会有所帮助。
Recommend using a library such as Jackson or Gson to help with this. The idea is to use the library to consume the JSON and convert it into a Map<String, Object> and then drill into that map with your Java code to create the desired output as a .csv file fully compatible with Excel. Does it make sense? This link may be helpful.
这篇关于在Java中将json转换为excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
