Selenium - Data Driven Testing

In Automation, we need to runs scripts to test the same functionality with different types of data. This data is usually stored in an excel file and accessed via the script to enter in the Application Under Test.

In QTP, this is done using the DataTable. 

In Selenium we can use the Apache POI API to do read an excel workbook and write the output to the excel file.

Using the API is quite simple if you understand the basics.
POI - Poor Obfuscation Implementation. This name was humorously given  because Microsoft code was deliberately made difficult to reverse engineer but still it was reverse engineered.

POIFS - Poor Obfuscation Implementation File System. Used to access the input file.

HSSF - Horrible Spreadsheet Format This is used to read and write .xls files

XSSF - XML Spreadsheet Format This is used to read and write .xlsx files.

FileInputStream fs=new FileInputStream("C:\\Users\\Krishna\\selenium workspace\\Data\\firstone.xls");
POIFSFileSystem poi=new POIFSFileSystem(fs);
HSSFWorkbook hw=new HSSFWorkbook(poi);
HSSFSheet hs=hw.getSheet("Global");
Object data[][]=new Object[2][2];
System.out.println(hs.getLastRowNum());// last row number in excel
System.out.println(hs.getPhysicalNumberOfRows());//number of physically defined rows
System.out.println(hs.getRow(0).getLastCellNum()); // last cell number in row
System.out.println(hs.getRow(0).getPhysicalNumberOfCells()); // number of physically defined cells
for(int i=1;i<=hs.getLastRowNum();i++){
for(int j=0;j<hs.getRow(0).getLastCellNum();j++)
{
data[i-1][j]=hs.getRow(i).getCell(j).toString();
System.out.println(data[i-1][j]);
}
}
fs.close();

Use with Junit:
@Parameters
public static Collection<Object[]>(){
return Arrays.asList(data);
//data should be declared static in this case
}

The above code can be used to read data from excel sheet and store it in the object data array.
To understand the code remember the following hierarchy.

Access File -> Open the accessed file using POIFSFileSystem class -> Access the HSSFWorkBook workbook in the POIFSFileSystem -> Access the HSSFSheet sheet in the workbook using getSheet(sheetname) -> Access the row in the sheet using getRow(rownumber) -> Access the cell in the row using getCell(cellnumber)

getLastRowNum () gets the number last row on the sheet. Owing to idiosyncrasies in the excel file format, if the result of calling this method is zero, you can't tell if that means there are zero rows on the sheet, or one at position zero. For that case, additionally call getPhysicalNumberOfRows() to tell if there is a row at position zero or not.