Read Data from the Excel file

Question

I have 2 columns in the excel file(Attribute & Value). I have the following rows for the login page(email1-value,password1-value,e-mail2-value and password2-value). I need to store all e-mail in a single array and all password in a single array so that i can use later for selenium testing in the front-end. Suggest a solution for this in java.

Answer 1

You need to use DataProvider to pass the values from excel sheet. Try the below code.

public class DataInputProvider{

public static Object[][] getAllSheetData(String dataSheetName) {

    Object[][] data = null;

    try {
        FileInputStream fis = new FileInputStream(new File("./data/"+dataSheetName+".xlsx"));
        XSSFWorkbook workbook = new XSSFWorkbook(fis);
        XSSFSheet sheet = workbook.getSheetAt(0);   

        // get the number of rows
        int rowCount = sheet.getLastRowNum();

        // get the number of columns
        int columnCount = sheet.getRow(0).getLastCellNum();
        data = new String[rowCount][columnCount];


        // loop through the rows
        for(int i=1; i <rowCount+1; i++){
            try {
                XSSFRow row = sheet.getRow(i);
                for(int j=0; j <columnCount; j++){ // loop through the columns
                    try {
                        String cellValue = "";
                        try{
                            cellValue = row.getCell(j).getStringCellValue();
                        }catch(NullPointerException e){

                        }

                        data[i-1][j]  = cellValue; // add to the data array
                    } catch (Exception e) {
                        e.printStackTrace();
                    }               
                }

            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        fis.close();
        workbook.close();
    } catch (Exception e) {
        e.printStackTrace();
    }

    return data;


}

}