Sunday 25 October 2015

Read XLSs and Storing data in map


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;

public static DataHashMap<String, List<String>> createTestDataMap(String xlsFileName ){

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