Read Data from Excel and Convert into HashMap for Cucumber framework

Question

I'm trying to read an Excel spread sheet, where I maintain all the properties file details like: browser name, url, user name & password, etc.

I'm storing the data in key-value pairs, so when i try to run this class, i'm not getting any results. My class shows terminated in the console.

If can anyone please review this code and let me know what mistakes you can find, i will rectify.

I'm trying to run this excel example in my cucumber project.

public class ExcelUtil {

    public static String ExcelSheetName = "./src/test/java/com/codeElan/TestData/Config.xlsx";
    public static FileInputStream fis;
    public static XSSFWorkbook workbook;
    public static XSSFSheet sheet;
    public static XSSFRow row;

    public static void loadExcel() {

        System.out.println("Load Excel Sheet.........");
        File file = new File(ExcelSheetName);

        try {
            fis = new FileInputStream(file);
            workbook = new XSSFWorkbook(fis);
            sheet = workbook.getSheet("LoginData");
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }

        //sheet = workbook.getSheet("LoginData");

        try {
            fis.close();
        } catch (IOException e) {
            e.printStackTrace();
        }   
    }

    public static Map<String,Map<String,String>> getDataMap() { 
        if(sheet==null){
            loadExcel();
        }

        Map<String, Map<String,String> > superMap= new HashMap<String, 
        Map<String,String> >();
        Map<String,String> map = new HashMap<String,String>();

        for(int i=1; i<sheet.getLastRowNum()+1; i++){
            row = sheet.getRow(i);
            String keyCell = row.getCell(0).getStringCellValue();

            int collNum = row.getLastCellNum();
            for(int j=1;j<collNum;j++){
                String value=row.getCell(j).getStringCellValue();
                map.put(keyCell, value);
            }

            superMap.put("MASTERDATA", map);
        }   
        return superMap;        
    }

    public static String getValue(String key) {
        Map<String,String> mapValue = getDataMap().get("MASTERDATA");
        String retValue = mapValue.get(key);

        return retValue;
    }

    public static void main(String[] args){
        System.out.println(getValue("Browser"));
    }
}

Answer 1

Here is the sample script that I have created to resolve your issue.Now I need to add few points here if you are planning to use this thing in the live project then you need to verify the type of the data coming from the excel cell otherwise if the data is of different type then probably it will throw the error at runtime. If you really want to parameterize your project with something like key value pair data then you should look into the properties file as well.

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;

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

public class ExcelUtil {

    public static String ExcelSheetName = "Book1.xlsx";
    public static FileInputStream fis;
    public static XSSFWorkbook workbook;
    public static XSSFSheet sheet;
    public static XSSFRow row;

    public static void loadExcel() {

        System.out.println("Load Excel Sheet.........");
        File file = new File(ExcelSheetName);

        try {
            fis = new FileInputStream(file);
            workbook = new XSSFWorkbook(fis);
            sheet = workbook.getSheet("LoginData");

        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }


        try {
            fis.close();
        } catch (IOException e) {
            e.printStackTrace();
        }   
    }

    public static Map<String,Map<String,String>> getDataMap() { 
        if(sheet==null){
            loadExcel();
        }

        Map<String, Map<String,String>> parentMap = new HashMap<String, Map<String,String>>();
        Map<String, String> childMap = new HashMap<String, String>();

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

        while( rowIterator.hasNext() )
        {
            Row row = rowIterator.next();
            childMap.put(row.getCell(0).getStringCellValue(), row.getCell(1).getStringCellValue());
        }

        parentMap.put("MASTERDATA", childMap);

        return parentMap;


    }

    public static String getValue(String key) {
        Map<String,String> mapValue = getDataMap().get("MASTERDATA");
        String retValue = mapValue.get(key);

        return retValue;
    }

    public static void main(String[] args){
        System.out.println(getValue("Password"));
    }
}