'How to write TestNG DataProvider annotation for Excel sheet with different column numbers for different rows

I have an Excel sheet with data as below

LoginPageValidation|            
LoginPage_login    | username1 | password1  
LoginPage_login    | username2 | password2   
LoginPage_login    | username3 | password3

I am returning "array of arrays" to @Dataprovider form class reading ExcelSheet(Excelutility.java)

Is there any way to write @DataProvider which handles nullpointerException while reading data from rows with single column data.

public static Object[][] getTableArray() 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 totalRows = ExcelWSheet.getLastRowNum()-ExcelWSheet.getFirstRowNum()+1;
           System.out.print("\nTOTAL ROWS "+totalRows+"\n");
    String a[][]=new String[totalRows][];
  for(int i=0;i<totalRows;i++)
  {
      int ColnumForRow=ExcelWSheet.getRow(i).getLastCellNum();
          a[i]=new String [ColnumForRow];
    for (int j=0;j<ExcelWSheet.getRow(i).getLastCellNum();j++)
    {
        if(getCellData(i,j).isEmpty())
        {System.out.println("\nEMPTY \n");}
        else
        { a[i][j]=getCellData(i,j);
          System.out.println("\nTABLE ARRAY : "+ a[i][j]);      
        }}
  }}
                return(tabArray);
        }

public static String getCellData(int RowNum, int ColNum) throws Exception 
    {try{   
             Cell = ExcelWSheet.getRow(RowNum).getCell(ColNum);
            int dataType = Cell.getCellType();
            String CellData = Cell.getStringCellValue();
                return CellData;
            }
        }

}

/testClass/
public class test1 { @Test(dataProvider="access") public void AADLoginLogoutTest(String test,String username,String pwd) throws IOException { System.out.println("CLAASS name AADLOGINLOGOUT"+this.getClass().getSimpleName()); } @DataProvider public Object[][] access() throws Exception { Object[][] testObjArray = ExcelUtils.getTableArray(); return (testObjArray); } }

javaarraysseleniumselenium-webdrivertestng-dataprovider


Solution 1:[1]

If your excel sheet contains different column numbers, we can handle in reading the excel sheet rather than handling them in data provider. In your code, replace the below section

for(int i=0;i<totalRows;i++)
{
  int ColnumForRow=ExcelWSheet.getRow(i).getLastCellNum();
  a[i]=new String [ColnumForRow];
  for (int j=0;j<ExcelWSheet.getRow(i).getLastCellNum();j++)
  {
     if(getCellData(i,j).isEmpty())
     { 
       System.out.println("\nEMPTY \n");
     }
     else
     { 
       a[i][j]=getCellData(i,j);
       System.out.println("\nTABLE ARRAY : "+ a[i][j]);      
     }
  }
}

with

for(int i=0;i<totalRows;i++)
{
  int ColnumForRow=ExcelWSheet.getRow(i).getPhysicalNumberOfCells();
  a[i]=new String [ColnumForRow];
  for (int j=0;j<ColnumForRow;j++)
  {
    if(getCellData(i,j).isEmpty())
    { 
       System.out.println("\nEMPTY \n");
    }
    else
    { 
       a[i][j]=getCellData(i,j);
       System.out.println("\nTABLE ARRAY : "+ a[i][j]);      
    }
  }
}

if we use getPhysicalNumberOfCells method instead of getLastCellNum it will return only the column count with data. Hence there would be no need to check whether the column is empty or not.

Hope this helps.

Solution 2:[2]

I suppose you are calling getTableArray() in @DataProvider method and you are getting NullPointerException in getTableArray() method. Rather than handling that exception in @DataProvider, you can do that in getTableArray() as that is where all action of reading of excel sheet is going on.

To know a simple way of handling the exception in getTableArray(), you can follow the script as given in my blog post- http://selenium-coding.blogspot.in/2016/05/generalized-apache-poi-script-for.html It shows easier way to go about reading and writing excelsheet. It basically puts the code that reads and writes the excel sheet in one class so that it can be used by other tests as well and it does the exception handling done in those particular methods only. As a standard practice, we should keep the tests away from the underlying complexities (such as catching the exception and etc) so that they become simple to understand by someone who has not written them.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 k.s. Karthik
Solution 2

Related Questions