Java POI: how to find an Excel cell with a string value and get its position (string), to use this position to find another cell

Question

I am looking for a cell in a spreadsheet that contains the row “Total”, and then use the row in which this cell should find the common value in another cell, which is always the same cell / column (10th cell in the index based to 0).

I have the following code that has no errors (syntax), but the findCell method does not return rowNum:

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); } } 

Answer 1

This fix method 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.

Answer 2

You have a semicolon after your if , which means your if will not work:

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

Even if this does not solve your problem, I think your while statement may be wrong here:

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

As far as I remember, there are other types of cells in the POI. Try to set a breakpoint on these lines and check if they have the correct CellType.