title: Excel to Json Conversion In Java
body: <p>I have a problem where I would like to convert the below Excel data into a JSON format containing children. Here is the information about what I am talking about.</p>
  
  <p><a href="https://i.stack.imgur.com/ghi9R.jpg" rel="nofollow noreferrer">Given excel data</a></p>
  
  <p>This data needs to be converted to the below format but unfortunately, I am unable to do so!</p>
  
  <p><a href="https://i.stack.imgur.com/DkC6D.jpg" rel="nofollow noreferrer">Type of JSON data needed</a></p>
  
  <p>Here is the updated code snippet I am using to get the data.</p>
  
  <pre><code>public static void excel2Csv(String file_path) {
  
      // Variables!
      Books b1, b2 = null;
      String jsonInString = "";
      int active_sheet_index = 0;
  
      // Initializing the parent and the children list!
      List&lt;Books&gt; bList = new ArrayList&lt;Books&gt;();
      List&lt;Books&gt; children = null;
  
      // Initializing the excel variables!        
      Sheet sheet = null;     
      Workbook workbook = null;
      FileInputStream inp = null;
  
      // Initializing the JSON Mapper variables!
      ObjectMapper mapper = null;
  
      try {
          // Reading the excel input for conversion!
          inp = new FileInputStream(new File(file_path));
  
          // Creating the excel workbook object!
          workbook = WorkbookFactory.create(inp);     
  
          // Get the first sheet!
          sheet = workbook.getSheetAt(active_sheet_index);
          Iterator&lt;Row&gt; iterator = sheet.iterator();
          while (iterator.hasNext()) {
              b1 = new Books();
              children = new ArrayList&lt;Books&gt;();
  
              // Iterating through the excel rows!
              Row row = iterator.next();
  
              // If Cell0 of the active row is blank or null!
              if(row.getCell(0)==null || row.getCell(0).getCellType()==Cell.CELL_TYPE_BLANK) {
                  b2 = new Books();               
                  b2.setBauthor(getCellValueAsString(row.getCell(1)));
                  b2.setBcost(getCellValueAsString(row.getCell(2)));
                  children.add(b2);
              } else {
                  b1.setBname(getCellValueAsString(row.getCell(0)));
                  b1.setBauthor(getCellValueAsString(row.getCell(1)));
                  b1.setBcost(getCellValueAsString(row.getCell(2)));              
              }
  
              if (children!=null &amp;&amp; children.size()&gt;0) {
                  b1.setChildren(children);
              }
              bList.add(b1);
          }
  
          mapper = new ObjectMapper();
          mapper.configure(SerializationFeature.INDENT_OUTPUT, true);
  
          jsonInString = mapper.writerWithDefaultPrettyPrinter().writeValueAsString(bList);
          System.out.println("Final Json= \n" + jsonInString);
  
          // Convert object to JSON string and save into file directly
          mapper.writeValue(new File("D:\\test.json"), new JSONTokener(jsonInString).nextValue().toString());
      } catch(Exception ex) {
          ex.printStackTrace();
      }
  }
  </code></pre>
  
  <p><strong>Pojo Class</strong> </p>
  
  <pre><code>public class Books {
  
      public String bname, bauthor, bcost;
  
      public List&lt;Books&gt; children;
  
      .... // Getters &amp; setters 
  }
  </code></pre>
  
  <p>Please take a look and suggest your view or a sample code snippet to generate the data in the specified JSON format.</p>