Read Data from the Excel file



  • 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.



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


Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2