0

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

}
  • 1
    Good question.. – Selenium Sep 22 at 10:31
  • Where are you stuck? – Niels van Reijmersdal Sep 22 at 11:25
  • @NielsvanReijmersdal I am unable to read the Data based on the test case and column name (Key value pair). Above is the logic what I wrote. I just want to read data based on the Test case number. – forumqaa Sep 22 at 11:33
  • Hi @forumqaa i can suggest you answer by using DataProvider, but in that case you need to prepare different sheets for each test cases. – Upkar Singh Sep 23 at 5:41
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;
}

}

  • 1
    it was helped me and near to what I was expected. I am not sure who is down voting, if they can not help then do not down vote. – forumqaa Sep 24 at 2:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.