How to read the excel (Later enter test data in application based on particular test script name)



  • I need your help, I want to read test data based on the Test script. And after reading data it should enter in the application.

    I am using selenium Java + Apache POI

    Below is the code please help.

    enter image description here

      package mysrc;
    
    import java.io.FileInputStream;
    import java.io.IOException;
    import java.util.HashMap;
    import java.util.Map;
    
    import org.apache.poi.ss.usermodel.Cell;
    import org.apache.poi.ss.usermodel.Row;
    import org.apache.poi.ss.usermodel.Sheet;
    import org.apache.poi.ss.usermodel.Workbook;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    
    
    public class ReadExcel {
    
        public static Map<String,  Map<String, String>> setMapData() throws IOException {
    
            String path = "G:\\Selenium_TestNG_RnD\\MyFW\\testData\\demo.xlsx";
    
            FileInputStream fis = new FileInputStream(path);
    
            @SuppressWarnings("resource")
            Workbook workbook = new XSSFWorkbook(fis);
    
            Sheet sheet = workbook.getSheetAt(0);
    
            int lastRow = sheet.getLastRowNum();
    
            Map<String, Map<String, String>> excelFileMap = new HashMap<String, Map<String,String>>();
    
            Map<String, String> dataMap = new HashMap<String, String>();
    
            //Looping over entire row
            for(int i=0; i<=lastRow; i++){
    
                Row row = sheet.getRow(i);
                Cell cell = row.getCell(i);
                //0th Cell as Key
                /* Cell keyCell = row.getCell(0);
    
               //1st Cell as Value
               Cell valueCell = row.getCell(1);
    
               String value = valueCell.getStringCellValue().trim();
               String key = keyCell.getStringCellValue().trim();*/
    
                String value = cell.getStringCellValue().trim();
                String key = cell.getStringCellValue().trim();
    
                //Putting key & value in dataMap
                dataMap.put(key, value);
    
                System.out.println(dataMap.put(key, value));
    
                //Putting dataMap to excelFileMap
                excelFileMap.put("TestData", dataMap);
            }
    
            //Returning excelFileMap
            return excelFileMap;
    
        }
    
        //Method to retrieve value
        public String getMapData(String key) throws IOException{
    
            Map<String, String> m = setMapData().get("TestData");
            String value = m.get(key);
    
            return value;
    
        }
    
        public static void main(String[] args) throws IOException {
    
            ReadExcel s = new ReadExcel();
            s.getMapData("EmailID");
        }
    
    }
    


  • Hi @Forumqaa Below code may help for you.

    Please change the logic as per you.

    public class Sac {
    
    public static void main(String[] args) throws IOException {
    
        Sac s = new Sac ();
        s.getData("TC_001");
    }
    
    public ArrayList<String> getData(String testCaseName) throws IOException{
    
        ArrayList<String> a = new ArrayList<String>();      
        FileInputStream fis = new FileInputStream("G:\\Selenium_TestNG_RnD\\MyFW\\testData\\demo.xlsx");
        @SuppressWarnings("resource")
        XSSFWorkbook workbook =new XSSFWorkbook(fis);
    
        int sheets =workbook.getNumberOfSheets();
    
        for (int i = 0; i < sheets; i++) {
    
            if(workbook.getSheetName(i).equalsIgnoreCase("TestData")){
    
                XSSFSheet sheet = workbook.getSheetAt(i);
    
                Iterator<Row> row = sheet.iterator();
                Row firstRow =row.next();
                Iterator<Cell> cell =firstRow.cellIterator();
                int k=0;
                int coloumn =0;
    
                while(cell.hasNext()){
    
                    Cell value =cell.next();
                    if(value.getStringCellValue().equalsIgnoreCase("TestCases")){
    
                        coloumn =k;
                    }
    
                    k++;
                }
            //  System.out.println(coloumn);
    
                while(row.hasNext()){
    
                    Row r =row.next();
                    if(r.getCell(coloumn).getStringCellValue().equalsIgnoreCase(testCaseName)){
    
                        Iterator<Cell> cy =r.cellIterator();
                        while(cy.hasNext()){
    
                            Cell c =cy.next();
                            if(c.getCellTypeEnum()==CellType.STRING){
    
                                a.add(c.getStringCellValue());
    
                                System.out.println(c.getStringCellValue());
                            }
                            else{
                                a.add(NumberToTextConverter.toText(c.getNumericCellValue()));
                                //a.add(c.getNumericCellValue());
    
                            }
    
                        }
                    }
    
                }
    
            }
    
        }
    
        return a;
    }
    

    }



Suggested Topics

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