Selenium excel read and write to find row number

Question

In my program I want to find the row number in the excel sheet matching the string I have passed as argument . It works fine for first and second row but problem is with the next rows. My code to find row number is as below :

    public int findrownum(String sName, String value, int cNum) throws Exception{
        File excel = new File(filepath);
        FileInputStream fis = new FileInputStream(excel);
        XSSFWorkbook wb = new XSSFWorkbook(fis);
        XSSFSheet ws = wb.getSheet(sName);
        boolean check = true;
        int i=0;
        while (check){
            XSSFRow rowH = ws.getRow(i);
            XSSFCell cell = rowH.getCell(cNum);
            String cellvalue = cellToString(cell);
            if (cellvalue.equals(value)){
                check = false;
            }
            else {
                i = i+1;
            }
        }       

        return i;
    }

}

I want to read third row that is the string with name registration from the excel

Sl No test case name result timestamp 1 login Pass 03/03/2014 12:11:43 PM 2 Registration

Please let me know what changes needs to be done in the code .

Thanks

I used the similar logic as mentioned by @eric in JUNIT now i am able to find the row number .But now its giving error while i try to read the data using this row number . My code to read data is as below . Please let me know what changes needs to be done public String dataread(String sName, int rNum, String cName) throws Exception{ File excel = new File(filepath); FileInputStream fis = new FileInputStream(excel); XSSFWorkbook wb = new XSSFWorkbook(fis); XSSFSheet ws = wb.getSheet(sName); XSSFRow rowH = ws.getRow(rNum-1); int totalRows = ws.getLastRowNum();

    int i =0;
    for(i=0;i<=totalRows;i++)

    {
        XSSFCell cell = rowH.getCell(i);
        String value = cellToString(cell);
        if (value.equals(cName)){
             System.out.println(i);
             break;
        }

    }
    XSSFRow row = ws.getRow(rNum);
    XSSFCell cell = row.getCell(i);
    String value = cellToString(cell)          return value;
}

Answer 1

In general From this Documentation you can use the getHeight() to get in which your cursor instead of writing up your own loop. Obviously this would reduce the execution time as well. Also the code which you have written could have caused the exception,as there is no more physical rows.

ws.getRow(i); can cause a fatal error if i>height of the last row

Answer 2

Hope the following code helps. The assumption is the data in the cell is string data. Also this is with apache poi api.

public static String getcellValue(int testRowNo, int colNo)
    {
        String projectPath = System.getProperty("user.dir");
        String excelPath = projectPath + "/TestSet.xlsx";
        File excel = new File(excelPath);
        FileInputStream fis = null;
        Workbook workBook = null;
        String cellValue = null;
        try
        {
            fis = new FileInputStream(excel);
            workBook = WorkbookFactory.create(fis);
            Sheet workSheet = workBook.getSheet(sheetName);
            int totalRows = workSheet.getLastRowNum();
            Row row = null;
            cellValue  = workSheet.getRow(testRowNo).getCell(colNo).getStringCellValue();


        } catch (InvalidFormatException e)
        {
            e.printStackTrace();
        } catch (IOException e)
        {
            e.printStackTrace();
        }finally
        {
            try
            {
                fis.close();
            } catch (IOException e)
            {
                e.printStackTrace();
            }
        }
        return cellValue;
    }

public static int getrowNumber(String sheetName, String cellData)
    {
        String projectPath = System.getProperty("user.dir");
        String excelPath = projectPath + "/TestSet.xlsx";
        File excel = new File(excelPath);
        FileInputStream fis = null;
        Workbook workBook = null;
        String cellValue = null;
        try
        {
            fis = new FileInputStream(excel);
            workBook = WorkbookFactory.create(fis);
            Sheet workSheet = workBook.getSheet(sheetName);
            int totalRows = workSheet.getLastRowNum();
            Row row = null;
            int testRowNo = 0;
            for(int rowNo =1; rowNo<=totalRows; rowNo++)
            {
                row = workSheet.getRow(rowNo);
                testRowNo = testRowNo +1;
                if(row.getCell(0).getStringCellValue().equalsIgnoreCase(cellData))
                {
                    break;
                }
            }


        } catch (InvalidFormatException e)
        {
            e.printStackTrace();
        } catch (IOException e)
        {
            e.printStackTrace();
        }finally
        {
            try
            {
                fis.close();
            } catch (IOException e)
            {
                e.printStackTrace();
            }
        }
        return testRowNo;
    }