title: in excel empty cells of non empty rows are neglected using java , POI jars
body: <p><strong>Excel To Json Conversion using JAVA, POI</strong></p>
  
  <blockquote>
    <blockquote>
      <p>Issue: Empty cells in Non Empty rows are neglected.</p>
      
      <blockquote>
        <p>I am trying to convert an excel file through java to Json.<a href="https://i.stack.imgur.com/VFtyE.png" rel="nofollow noreferrer">A2 and B2 cells are empty but these cells are negleted i want even these cells to be counted and return an empty string</a></p>
      </blockquote>
    </blockquote>
  </blockquote>
  
  <p><a href="https://i.stack.imgur.com/kUs4w.png" rel="nofollow noreferrer">output obtained, the 2nd row starts from C2 by neglecting A2 and B2  which has empty values</a></p>
  
  <p>public static String ExcelToJsonConverter(String filePath) throws Exception {</p>
  
  <pre><code>try {
  
  
      FileInputStream inp = new FileInputStream(filePath);
      Workbook workbook = WorkbookFactory.create(inp);
  
      Sheet sheet = workbook.getSheetAt(0);
  
      JSONArray rows = new JSONArray();
  
      int coun = 0;
  
  
      for (Iterator &lt; Row &gt; rowsIT = sheet.rowIterator(); rowsIT.hasNext();)
  
      {
  
          Row row = rowsIT.next();
          JSONObject jRow = new JSONObject();
  
  
          JSONArray cells = new JSONArray();
          for (Iterator &lt; Cell &gt; cellsIT = row.cellIterator(); cellsIT.hasNext();) {
              coun++;
              Cell cell = cellsIT.next();
  
  
              String val = "";
  
  
              if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
                  System.out.println("Formula is " + cell.getCellFormula());
                  switch (cell.getCachedFormulaResultType()) {
                      case Cell.CELL_TYPE_NUMERIC:
                          System.out.println("Last evaluated as: " + cell.getNumericCellValue());
                          val = Double.toString(cell.getNumericCellValue());
                          break;
                      case Cell.CELL_TYPE_STRING:
                          System.out.println("Last evaluated as \"" + cell.getRichStringCellValue() + "\"");
                          val = cell.getStringCellValue();
                          break;
                  }
              } else if (cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK) {
  
                  val = " ";
  
              } else {
  
                  val = formatter.formatCellValue(cell);
  
              }
  enter code here
              cells.put(val);
          }
          jRow.put("", cells);
          rows.put(jRow);
  
      }
  
  
      json.put("", rows);
  
  
  } catch (Exception e) {
      //throw new BsfConstraintViolationException("Please Upload a valid file."+e.getMessage());
      throw new BsfConstraintViolationException("Please Upload a valid file using Download Default BOQ option");
  }
  
  String formatJson = json.toString().replace("\"\":", "");
  
  formatJson = formatJson.toString().replace("{", "");
  formatJson = formatJson.toString().replace("}", "");
  formatJson = formatJson.substring(1, formatJson.length() - 1);
  
  
  return formatJson;
  </code></pre>
  
  <p>}</p>
  
  <p>Advance Thanks to all</p>