How to read data from excel in selenium

Read test data from any external source in selenium is very important when we are working with framework.
Test data can be store in any format like excel, csv, text etc.

If you are using C# in selenium then go to below URL.
How to Read Data from Excel in Selenium C#

There are many methods for reading data from excel files. easiest way to read data from excel file is using apache poi.

first of all you have to import below packages for apache poi.

import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

Now you need to create separate function for getting data from excel. I have created below function for getting data from excel file. 

public String[][] ReadDataFromExcel(String vXlPath, int vSheetNum){
 
  File myxl = new File(vXlPath);

  FileInputStream myStream = null;

  try {

   myStream = new FileInputStream(myxl);

  } catch (FileNotFoundException e) {

   System.out.println("File not found exception in class GetDataFromExcel");

   e.printStackTrace();

  }

  

  XSSFWorkbook myWB = null;

  try {

   myWB = new XSSFWorkbook(myStream);

  } catch (IOException e) {

   System.out.println("Exception during new workbook creation in class GetDataFromExcel");

   e.printStackTrace();

  }

  XSSFSheet mySheet = myWB.getSheetAt(vSheetNum); // Referring to work sheet 

  

  int xRows = mySheet.getLastRowNum()+1; // getting the no.of rows

  int xCols = mySheet.getRow(0).getLastCellNum(); //getting the no.of columns


   System.out.println("No.of rows in excel sheet is "+xRows);

  System.out.println("No.of columns in excel sheet is "+xCols);

  

  String[][] xData = new String[xRows][xCols];

  for (int i = 0; i < xRows; i++) {

   XSSFRow row = mySheet.getRow(i);

         for (int j = 0; j < xCols; j++) {

          XSSFCell cell = row.getCell(j); // To read value from each col in each row

             String value = cellToString(cell);

             xData[i][j] = value;

         }

  }
   
  return xData;

 }

This function return two dimensional String array and we can use that array into our selenium code.

Here, you can see one another function 'cellToString' in highlighted part. This function is for covert cell value to string.

public static String cellToString(XSSFCell cell) {
  // This function will convert an object of type excel cell to a string value
  Object result;
  try{
          int type = cell.getCellType();
          DataFormatter df = new DataFormatter();
          switch (type) {
              case XSSFCell.CELL_TYPE_NUMERIC: //0
               //result = cell.getNumericCellValue();
               result = df.formatCellValue(cell);
                  break;
              case XSSFCell.CELL_TYPE_STRING: //1
                  result = cell.getStringCellValue();
                  break;
              case XSSFCell.CELL_TYPE_FORMULA: //2
                  throw new RuntimeException("We can't evaluate formulas in Java");
              case XSSFCell.CELL_TYPE_BLANK: //3
                  result = "-";
                  break;
              case XSSFCell.CELL_TYPE_BOOLEAN: //4
                  result = cell.getBooleanCellValue();
                  break;
              case XSSFCell.CELL_TYPE_ERROR: //5
                  throw new RuntimeException ("This cell has an error");
              default:
                  throw new RuntimeException("We don't support this cell type: " + type);
                  
          }
          return result.toString();
  }catch (Exception e)
  {
   System.out.println(" Exception during cell to string conversion in class GetDataFromExcel" + cell);
  }
  return null;  
   }

I have created one more function if we want data of only one particular row in excel then we can use below function.

public  String[] mGetDataFromExcel(String vXlPath, int vSheetNum, int nRow){
  
  File myxl = new File(vXlPath);
  FileInputStream myStream = null;
  try {
   myStream = new FileInputStream(myxl);
  } catch (FileNotFoundException e) {
   // TODO Auto-generated catch block
   System.out.println("File not found exception in class GetDataFromExcel");
   e.printStackTrace();
  }
  
  XSSFWorkbook myWB = null;
  try {
   myWB = new XSSFWorkbook(myStream);
  } catch (IOException e) {
   // TODO Auto-generated catch block
   System.out.println("Exception during new workbook creation in class GetDataFromExcel");
   e.printStackTrace();
  }
  XSSFSheet mySheet = myWB.getSheetAt(vSheetNum); // Referring to 1st sheet 
  int xCols = mySheet.getRow(0).getLastCellNum(); //getting the no.of columns
   
    
  String[] xData = new String[xCols];
  DataFormatter df = new DataFormatter();
  
             XSSFRow row = mySheet.getRow(nRow);
             for (int j = 0; j < xCols; j++) {
                XSSFCell cell = row.getCell(j); // To read value from each col in each row
                
                if (j >= 6 && j <= 11){
                 xData[j]  = df.formatCellValue(cell); // this gives the value of string 60 as 60, instead of 60.00
                }
                else {
                 String value = cellToString(cell);
                xData[j] = value;
              }
    }
    
    return xData;    
              
 }

Please add comment if you have any question.

If you want to read data from csv file then go to below post.
How to Read data from CSV file in Selenium