Declaration:
public static DataHashMap<String, List<String> XLSMap = null;
package frames;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import org.testng.Assert;
public class DataHashMap<K,V> extends HashMap{
@Override
public List<String> get(Object key){
List<String> ls = (List<String>)(super.get(key));
if(ls.isEmpty())
Assert.fail("Value for " + key + "is not found, Please check xls sheet");
System.out.println("TestData: " + ls);
return ls;
}
}
//List<String> testData = Config._XLSMap.get(testDataID);
Call:
_XLSMap = Utils.createTestDataMap(testDataLocation + "\\<FILENAME>.xls");
Function:
import org.apache.commons.io.FileUtils;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
DataHashMap<String, List<String>> map = new DataHashMap<String, List<String>>();
try {
FileInputStream file = new FileInputStream(xlsFileName);
//Get the workbook instance for XLS file
HSSFWorkbook workbook = new HSSFWorkbook(file);
//Get first sheet from the workbook
HSSFSheet sheet = workbook.getSheetAt(0);
//Iterate through each rows from first sheet
Iterator<Row> rowIterator = sheet.iterator();
//Read header record
Row row = rowIterator.next();
int columnCount = row.getPhysicalNumberOfCells();
while(rowIterator.hasNext()) {
row = rowIterator.next();
List<String> values = new ArrayList<String>();
//For each row, iterate through each columns
//Iterator<Cell> cellIterator = row.cellIterator();
String testCaseId = null;
int cellIndex = 0;
String cellValue = null;
for(cellIndex=0;cellIndex<columnCount;cellIndex++)
{
Cell cell = row.getCell(cellIndex);
if (cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK) {
values.add("");
} else {
if (cellIndex == 0) {
testCaseId = cell.getStringCellValue();
} else {
cellValue = cell.getStringCellValue();
if (cellValue.contains("#")){
cellValue= Config.getData(cellValue);
}
values.add(cellValue);
}
}
}
map.put(testCaseId, values);
//System.out.println(map.get(testCaseId));
}
file.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
//System.out.println("Map: " + map);
return map;
}