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

Question

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); } }

Answer 1

refactored your code, try this, it must work..

  /**
     * @author revanthreddya
     *
     */
    package com.playground.excel;

    public class ExcelUtils 
    {
        private Workbook wb;
         private Sheet ws;

         public ExcelUtils(String file, String sheet) {
             try {

                   if (file.indexOf("xlsx") < 0) { 
                    wb = new HSSFWorkbook(new FileInputStream(new File(file)));
                    ws = wb.getSheet(sheet);
                   } else { 
                    wb = new XSSFWorkbook(new FileInputStream(new File(file)));
                    ws = (XSSFSheet) wb.getSheet(sheet);
                   }
                  } catch (IOException io) {
                   System.err.println("Invalid file '" + file
                     + "' or incorrect sheet '" + sheet
                     + "', enter a valid one");
                  }
        }


         public int rowCount(){
                return ws.getLastRowNum();
             }


         public String getCell(int rowIndex, int columnIndex) {
              Cell cell = null;

              try {
                cell = ws.getRow(rowIndex).getCell(columnIndex);
              } catch (Exception e) {
               System.err.println("The cell with row '" + rowIndex + "' and column '"
                 + columnIndex + "' doesn't exist in the sheet");
              }
              return new DataFormatter().formatCellValue(cell);
             }


    }



public class TestCase {

     @Test(dataProvider="access")
     public void AADLoginLogoutTest(String test, String username, String password) throws IOException 
     {
          System.out.println(" test :"+test+"  user "+username+"  password:"+  password); 
     }


     @DataProvider(name = "access")
     public Object[][] access() throws Exception {

      ExcelUtils userD= new ExcelUtils("input.xlsx", "Actions");

      ArrayList<Object> paraList = new ArrayList<Object>();

      int i = 1;
      int totalRows = userD.rowCount();
      System.out.println("TotalRows: "+totalRows);
      while (i < totalRows) {

       Object[] args = new Object[3];
       args[0] = userD.getCell(i, 0);
       args[1] = userD.getCell(i, 1);
       args[2] = userD.getCell(i, 2);

       paraList.add(args);

       i++;
      }

      Object[][] argsData = new Object[paraList.size()][];
      for (i = 0; i < paraList.size(); i++)
       argsData[i] = (Object[]) paraList.get(i);
      return argsData;
     }
}

Answer 2

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.

Answer 3

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.