CODEWITHSUNDEEP
javaexcelapache-poi
SDS
SDS

Reputation: 828

Get cell index from cell value, Apache POI

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.

Upvotes: 9

Views: 61389

Answers (3)

Karthik P
Karthik P

Reputation: 53

public CellAddress searchStringInXslx(String string) throws IOException{
            FileInputStream inputStream = new FileInputStream("Books.xlsx");
            Workbook workbook = new XSSFWorkbook(inputStream);
            Sheet firstSheet = workbook.getSheetAt(0);
            Iterator<Row> iterator = firstSheet.iterator();
            CellAddress columnNumber=null;

            while(iterator.hasNext()){
                 Row nextRow = iterator.next();
                 Iterator<Cell> cellIterator = nextRow.cellIterator();
                 while (cellIterator.hasNext()) {
                     Cell cell = cellIterator.next();
                     if(cell.getCellType()==Cell.CELL_TYPE_STRING){ 
                         String text = cell.getStringCellValue();
                          if (string.equals(text)) {
                             columnNumber=cell.getAddress();
                             break;
                          }
                        }
                     }
            }
            workbook.close();
            return columnNumber;
     }

Upvotes: 0

Vladimir
Vladimir

Reputation: 4850

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)

Upvotes: 3

Gagravarr
Gagravarr

Reputation: 48326

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!

Upvotes: 11

Related Questions