title: convert json to excel in java
body: <p>I have to convert very complex json file to excel.
  my json file is something like that:</p>
  
  <pre><code>{
  "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&amp;ownerId=whatEver&amp;avatarId=10501",
                                  "24x24": "http:// collaboration/secure/useravatar?size=small&amp;ownerId=whatEver&amp;avatarId=10501",
                                  "32x32": "http:// collaboration/secure/useravatar?size=medium&amp;ownerId=whatEver&amp;avatarId=10501",
                                  "48x48": "http:// collaboration/secure/useravatar?ownerId=whatEver&amp;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&amp;ownerId=whatEver&amp;avatarId=10501",
                                  "24x24": "http:// collaboration/secure/useravatar?size=small&amp;ownerId=whatEver&amp;avatarId=10501",
                                  "32x32": "http:// collaboration/secure/useravatar?size=medium&amp;ownerId=whatEver&amp;avatarId=10501",
                                  "48x48": "http:// collaboration/secure/useravatar?ownerId=whatEver&amp;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"
                      }                       
                  ]
              }
          }
      }
  ]
  </code></pre>
  
  <p>}</p>
  
  <p>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.</p>
  
  <p>here is what I expect:
  <img src="https://i.stack.imgur.com/qd2nl.jpg" alt="enter image description here"></p>
  
  <p>here is my code:</p>
  
  <pre><code>public class JsonParseTest {
  
  private static List&lt;String&gt; header = new ArrayList&lt;String&gt;();
  private static List&lt;Row&gt; rows = new ArrayList&lt;Row&gt;();
  private static Row row = new Row();
  private static int rowsSize;
  
  public static List&lt;String&gt; getHeader() {
      return header;
  }
  
  public static List&lt;Row&gt; 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&lt;String&gt; beforeItrFields = new ArrayList&lt;String&gt;();
              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();
  }
  </code></pre>
  
  <p>}</p>
  
  <p>excel header set fine, but unfortunately fields of rows aren't in correct column they are displaced.</p>
  
  <p><img src="https://i.stack.imgur.com/BUBVv.jpg" alt="enter image description here"></p>
  
  <p>what should I do to solve that.thank you </p>