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

Question

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.

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

}

Answer 1

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

}