CODEWITHSUNDEEP
javaexcelapache-poi
Tiny Rick
Tiny Rick

Reputation: 276

Finding the column number of the String that needs to be found in Java using Apache POI

Suppose I need to find the String "XXX" in an Excel sheet, I am not aware of how to get the column number and row number of the cell which contains the string.

Iterator<Row> it = XXXPresentSheet.iterator();          
while (it.hasNext()) {
    Row row = it.next();                
    Iterator<Cell> cellIter = row.cellIterator();

    while (cellIter.hasNext()) {
        String field1 = cellIter.next().getStringCellValue();               
        if (field1 == "XXX") {
            System.out.println(someMethodThatReturnsColumnNumber());
        }
    }
}

I am able to traverse through the row using the cell iterator. But when I encounter the String which I want, I want to get that column number. Any help is deeply appreciated. Thanks.

Upvotes: 0

Views: 4818

Answers (2)

Gagravarr
Gagravarr

Reputation: 48326

If you ask a cell nicely, it will tell you what Column Number it is in. It will also tell you what Row Number it is in

So, just do something like:

DataFormatter fmt = new DataFormatter();
for (Row r : sheet) {
   for (Cell c : r) {
       if ("ThingToLookFor".equals(fmt.formatCellValue(cell))) {
          // Found it!
          int columnIndex = c.getColumnIndex();
          int rowNumber = c.getRowNumber();
          // Get the Excel style reference, eg C12
          String ref = ((new CellReference(rowNumber,columnIndex)).formatAsString();
       }
   }
}

By using a DataFormatter, you can get the string value to compare for any cell, not just String ones. The CellReference bit is optional, but'll help if you want to see the Excel-style reference for the cell that matched

Upvotes: 1

insidesin
insidesin

Reputation: 743

The simplest way to do this is to implement your own variable which will increase on each successful iteration of the while(it.hasNext()) loop.

Iterator<Row> it = XXXPresentSheet.iterator();  
while(it.hasNext()) { 
    int column = 0;
    Row row = it.next();                
    Iterator<Cell> cellIter = row.cellIterator();
    while(cellIter.hasNext()) {
       column++;
       String field1 = cellIter.next().getStringCellValue();                
       if(field1 == "XXX") {
           System.out.println(column);
       }
    }
}

Where column = 1 (starting column), you can increment column AFTER the first loop if you wish to start with the value column = 0

Further reading can be found in this alternative StackOverflow question.

Upvotes: -1

Related Questions