Retriving Data from Excel Sheet with TestNG DataProvider for Selenium Webdriver.

How to get Data from Excel Sheet By Using TestNG - Data Provider for Selenium WebDriver.

 

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.testng.annotations.DataProvider;
import org.testng.annotations.Parameters;
import org.testng.annotations.Test;

Public Class Registration
{
    @Test(dataProvider="Registrationdata")
    public void Registration_data(String parameter1,String parameter2,...String Parameter n)throws  Exception
  {
         // Test code
   }


    @DataProvider
    public Object[][] Registrationdata() throws Exception
    {
          Object[][] testObjArray = getTableArray("C:\\Users\\TS\\Desktop\\PatientIDlist.xlsx","Sheet1");
         return (testObjArray);
    }


    public static Object[][] getTableArray(String xlFilePath, String sheetName)    throws Exception
        {  
           String[][] tabArray = null;
           try
           {
               FileInputStream file = new FileInputStream(new File(xlFilePath));
               XSSFWorkbook workbook = new XSSFWorkbook(file);
               XSSFSheet sheet = workbook.getSheetAt(0);
               int startRow = 1;
               int startCol = 0;
               int ci,cj;
               int totalRows = 0;
               int totalCols =  0;
               totalRows = sheet.getLastRowNum();
               totalCols = sheet.getRow(0).getLastCellNum();
               System.out.println("total cols = " + totalCols);
               System.out.println("total rows = " + totalRows);
               tabArray = new String[totalRows][totalCols];
               ci=0;
               for (int i=startRow;i<=totalRows;i++,ci++)
               {
                   cj=0;
                   for (int j=startCol;j<totalCols;j++, cj++)
                   {
                       tabArray[ci][cj]=sheet.getRow(i).getCell(j).getStringCellValue();
                       System.out.println(tabArray[ci][cj]);
                   }
               }

               workbook.close();
            }
            catch (FileNotFoundException e)
            {
                System.out.println("Could not read the Excel sheet");
                e.printStackTrace();
            }
            catch (IOException e)
            {
                System.out.println("Could not read the Excel sheet");
                e.printStackTrace();
            }
            return(tabArray);
        }

}