How to loop into Excel file row in Java Poi
<p>I'm trying to loop into my Excel rows using the row.hasNext() but only loop once and return the 1st row only. (I'm using eclipse btw.)</p>

<p>This is my Excel (.xlsm) file:</p>

<pre><code>BILLOFLADING || REFERENCENUMBER || PURCHASEORDERNUMBER... and so on
4621002      || 7672784         || BLAZER .... and so on
4621001      || 7672914         || CG605515.... and so on
..some more rows here..
</code></pre>

<p>This is my code:</p>

<pre><code>String filename = "C:\\Users\\myExcel.xlsm";
    String batchRunNbr = convertDateToString(new Date(), "yyMMddHHmmSS");

    List sheetData = new ArrayList();
    FileInputStream fis = null;
    try {
        fis = new FileInputStream(filename);
        XSSFWorkbook workbook = new XSSFWorkbook(fis);
        XSSFSheet sheet = workbook.getSheetAt(0);

        Iterator&lt;Row&gt; rows = sheet.rowIterator();
        int counter = 0;
        while (rows.hasNext()) {
            counter++;
            log.info("COUNTER&gt;"+counter);
            Row row = rows.next();
            Iterator&lt;Cell&gt; cells = row.cellIterator();

            List data = new ArrayList();
            while (cells.hasNext()) {
                Cell cell = cells.next();
                data.add(cell);
            }

            sheetData.add(data);
        }
    } catch (IOException e) {
        e.printStackTrace();
    } finally {
        if (fis != null) {
            fis.close();
        }
    }

    showExcelData(sheetData, batchRunNbr);
    return 0;
}

private void showExcelData(List sheetData, String batchRunNbr) throws ParseException {

     DataFormatter formatter2 = new DataFormatter();
    DateFormat formatter;
    formatter = new SimpleDateFormat("dd/mm/yyyy");
    boolean firstTime = true;
    int count = 0;
    for (int i = 0; i &lt; sheetData.size(); i++) {
        List list = (List) sheetData.get(i);

        if (firstTime) {
            firstTime = false;

        }else{
            count++;
            log.info("VALUE OF R&gt;"+count);
        for (int j = 0; j &lt; list.size(); j++) {
            Cell cell = (Cell) list.get(j);

                if ((cell.getCellType() == Cell.CELL_TYPE_STRING) || (cell.getCellType() == Cell.CELL_TYPE_NUMERIC)){

                switchCase(formatter, i, cell);


            } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
                System.out.print(cell.getBooleanCellValue());
            }
            if (j &lt; list.size() - 1) {
                i++;
                System.out.print("\t");
            }
        }

        System.out.println("");


        //this code is an sql statement to insert to the database using hibernate
        insertToStaging(batchRunNbr);




        }
    }
    log.info("Completed!");
}
</code></pre>

<p>And this is my <strong>switchCase()</strong> method to allign the cell values:</p>

<pre><code>switch (i) {

    case 1:
        this.billOfLading = formatNumber(Double.toString(cell.getNumericCellValue()));
        log.info(i+"="+this.billOfLading);
        break;
    case 2:
        this.referenceNbr = formatNumber(Double.toString(cell.getNumericCellValue()));
        log.info(i+"="+this.referenceNbr);
        break;
    case 3:
        this.purchaseOrderNbr = formatText(cell.getStringCellValue());
        log.info(i+"="+this.purchaseOrderNbr);
        break;
    case 4:
        this.sku = formatText(cell.getStringCellValue());
        log.info(i+"="+this.sku);
        break;
..and so on...
</code></pre>

<p>This is my <strong>formatText()</strong> and <strong>formatNumber()</strong> methods to add single quotes or null values to each cell.</p>

<pre><code>    private String formatText(String s) {
    if ((s != null) &amp;&amp; (s.trim().length() &gt; 0)){
        s = "'" + s + "'";
    } else {
        s = "null";
    }
    return s;
}

private String formatNumber(String s) {

    if ((s != null) &amp;&amp; (s.trim().length() &gt; 0)) {
        return s;
    } else {
        s = "0";
    }

    return s;
}
</code></pre>

<p>And my result is looping only once..</p>

<pre><code>main 2016/11/17 14:47:33:618 INFO  com....(234) - COUNTER&gt;1
main 2016/11/17 14:47:33:622 INFO  com....(234) - COUNTER&gt;2
main 2016/11/17 14:47:33:622 INFO  com....(234) - COUNTER&gt;3
main 2016/11/17 14:47:33:622 INFO  com....(234) - COUNTER&gt;4
main 2016/11/17 14:47:33:627 INFO  com....(283) - VALUE OF R&gt;1
main 2016/11/17 14:47:33:632 INFO  com....(349) - 1=4621002.0
    main 2016/11/17 14:47:33:632 INFO  com....(354) - 2=7672784.0
    main 2016/11/17 14:47:33:635 INFO  com....(359) - 3='BLAZER'
    main 2016/11/17 14:47:33:636 INFO  com....(364) - 4='CT101_6X48_FL_852'
    main 2016/11/17 14:47:33:636 INFO  com....(370) - 5=9.0
    main 2016/11/17 14:47:33:647 INFO  com....(383) - 6='2016-11-14 00:00:00'
    main 2016/11/17 14:47:33:648 INFO  com....(388) - 7='DEJ'
    main 2016/11/17 14:47:33:648 INFO  com....(393) - 8='LTL'
    main 2016/11/17 14:47:33:649 INFO  com....(398) - 9='Prepaid'
    main 2016/11/17 14:47:33:650 INFO  com....(403) - 10='FRYS FLOORING, LLC'
    main 2016/11/17 14:47:33:650 INFO  com....(408) - 11=null
    main 2016/11/17 14:47:33:651 INFO  com....(413) - 12='7 E MAIN STREET'
    main 2016/11/17 14:47:33:651 INFO  com....(418) - 13='ELIZABETHVILLE'
    main 2016/11/17 14:47:33:652 INFO  com....(423) - 14='PA'
    main 2016/11/17 14:47:33:652 INFO  com....(428) - 15='17023'
    main 2016/11/17 14:47:33:653 INFO  com....(433) - 16='US'
    main 2016/11/17 14:47:33:653 INFO  com....(438) - 17='7173626987'
    main 2016/11/17 14:47:33:654 INFO  com....(443) - 18=null
    main 2016/11/17 14:47:33:654 INFO  com....(448) - 19='T lot number'
    main 2016/11/17 14:47:33:655 INFO  com....(457) - 20='284392'
</code></pre>