read json data to excel

Question

I have a JSON file in the below format.

{
  "applications": [
    {
      "author": "Appriss, Inc.",
      "rating": 4.5,
      "isAvailable": true,
      "isRecommended": null,
      "isEndorsed": false,
      "id": "WfIABNya87qyAWABoDivFQ",
      "app_name": "MobilePatrol Public Safety App",
      "icon_path": "org_5945/android_market_62834/appIcon.png",
      "custom_metadata": {
        "title": null,
        "description": null,
        "projects": null,
        "category": [
          100
        ],
        "user_segment": [
          200
        ],
        "aboutApp": null,
        "tablet_1_description": null,
        "tablet_2_description": null,
        "tablet_3_description": null,
        "tablet_4_description": null,
        "tablet_5_description": null,
        "screenshot_1_description": null,
        "screenshot_2_description": null,
        "screenshot_3_description": null,
        "screenshot_4_description": null,
        "screenshot_5_description": null,
        "endorsement": null,
        "developer_description": null,
        "developer_website": null
      },
      "operating_system": "ANDROID",
      "app_psk": 62834
    },
}

I want to read few of data(like author,rating,app_name etc) into an excel in the form of key/value pair using java. Written below code.

public class JsonParseTest {
private static List<String> header = new ArrayList<String>();
private static List<Row> rows = new ArrayList<Row>();
private static Row row ;-- not able to instantiate this
private static int rowsSize;
public static List<String> getHeader() {
    return header;
}
public static List<Row> getRows() {
    return rows;
}
public static void main(String[] args) throws IOException, ParseException {
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) 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);
       if (type == (short) 2) {
            createHeader(header);
            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 : ((Object) 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);
            ((Object) 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 = "C:\\Users\\skond2\\Desktop\\JSON Files\\PSEID123_APPS.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("Apps.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();
}
}

I'm getting errors at getField, addField methods of Row interface. First thing, is it correct declaration? private static Row row =new Row(); Row is from org.apache.poi.ss.usermodel.Row;

If you don't know the API, search for EXCEL in Java, if you don't know how to use it at all, follow the guide usually provide with the API. If none of those, please update your question with a problem. For now, this is off-topic to ask API or guide. Please see [ask] and the [help/on-topic] — AxelH, May 16 '17 at 12:59
What code ? The JSON is not enough, you need to show what you have tried to read, decode and insert the value. — AxelH, May 16 '17 at 13:13

score 1 · Answer 1 · answered May 16 '17 at 12:57

1

You can use the famous Apache POI for creating excel files. It is available here with documentation links. Your question is very general. You should try to do it on your own and get back to stackoverflow when you have a precise programming question.

answered May 16 '17 at 12:57

Stimpson Cat

1,444
19
44

score 0 · Accepted Answer · edited May 23 '17 at 12:26

Your problem can be broken down into the following 3 parts

Parse the JSON
Read the required values
Write the data in excel

For the first part you can use the any of the wide range of JSON parsing APIs and can also refer to this question

For the second part, once you get the data into your code, you need to be able to read through it, for this you'd need to be able to traverse through the Object that you'll get by using the above mentioned API.

And for the last part, you can simply write the output on a file in CSV format and open it in Excel.

This answer may seem vague, please comment if you need clarification

I have added the code written and the error i'm facing. Could you help me? — SKondapalli, May 16 '17 at 13:58

read json data to excel

2 Answers2

Linked