Selenium DataProvider - How to add all the values from Excel Sheet in HashMap

Question

I have written some test scripts to which i need to send test data to execute the scripts. I have written a code to iterate excel and find the given string along with the row and column number in which the String is present. Here is my testData format which I wish to use:

TestCase_ID || File Format || File Name || File Path || .... n
===============================================================
TC_01       || Document    || selenium.pdf || C://selenium.pdf
===============================================================

Here is the excel iteration code I'm using:

  public class ExcelFileData {

    private String fileFormat; 

    private String fileName; 

    String filepath;

    public static void getCellData(String testCaseName) {

        try {

            FileInputStream file = new FileInputStream(new File("C://TestData_01.xlsx")); 

            @SuppressWarnings("resource")

            XSSFWorkbook workbook = new XSSFWorkbook(file); 

            XSSFSheet sheet = workbook.getSheetAt(0); 

            Iterator<Row> rowIterator = sheet.iterator(); 

                while (rowIterator.hasNext()) { 

                    Row row = rowIterator.next(); 

                    Iterator<Cell> cellIterator = row.cellIterator(); 

                while (cellIterator.hasNext()) { 

                    Cell cell = cellIterator.next(); 

                    if(cell.getCellType() == CellType.STRING && cell.getStringCellValue().equalsIgnoreCase(testCaseName)) {

                        System.out.println(cell.getStringCellValue());

                        System.out.println("search key  at Col: "+cell.getColumnIndex());

                        System.out.println("search key Found at Row: "+cell.getRowIndex());

                        }else {

                            break;
                        }
                    } 

                    System.out.println(""); 
                }       

            }catch (Exception e) {

                e.printStackTrace();
        }

    }

    Map<String, ExcelFileData> excelDataMap = new HashMap();

    public static void main(String args[]) {

        ExcelFileData.getCellData("TC_01");

    }

}

Output:

TC_01
search key  at Col: 0
search key Found at Row: 1

I wish to find the Data with respect to given test case. As in , I would pass testcase id(i.e TC_01) and then wish to iterate all the columns of this particular row. I'm new to programming, hence would like to know how can I put all the data while iterating through excel in HashMap so that I can use that data as input to my testscripts.

HashMap can keep only key and value not all the data from the excel sheet. — Sambit, May 21 '19 at 17:25
@Sambit: Thanks! Could you please advise what would be a good way to deal with the scenario i have mentioned? — Kaustubh, May 21 '19 at 17:28
This question has nothing to do with Selenium so I removed those tags. Add a tag for the Excel library you are using, ApachePOI? — JeffC, May 23 '19 at 21:27

Sambit · Accepted Answer · 2019-05-21T19:32:33.577

As discussed, I provide below the working code for you.

public class ExcelFileData {
  private String fileFormat;
  private String filePath;

  public String getFileFormat() {
    return fileFormat;
  }

  public void setFileFormat(String fileFormat) {
    this.fileFormat = fileFormat;
  }

  public String getFilePath() {
    return filePath;
  }

  public void setFilePath(String filePath) {
    this.filePath = filePath;
  }
}

Test class to verify

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.Map;

public class TestExcelReader {
  public static void main(String[] args) throws Exception {
    FileInputStream file =
        new FileInputStream(
            new File(
                "Some Location in windows\\TestData_01.xlsx"));
    XSSFWorkbook workbook = new XSSFWorkbook(file);
    XSSFSheet sheet = workbook.getSheetAt(0);
    Iterator<Row> rowIterator = sheet.iterator();

    Map<String, ExcelFileData> excelDataMap = new LinkedHashMap<>();

    while (rowIterator.hasNext()) {
      Row row = rowIterator.next();
      Cell testCaseCell = row.getCell(0); // Test Case Name or Test Case Id

      Cell fileFormatCell = row.getCell(1);
      Cell filePathCell = row.getCell(2);

      ExcelFileData excelFileData = new ExcelFileData();
      if (fileFormatCell != null) excelFileData.setFileFormat(fileFormatCell.getStringCellValue());
      if (filePathCell != null) excelFileData.setFilePath(filePathCell.getStringCellValue());
      if (testCaseCell != null) {
        excelDataMap.put(testCaseCell.getStringCellValue(), excelFileData);
      }
    }

    excelDataMap.forEach(
        (key, value) -> {
          System.out.println("Key as test case Id : " + key);
          System.out.println("File Format : " + value.getFileFormat());
          System.out.println("File Path : " + value.getFilePath());
        });
  }
}

I have used java 8 along with Apache Poi version 4.1.0 and poi-ooxml 4.1.0.

I am unable to format the code here, somebody can help here. — Sambit, May 21 '19 at 17:43
Hi Sambit, I would like to share my code with you to verify if i'm on the right path. How can i share it with you? Please advise. — Kaustubh, May 21 '19 at 18:35
Please check, I have updated my question with the new code for your review. — Kaustubh, May 21 '19 at 18:38
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/193720/discussion-between-kaustubh-and-sambit). — Kaustubh, May 21 '19 at 18:39

Selenium DataProvider - How to add all the values from Excel Sheet in HashMap

1 Answers1