convert json to excel in java

Question

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": "[email protected]",
                            "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": "[email protected]",
                            "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"
                    }                       
                ]
            }
        }
    }
]

}

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: enter image description here

here is my code:

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 header set fine, but unfortunately fields of rows aren't in correct column they are displaced.

enter image description here

what should I do to solve that.thank you

are you fix your pblm?

Karthi
– Karthi

2016-12-19 04:26:03 +00:00
Commented Dec 19, 2016 at 4:26 — Karthi
– Karthi, Commented Dec 19, 2016 at 4:26
What is the Row class?

Nida Sahar
– Nida Sahar

2018-06-06 22:41:38 +00:00
Commented Jun 6, 2018 at 22:41 — Nida Sahar
– Nida Sahar, Commented Jun 6, 2018 at 22:41

Community · Accepted Answer · 2017-05-23 11:45:47Z

1

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.

edited May 23, 2017 at 11:45

CommunityBot

11 silver badge

answered Jan 21, 2015 at 14:32

unigeek

2,83630 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

farhad Over a year ago

thank you my friend I took tow days off,I will check it a.s.a.p

unigeek Over a year ago

If you prefer to use the Gson library, there seems to be a good example of how to consume the JSON that way here.. stackoverflow.com/questions/19551242/… In this case, I suppose you are creating a mapping to your own specific Java classes, so a little different than using Jackson to convert to a Map<String, Object>

farhad Over a year ago

thank you.It really help me. but my problem is, to create gson with g.fromJson(myJson,MyClass.class) I have to say my class but I cant create this class because every time myjson is diffrent. I wanted to use something like Javassist but I couldn't create something like List<Class1> = new ArrayList<Class1>() property

Collectives™ on Stack Overflow

convert json to excel in java

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related