Question: I am using arraylist to collect reference IDs of the product from Excel sheet. I am using POI. The issue is that I don't want to include blank cells in my arraylist. Here is my code:

```
 public static ArrayList<String> extractExcelContentByColumnIndex()
        {
            ArrayList<String> columnData=null;

            try
            {
     FileInputStream fis=new FileInputStream(excel_path);
                Workbook wb=WorkbookFactory.create(fis);
                Sheet sh=wb.getSheet(sheetname);
                Iterator<Row> rowIterator=sh.iterator();
                columnData=new ArrayList<>();

                while(rowIterator.hasNext())
                {

                    Row row=rowIterator.next();
                    Iterator<Cell> cellIterator=row.cellIterator();
                    while(cellIterator.hasNext())
                    {
                        Cell cell=cellIterator.next();

        if((row.getRowNum()>=3) && (row.getRowNum()<=sh.getPhysicalNumberOfRows()))

                        {
                            if(cell.getColumnIndex()==3)// column under watch

                            {

                                columnData.add(cell.getStringCellValue());
                                Collections.sort(columnData);            
                            }
                        }
                    }
                }
                fis.close();

            }
            catch(Exception e)
            {
                e.printStackTrace();
            }

    System.err.println("DL BoM = "+columnData);
        return columnData;
    }

```

And output is :

```
DL BoM = [, , , , , , 03141, 03803, 08002, 08012, 08817, 13124, A9C22712, A9N21024, A9N21027, A9N21480]

```

Answer: 
The POI documentation provides some useful information to that specific topic.

> 
> Iterate over cells, with control of missing / blank cells
> =========================================================
> 
> 
> In some cases, when iterating, you need full control over how missing
>  or blank rows and cells are treated, and you need to ensure you visit
>  every cell and not just those defined in the file. (The CellIterator
>  will only return the cells defined in the file, which is largely those
>  with values or stylings, but it depends on Excel).
> 
> 
> In cases such as these, you should fetch the first and last column
>  information for a row, then call getCell(int, MissingCellPolicy) to
>  fetch the cell. Use a MissingCellPolicy to control how blank or null
>  cells are handled.
> 
> 
> 
> ```
> // Decide which rows to process
> int rowStart = Math.min(15, sheet.getFirstRowNum());
> int rowEnd = Math.max(1400, sheet.getLastRowNum());
> 
> for (int rowNum = rowStart; rowNum < rowEnd; rowNum++) {
>    Row r = sheet.getRow(rowNum);
>    if (r == null) {
>       // This whole row is empty
>       // Handle it as needed
>       continue;
>    }
> 
>    int lastColumn = Math.max(r.getLastCellNum(), MY_MINIMUM_COLUMN_COUNT);
> 
>    for (int cn = 0; cn < lastColumn; cn++) {
>       Cell c = r.getCell(cn, Row.RETURN_BLANK_AS_NULL);
>       if (c == null) {
>          // The spreadsheet is empty in this cell
>       } else {
>          // Do something useful with the cell's contents
>       }
>    }
> }
> 
> ```
> 
> 

Source: <http://poi.apache.org/spreadsheet/quick-guide.html#Iterator>
Before getting the cell value.. check if it's not empty 

```
  if(cell.getColumnIndex()==3)// column under watch
         {
               if(cell.getStringCellValue().Trim() != "")
               {
                    columnData.add(cell.getStringCellValue());
               }
               Collections.sort(columnData);            
          }

```