Pass Data From Excel Sheet to Data Provider in TestNG

Data Provider is  a special annotation used for supplying data for a test method. The annotated method must return an Object[ ][ ]. In this tutorial we discuss how we can pass data to data provider dynamically from excel to data provider so that out test will get data from data provider directly not from excel.

We can use this code as a reusable function to read data from excel and store it in data provider.This below code works for Excel sheets with .xlsx extention if you have older version like .xls replace all "XSSF" with "HSSF".

Code to Pass Data From Excel Sheet to Data Provider in TestNG

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
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;
import org.testng.annotations.DataProvider;
import org.testng.annotations.Test;

public class ReadAnyExcel {
    public static XSSFWorkbook workbook;
    public static XSSFSheet worksheet;
    public static DataFormatter formatter= new DataFormatter();
    @DataProvider(name="data")
    public static Object[][] readFromExcel( ) throws IOException {
    FileInputStream fileInputStream= new FileInputStream("ExcelFileLocation");
         workbook = new XSSFWorkbook (fileInputStream);
         worksheet=workbook.getSheetAt(0);
         XSSFRow Row=worksheet.getRow(0);
         int RowNum = worksheet.getPhysicalNumberOfRows();
         int ColNum= Row.getLastCellNum();
         Object Data[][]= new Object[RowNum-1][ColNum];
             for(int i=0; i<RowNum-1; i++)
             {
                 XSSFRow row= worksheet.getRow(i+1);
                 for (int j=0; j<ColNum; j++)
                 {
                     if(row==null)
                         Data[i][j]= "";
                     else
                     {
                         XSSFCell cell= row.getCell(j);
                         if(cell==null)
                             Data[i][j]= "";
                         else
                         {
                             String value=formatter.formatCellValue(cell);
                             Data[i][j]=value;
                         }
                     }
                 }
             }
         return Data;
     }
    @Test(dataProvider = "data")
//Here total no of parameters should match no of columns in excel sheet.   
public void name(String s1,String s2....) {
     System.out.println(s1+s2+s3+s4+s5);
   }
 }