Get cell index from cell value, Apache POI

Question

Environment    Status    Version    PatchNumber
Windows        Live      1.0        2
Unix           Live      2.0        4
Mac            Live      1.3        8

If I have the above shown data in an excel, how do I access the cellNumber of PatchNumber using the text

XSSFRow row = (XSSFRow) rows.next();
I would like to access row.getCellNumber("PatchNumber"); //Note this method does not exist in Apache POI.

Answer 1

Isn't this what you want?

row.getCell(3) 

public XSSFCell getCell(int cellnum) Returns the cell at the given (0 based) index, with the Row.MissingCellPolicy from the parent Workbook.

If you want to access exactly by name - "PatchNumber" you can read header row and save an index of PatchNumber. I think the table model only defines access to cells, but doesn't provide associate access by column names. All rows are equal in model I think :-)

To get column index. I would iterate over header row and build a list of column names columns then you can use columns.indexOf("PatchNumber")

http://poi.apache.org/apidocs/org/apache/poi/xssf/usermodel/XSSFRow.html#getCell(int)

Answer 2

I think I understand what you're after - you want to know which column contains the word "Patch" in it's first row? If so, all you need to do is something like:

Sheet s = wb.getSheetAt(0);
Row r = s.getRow(0);

int patchColumn = -1;
for (int cn=0; cn<r.getLastCellNum(); cn++) {
   Cell c = r.getCell(cn);
   if (c == null || c.getCellType() == Cell.CELL_TYPE_BLANK) {
       // Can't be this cell - it's empty
       continue;
   }
   if (c.getCellType() == Cell.CELL_TYPE_STRING) {
      String text = c.getStringCellValue();
      if ("Patch".equals(text)) {
         patchColumn = cn;
         break;
      }
   }
}
if (patchColumn == -1) {
   throw new Exception("None of the cells in the first row were Patch");
}

Just loop over the cells in the first (header) row, check their value, and make a note of the column you're on when you find your text!