Excel Parametrize using POI with TestNG data provider

Excel Parametrize using POI with TestNG data provider
First add POI jar in your test

Make a Main Method:-
public class Parametrize {
    private WebDriver driver;
     private static XSSFSheet ExcelWSheet;
     private static XSSFWorkbook ExcelWBook;
     private static XSSFCell Cell;
      
    public String[][] getTableArray(String FilePath, String SheetName) throws Exception
     {
        String[][] tabArray = null;
        try {
            FileInputStream ExcelFile = new FileInputStream(FilePath);
            // Access the required test data sheet
            ExcelWBook = new XSSFWorkbook(ExcelFile);
            ExcelWSheet = ExcelWBook.getSheet(SheetName);

            int startRow = 0;
            int startCol = 0;
            int ci,cj;
            int totalRows = ExcelWSheet.getLastRowNum();
            System.out.println(totalRows);
            // you can write a function as well to get Column count
            int totalCols = 3;

            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]=getCellData(i,j);
                    System.out.println(tabArray[ci][cj]);
                    System.out.println(ci);
                    System.out.println(cj);
                     }
               }
             }
         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);
         }
    public String getCellData(int RowNum, int ColNum) throws Exception
    {
         try{
            Cell = ExcelWSheet.getRow(RowNum).getCell(ColNum);
           
                String CellData = Cell.getStringCellValue();
                System.out.println("ceel date =" + CellData);
                return CellData;
        }
            catch (Exception e)
            {
            System.out.println(e.getMessage());
            throw (e);
            }
        }
    }
Call using TestNG data provider
public class AddApachewebservice
{
    public static Parametrize action = new Parametrize(driver);
    @DataProvider(name = "appS")
     public static Object[][] settr() throws Exception
        {
         Object[][] testObjArray = action.getTableArray("E://Issues.xlsx","Sheet1");
         return (testObjArray);
                                
     }
@Test(dataProvider = "appS")
  public void testwebserviceApacheadd(String logicalName, String paraMeter, String workflowName )
     @Test
     ActionBot action = new ActionBot(driver);
     action.click(UIMap.webserviceLink);
     action.type(UIMap.virtualHostName,logicalName);
     action.type(UIMap.actionApache, paraMeter);
     action.type(UIMap.workFlowname, workflowName);
     action.type(UIMap.platformIDApache, "SCLCentos001");
     action.click(UIMaps.browseApache);
     action.fileUpload("E:\\seli\\FileChooser.exe");
   
}
    }
Issues.xlsx below Contents:-
   A                               B             C

1.www.test1.com	add	c3dna
2.www.test2.com	add	c3dna
3.www.test3.com	add	c3dna
4.www.test4.com	add	c3dna
5.www.test5.com	add	c3dna
6.www.test6.com	add	c3dna

Powered By- selenium blogs Hitesh Singh