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

Question

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.

Maybe a duplicate [Link] (http://stackoverflow.com/questions/9049995/java-poi-how-to-find-an-excel-cell-with-a-string-value-and-get-its-position-ro) — MrT, Aug 06 '15 at 11:48
You can use a simple counter that increases upon each `it.next()` while loop iteration. — insidesin, Aug 06 '15 at 11:51
`field1 == "XXX"`is not going to work the way you probably expect it to. Use `equals` to compare. — f_puras, Aug 06 '15 at 17:58
@f_puras The code will also fail for non-string cells (eg numeric ones). [My answer](http://stackoverflow.com/a/31859798/685641) has a fix for both those issues :) — Gagravarr, Aug 06 '15 at 19:28
@Gagravarr I see. Strange you're getting downvoted for an answer which looks reasonable to me... — f_puras, Aug 06 '15 at 20:02
@f_puras Such is the joy of the internet - an answer with clear problems gets upvotes, and one which works + fixes the next two problems the OP will hit, written by one of the people who helps maintain the library in question, gets downvoted... :( — Gagravarr, Aug 06 '15 at 21:08

Gagravarr · Accepted Answer · 2015-08-06T16:35:57.267

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

Thank a lot @Gagravarr. Your solution woks perfectly :) – Tiny Rick Aug 07 '15 at 05:55 — Tiny Rick, Aug 07 '15 at 05:55

score -1 · Answer 2 · edited May 23 '17 at 11:47

-1

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.

edited May 23 '17 at 11:47

Community

1
1

answered Aug 06 '15 at 11:55

insidesin

727
1
7
25

The above method works like a charm for getting the row number. Although for finding the column number of the cell has a small problem since the excelsheet has empty columns among. Is there a way to overcome that?? – Tiny Rick Aug 06 '15 at 13:03
Woops. I accidentally iterated the wrong thing. Iterate the cell rather than row! My mistake! Updated now. :) – insidesin Aug 06 '15 at 13:21
1

I realized the issue before itself. Thanks anyway. But now the problem is cellIter.next() method is not taking into account the empty cells. So the empty columns are left uncounted. Any workaround for this? – Tiny Rick Aug 06 '15 at 13:33
I'm honestly not sure sorry, it's not an ideal solution that's for sure. – insidesin Aug 06 '15 at 13:44
It's alright. Thank you so much for ur time. I deeply appreciate it insidesin :) – Tiny Rick Aug 06 '15 at 13:46
The iterator skips over blank cells, so this solution is incorrect as it'd give the wrong column number for cells after that point – Gagravarr Aug 06 '15 at 15:22
It's not an incorrect solution. Just not the solution to that particular context. – insidesin Aug 06 '15 at 15:32
It is an incorrect solution, you claim that an iterator index will give you the column, but in many cases it won't do as the iterator skips blank cells. The correct solution is to ask the cell what column it's in! – Gagravarr Aug 06 '15 at 16:36
Nope, it was not. Unfortunate that you would think that way though. :( – insidesin Aug 06 '15 at 18:56
1

Your answer will only work if the string in question is in any cells before blank ones, and therefore is not a general answer as it'll fail in any case with blank cells before the needed one, and as the OP has pointed out won't work for them. There is a simple way to do it that doesn't have your issues! Your code will also fail to match the string (== doesn't do what you think for strings), and will fail for non-string cells (eg numeric ones). That's quite a lot wrong :( – Gagravarr Aug 06 '15 at 19:30
Correct. Which is a solution to those tables with no blank cells. Glad you agree with me finally. – insidesin Aug 06 '15 at 22:21

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

2 Answers2