CODEWITHSUNDEEP
javaexceleclipse
sara
sara

Reputation: 3

read multiple excel sheet selenium-webdriver, java, eclipse

I want to run selenium-webdriver-java-eclipse, using excel file contains multiple excel sheets with different name(sheet1,sheet2,sheet3,...), i need a for loop help me to do that and read from this sheets. public class ExcelDataConfig {

XSSFWorkbook wb;
XSSFSheet sheet = null;

public ExcelDataConfig(String Excelpath) throws IOException {
    // TODO Auto-generated method stub

    try {
        File file = new File(Excelpath);

        // Create an object of FileInputStream class to read excel file

        FileInputStream fis = new FileInputStream(file);
        wb = new XSSFWorkbook(fis);

    } catch (Exception e) {

    }
}

public String GetData(int sheetNumber, int Row, int Column) {

    Iterator<Row> rowIt=sheet.rowIterator();

    DataFormatter formatter = new DataFormatter();
    XSSFCell cell = sheet.getRow(Row).getCell(Column);

    String data = formatter.formatCellValue(cell);
    return data;
}

public int GetRowCount(String sheetNumber) {

    int row = wb.getSheet(sheetNumber).getLastRowNum();

    row = row + 1;

    return row;

}

}

Upvotes: 0

Views: 1851

Answers (2)

rijin john
rijin john

Reputation: 11

try something like this, it is working for me you need to add the sheet numbers and cell numbers at the places of k and j

enter code here

    String filePath="C:\\Users\\USER\\Desktop\\Book1.xlsx";// file path
    FileInputStream fis=new FileInputStream(filePath);
    Workbook wb=WorkbookFactory.create(fis);
    ArrayList<String> ls=new ArrayList<String>();
    for(int k=0; k<=3;k++)//k =sheet no
    {
        Sheet sh=wb.getSheetAt(k);
        System.out.println(sh);
//      int count=0;
        for(int i=0;i<=sh.getLastRowNum();i++)
        {
            System.out.println("row no:"+i);
            for(int j=0; j<=4;j++)//j=column no
            {
                try {
                 String values=sh.getRow(i).getCell(j).getStringCellValue().trim();
                 System.out.println(values);

//condetions

                /* if(values.contains("condtn1"))
                    {
                        System.out.println("Value of cell "+values+" ith row "+(i+1));
                        ls.add(values);
                        count++;
                    }   
                 if(values.contains("condn2"))
                    {
                        System.out.println("Value of cell "+values+" ith row "+(i+1));
                        ls.add(values);
                        count++;
                    }*/ 
                 }catch(Exception e){

                 }

            }

        }

    }
}

}

Upvotes: 1

Chuchoo
Chuchoo

Reputation: 842

Please try writing similar to something like this:

for (int i = startRow; i < endRow + 1; i++) {
                for (int j = startCol; j < endCol + 1; j++) {
                    testData[i - startRow][j - startCol] = ExcelWSheet.getRow(i).getCell(j).getStringCellValue();
                    Cell cell = ExcelWSheet.getRow(i).getCell(j);
                    testData[i - startRow][j - startCol] = formatter.formatCellValue(cell);
                }
            }

Terms used in method are pretty self explanatory. Let us know if you get stuck or need more info.

Upvotes: 0

Related Questions