iterating over the columns of a row through poi in java

Question

I have a excel file named abc.xls in my c: drive (local computer) , now in that excel file in the first sheet itself there is a table as shown below and this below table can lie in any range with in the sheet so i have developed the below java program which will scan the entire sheet first by row basis and then on column basis and will find the cell where TradeRef is there

 TradeRef   TMS  Deal     Date        
    12      45   DRT    23/97/2014      
    23      36   QWE    21/07/2015  

now the problem in my below program is that it captures the cell where TradeRef is there and then it iterates over the columns and then in similar fashion it captures the next row and iterating over the columns

but the logic that i want to apply is that when it captures the TradeRef cell and iterating over the columns and reached to the last column of the table which is Date in the above table then it should further scan the next 20 columns within the same row and if within the next 20 columns there is no cell having any value then it should move to the next row and if within the 20 columns it mite be that any cell can have value then in that case it should read that cell value

so it would be like

 TradeRef   TMS  Deal     Date          <----- scan next 20 columns is there is no value in next 20 cells then move to next row else include that cell value also------->
    12      45   DRT    23/97/2014        
    23      36   QWE    21/07/2015  

so please advise how to implement the above logic of scanning the next 20 columns within the row below is my earlier implementation that is

public class AAA {
    public static void main(String[] args) throws IOException {


        FileInputStream file = null ;
         try {

                 file = new FileInputStream(new File("C:\\abc.xls"));
                HSSFWorkbook workbook = new HSSFWorkbook(file);
                HSSFSheet firstSheet = workbook.getSheetAt(0);
                Iterator<Row> iterator = firstSheet.iterator();

                Cell c = findFirstRow(firstSheet);
             }




             catch (FileNotFoundException e) {
                e.printStackTrace();
            } catch (IOException e) {
                e.printStackTrace();
            } finally{
                file.close();
            }

    }

    public static Cell findFirstRow(HSSFSheet firstSheet) {
          for (Row row : firstSheet) {
            for (Cell cell : row) {
                  cell.setCellType(cell.CELL_TYPE_STRING);
              if ("TradeRef".equals(cell.getStringCellValue())) {
                int row1 = cell.getRowIndex() + 1;
                int col = cell.getColumnIndex();
                if (firstSheet.getRow(row1) == null)
                  throw new RuntimeException("Row " + row1 + 1 + " is empty!");
                Cell startOfFirstDataRow = firstSheet.getRow(row1).getCell(col);
                if (startOfFirstDataRow == null) {
                  CellReference ref = new CellReference(row1, col);
                  throw new RuntimeException("Data not found at " + ref.formatAsString());
                }
                return startOfFirstDataRow;
              }
            }
          }
          throw new RuntimeException("TradingRef header cell not found!");
        }


}

so please advise how can i implement the above logic of scanning next 20 columns

Answer 1

First, you should probably use the org.apache.poi.ss.usermodel API, which work with all Excel files while the HSSF* classes only work with .xls files.

The org.apache.poi.ss.usermodel.Sheet class has a getFirstRow() method that you can use to start your search. Next you want to find a sequence of Cells containing the given Strings, like:

// This is an array containing the headers
final String[] headers = { "TradeRef", "TMS", "Deal", "Date" };

// now we take row from above to be the Row object where we seek our headers
int last = row.getLastCellNum();
for (int c = row.getFirstCellNum(); c < last; c++) {
    int h = 0;
    // check if the cell at (c + h) has the required value
    for (; h < headers.length && c + h < last; h++) {
        if (!headers[h].equals(row.getCell(c + h).getStringCellValue())) {
            break; // if the cell value differs from our header
        }
    }
    if (h == headers.length) // this means the break was never invoked 
        return c; // found it
}
return -1; // not found

Answer 2

if you want to skip column you can use

while (cellsInRow.hasNext()) {
     Cell currentCell = cellsInRow.next();

     if (currentCell.getColumnIndex() >= 3 && currentCell.getColumnIndex() <= 6) {
         continue;
     }
         }

to skip column 4, 5, 6 and 7 from excel file. index starts at 0