TestNG parameterized test using XLS and test data model

Lets say there is a XLS file with test data in following format:-

UserName	Password	Email
abc	P1	abc@gmail.com
xyz	P2	xyz@gmail.com

Create model for the above XLS:-

public class TestModel {
       private String userName,password,eMail;
       public TestModel (Object[] column){
  userName = (String)column[0];
  password = (String)column[1];
  eMail= (String)column[2];
 }
      /* 
         Getters here...
     */
}

Create parameterized test:-

@Test(dataProvider = "xlsReader")
public void parameterizedTest(TestModel testModel ) throws Exception {
      /* 
        Perform test using model
     */
}

Create data provider:-

@DataProvider
public static Object[][] xlsReader() throws Exception {
       return SpreadsheetReader.getXlsData("XLS_FILE", TestModel.class);
}

Now the class SpreadsheetReader:- 

import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import java.io.FileNotFoundException;
import java.io.InputStream;
import java.lang.reflect.Constructor;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class SpreadsheetReader {
 
 public static Object[][] getXlsData(final String xlFile, Class<?> modelClass) throws Exception {
  InputStream inputStream = getFileInputStream(xlFile);
  Object[][] data = readSpreadsheet(inputStream, modelClass);
  inputStream.close();
  return data;
 }
 
 private static InputStream getFileInputStream(String filename) throws FileNotFoundException
 {
  InputStream inputStream = null;
  inputStream = SpreadsheetReader.class.getClassLoader().getResourceAsStream(filename);

  if (inputStream == null)
  {
   throw new FileNotFoundException("File '" + filename
     + "' not found in the classpath");
  }

  return inputStream;
 }
 
 private static Object[][] readSpreadsheet(final InputStream excelFile, Class<?> modelClass)                                                                                                                             throws Exception{

  HSSFWorkbook workbook = new HSSFWorkbook(excelFile);
  Sheet sheet = workbook.getSheetAt(0);
  int numberOfColumns = countNonEmptyColumns(sheet);
  List<Object[]> rows = new ArrayList<Object[]>();
  List<Object> rowData = new ArrayList<Object>();
  for (Row row : sheet) {
   if (isEmpty(row)) {
    break;
   } else if(row.getRowNum() > 0){
    rowData.clear();
    for (int column = 0; column < numberOfColumns; column++) {
     Cell cell = row.getCell(column);
     rowData.add(getCellObject(workbook, cell));
    }
    Object[] objAr= rowData.toArray();
    Constructor<?> cons = modelClass.getConstructor( new Class[]                                                                                                                                           {Object[].class});

    Object o = cons.newInstance(new Object[]{objAr});
    Object[] obj = new Object[]{o};
    rows.add(obj);
   }
  }
  return convertListToArray(rows);
 }
 
 private static Object[][] convertListToArray(List<Object[]> rows) {

  Object[][] objArr = new Object[rows.size()][];
  for (int i = 0; i < rows.size(); i++) {
   objArr[i] = new Object[]{};
   objArr[i] = rows.get(i);
  }
  return objArr;
 }
 
 private static boolean isEmpty(final Row row) {
  Cell firstCell = row.getCell(0);
  boolean rowIsEmpty = (firstCell == null) || (firstCell.getCellType() ==                                                                                                                                  Cell.CELL_TYPE_BLANK);
  return rowIsEmpty;
 }
 
 private static int countNonEmptyColumns(final Sheet sheet) {
  Row firstRow = sheet.getRow(0);
  return firstEmptyCellPosition(firstRow);
 }

 private static int firstEmptyCellPosition(final Row cells) {
  int columnCount = 0;
  for (Cell cell : cells) {
   if (cell.getCellType() == Cell.CELL_TYPE_BLANK) {
    break;
   }
   columnCount++;
  }
  return columnCount;
 }

 private static Object getCellObject(final HSSFWorkbook workbook, final Cell cell) {
  Object cellValue = null;
  if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
   cellValue = cell.getRichStringCellValue().getString();
  } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
   cellValue = getNumericCellValue(cell);
  } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
   cellValue = cell.getBooleanCellValue();
  }
  return cellValue;
 }
 
 private static Object getNumericCellValue(final Cell cell) {
  Object cellValue;
  if (DateUtil.isCellDateFormatted(cell)) {
   cellValue = new Date(cell.getDateCellValue().getTime());
  } else {
   cellValue = cell.getNumericCellValue();
  }
  return cellValue;
 }
}

That's it :)