Code to read data from excel file - Java Selenium Data Driven frameworks

In Data driven framework for automation testing, data is stored in external file and helper/utility classes are created to interact with the data stored in external file. Data is normally stored in excel or csv files. Storing data in excel file is convenient as data for multiple workflows/module can be stored in different sheets of the same workbook. In the last post,code explaining how to read data from a csv file was explained. Click here to understand how to read data from a csv file.

In this post, we will understand how to interact with excel file. To interact with excel file, we need to add external jar files. Two common external file to interact with excel are :

• jxl.jar - Supports only xls format.

• org.apache.poi - Support both xls and xlsx format.

Download the external Jar file and add in the build path for the project. Below code shows how to read data from excel file using jxl.jar 

package testingtest;

import java.io.File;
import java.io.IOException;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

class excelarrayclass {

 static String[][] getexceldatainArray;

 public static void main(String[] args) throws IOException, BiffException {
  // Create an array based on data in excel file
  excelarrayclass tc = new excelarrayclass();
  getexceldatainArray = tc.CreateArrayfromExcel("D:\\Testing.xls",
    "testing");
 }

 public String[][] CreateArrayfromExcel(String WorkbookName, String SheetName)
   throws BiffException, IOException {
  String[][] arrTestDta;

  Workbook workbk = Workbook.getWorkbook(new File(WorkbookName));
  Sheet wrkSheet = workbk.getSheet(SheetName);
  arrTestDta = new String[wrkSheet.getRows()][wrkSheet.getColumn()]

  for (int i = 0; i < wrkSheet.getRows(); i++) {
   for (int j = 0; j < wrkSheet.getColumns(); j++) {
    Cell cell = wrkSheet.getCell(j, i);
    if (cell.getContents() != null) {
     arrTestDta[i][j] = cell.getContents();
     System.out.println(cell.getContents());
    }
   }
  }
  return arrTestDta;
 }

}