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

Karthikeyan · Accepted Answer · 2014-03-03 10:26:49Z

0

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

edited Mar 3, 2014 at 10:26

answered Mar 3, 2014 at 10:21

Karthikeyan

2,7005 gold badges32 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Vinay · Accepted Answer · 2014-03-06 10:54:05Z

0

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

edited Mar 6, 2014 at 10:54

answered Mar 3, 2014 at 11:06

Vinay

7544 silver badges12 bronze badges

3 Comments

user3356119 Over a year ago

I used the similar logic 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

Vinay Over a year ago

Did you use the method i have posted? This will return the row number and you can use it and probably create another method which takes the row and column number as argument and reads data from the cell.

user3356119 Over a year ago

Yes I have used the same method . Can you let me know the method to read the data using this row number . The logic I am using is not working .

Collectives™ on Stack Overflow

Selenium excel read and write to find row number

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related