READING DATA FROM EXCEL FILE

       There is time when reading test data from EXCEL file is required, Consider an example where we want to read username and password from a file. In this scenario we can store the username and password in xls or xlxs file. This approach is helpful when we are doing the data driver test. The below code explains the procedure of reading a excel file


import java.io.File;
import java.io.FileInputStream;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
public class ExcelRead
{
      public static void main(String[]arg)throws Exception //Path of the xls file
          {
                 File excel = new File("F:/Offfice/Projects/ApachePOI/test.xls");
                 FileInputStream fis = new FileInputStream(excel);
                 HSSFWorkbook wb= new HSSFWorkbook (fis);
                 HSSFSheet ws = wb.getSheet("Input");
                 int rowNum = ws.getLastRowNum()+1;
                 int colNum = ws.getRow(0).getLastCellNum();
                 String[][] data = new String[rowNum][colNum];
                 for(int i=0;i<rowNum;i++)
                     {
                           HSSFRow row=ws.getRow(i);
                            for(int j=0;j<colNum;j++)
                                {
                                     HSSFCell cell =row.getCell(j);
                                     String value = cellToString(cell);
                                     data[i][j]= value;
                                     System.out.println("the value is "+value);
                                }
                     }
          }//For converting cell to string.

      public static String cellToString(HSSFCell cell)
          {
                  int type;
                 Object result = null;
                 type=cell.getCellType();
                 switch(type)
                     {
                         case 0:cell.getNumericCellValue();
                         break;
                        case 1:result = cell.getStringCellValue();
                        break;
                        default:
                         throw new RuntimeException("no support for this type of cell");
                     }
                 return result.toString();
          }
}

     Before explaining the program downloads the Apache POI jar file from the below link. 

Apache POI jar file
    

    This jar file help us to read the excel file, and add the jar file to your project. I believe you know how to add jar file to your project id you are gone through the part of configuring selenium. Once you added the jar file now we are ready for writing some code.

    Consider the java code for reading the excel file. Inside the public static void main we are writing our code. The first step we do is creating a file object and specifying the path of the excel file which we are going to read. Once we do that next we do is creating file input stream and passing the file object just we created. HSSFWorkbook is component of the Apache POI file we just downloaded, and we are creating object named wb, and we pass the file input stream object to HSSFWorkbook. From the next step onward we are dealing with the excel file. We create HSSFSheet for selecting the sheet from the excel file. wb.getSheet(“Input”); where getSheet function for selecting the sheet from the excel file, and the parameter is the name of the sheet. Now we have got the sheet to ws object. getLastRowNum(); for getting last row number of the sheet. Ultimately that will the number on total row in the sheet, and getLastCellNum() for getting the cell number, which is the total number of column. The rest of the thing is simple programming logic. getRow(“number”) for accessing the and getCell() is for reading the cell or we can say the column. The cellToString();   is for converting the data to the string, the function is defined below.

    So this is a simple program for reading a excel file. While writing the code we face several situations like managing the data associated with the test suite. For example reading a username password of several users, it’s not possible giver the data hard coded, so we go for alternative methods such as reading from excel file, reading from property file or from accessing from the data base itself.

    In the next session we are going to read the data from the database