Java POI: How to find an Excel cell with a string value and get its position (row) to use that position to find another cell

Question

I'm looking for a cell in a spreadsheet that has the string 'Total' and then use the row in which that cell is to find the total value in another cell which is always the same cell/column (the 10th cell in a 0 based index).

I have the following code, which has no errors (syntax), but the findCell method is not returning rowNum value:

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

        String fileName = "C:\\file-path\\report.xls";
        String cellContent = "Total";
        int rownr=0, colnr = 10;

        InputStream input = new FileInputStream(fileName);

        HSSFWorkbook wb = new HSSFWorkbook(input);
        HSSFSheet sheet = wb.getSheetAt(0);

        rownr = findRow(sheet, cellContent);

        output(sheet, rownr, colnr);

        finish();
    }

    private static void output(HSSFSheet sheet, int rownr, int colnr) {
        /*
         * This method displays the total value of the month
         */

        HSSFRow row = sheet.getRow(rownr);
        HSSFCell cell = row.getCell(colnr);

                System.out.println("Your total is: " + cell);           
    }

    private static int findRow(HSSFSheet sheet, String cellContent){
        /*
         *  This is the method to find the row number
         */

        int rowNum = 0; 

        for(Row row : sheet) {
            for(Cell cell : row) {

                while(cell.getCellType() == Cell.CELL_TYPE_STRING){

                    if(cell.getRichStringCellValue().getString () == cellContent);{

                            rowNum = row.getRowNum();
                            return rowNum;  
                    }
                }
            }
        }               
        return rowNum;
    }

    private static void finish() {

        System.exit(0);
    }
}   

Solution 1

This method fix is the solution to your problem:

private static int findRow(HSSFSheet sheet, String cellContent) {
    for (Row row : sheet) {
        for (Cell cell : row) {
            if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
                if (cell.getRichStringCellValue().getString().trim().equals(cellContent)) {
                    return row.getRowNum();  
                }
            }
        }
    }               
    return 0;
}

Keep in mind that your colnr is still a fixed value.

Solution 2

You have a semicolon after your if statement which means your if won't work:

if(cell.getRichStringCellValue().getString () == cellContent);{

Even if this won't resolve your problem, I think your while statement may not be proper for here;

while(cell.getCellType() == Cell.CELL_TYPE_STRING)

As far as I remember, there are other Cell types in POI. Try to put a breakpoint on these lines and check them if they have correct CellType.

Comments

[lv10]

Thank you very much. Your suggestion was right, the while statement had something wrong. I removed it and changed it for an if statement as suggested below by @msi.

[lv10]

Thank you very much @msi. The suggested code fixed the problem, it worked perfectly. I'm relatively new to programming, can ask you: 1. why you changed the while statement and 2. Why the return of the for loop is = 0. thanks again!

[msi]

When you use while loop you are assuming that all Cells at the left from 'total-cell' are filled with string. For example cells like this: | | 1| Total| will FAIL to be found with while, because while will break at value of 1. Returning 0 just in case no 'total-cell' would be found.

[msi]

For comparing two strings you should use equals method. == is used to compare primitive types, not objects. And getString() returns an Object. If you want to learn more about Strings, read about String pool and intern method. This is where you can found cases when comparing using == is correct. I have also used trim method just because user inputs like to contain some additional spaces at the end of string.

[lv10]

thanks for taking the time to explain me. I really appreciate it. I will follow your advice from now on.

[theRiley]

use derived cell to find your indexes : cell.getColumnIndex, cell.getRowIndex