Test NG @Dataprovider with APACHE POI and JExcel

Test NG Data Provider Example with Apache POI XSSF

Lets assume this is a form where we need to enter out data

I will create the data in a excel sheet

I will read the excel sheet using the below method

package com.sudas.utility.dataProvider;

import java.io.File;
import java.io.IOException;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.testng.annotations.DataProvider;

public class ExcelData {

 static File file;
 int totalNumberOfColumns;
 static String data[][];

 public static void main(String[] args) {
  // TODO Auto-generated method stub
  ExcelData ed = new ExcelData(new File("/Users/S_Das/Documents/Java/PersonalDetails.xlsx"));
  ExcelData.readExcelData();
  // dataGen();

 }

 public ExcelData(File file) {
  // TODO Auto-generated constructor stub
  ExcelData.file = file;
 }
@DataProvider(name="excelDP")
 public static String[][] readExcelData() {
  XSSFWorkbook workbook = null;
  try {

   workbook = new XSSFWorkbook(file);
   // taking the firstSheet
   XSSFSheet sheet = workbook.getSheetAt(0);
   // this give total ROWS in the sheet
   /**
    * For data generator each row should have equal number of columns
    * Total number of rows = number of times test gets executed total
    * number of column = number of argument in test method
    */
   int totalRows = sheet.getPhysicalNumberOfRows();
//   int totalRows = sheet.getLastRowNum()-sheet.getFirstRowNum();
   int totalColumns = sheet.getRow(1).getPhysicalNumberOfCells();
   
   // create a String [][] object
   data = new String[totalRows][totalColumns];
   // the first row is header so starting from 1
   for (int i = 1; i < totalRows; i++) {
    // total number of columns / row
    int totalNumberOfColumns = sheet.getRow(i).getPhysicalNumberOfCells();
    // System.out.println(totalNumberOfColumns);
    for (int j = 0; j < totalNumberOfColumns; j++) {
     XSSFCell x = sheet.getRow(i).getCell(j);
     if (x != null) {
      x.setCellType(Cell.CELL_TYPE_STRING);
      System.out.println(x.getStringCellValue());
//System.out.print(i);
//System.out.println(j);
      data[i][j] = x.getStringCellValue();
      

     }

    }
   }

  }

  catch (InvalidFormatException | IOException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }
  return data;

 }
/**
 * Test Method
 * @return
 */
@Deprecated
 public static Object[][] dataGen() {
  String s[][] = new String[3][1];
  for (int i = 0; i < s.length; i++) {

   for (int j = 0; j < s[i].length; j++) {
    System.out.print(i);
    System.out.println(j);
   }
  }
  return new Object[][] { {}, {} };

 }

}

Finally I will use a TestNG data provider to pass the values from excel.

 package com.sudas.utility.dataProvider;
 
 import java.io.File;
 
 import org.testng.annotations.BeforeSuite;
 import org.testng.annotations.Test;
 
 public class EnterDataTest {
  @BeforeSuite
  public void before()
  {
   ExcelData data = new ExcelData(new File("/Users/S_Das/Documents/Java/PersonalDetails.xlsx"));
   ExcelData.readExcelData();
  }
  
 
  @Test(dataProvider="excelDP",dataProviderClass=com.sudas.utility.dataProvider.ExcelData.class)
  public static void enterDataInForm(String FirstName, String MiddleName, String LastName, String Age, String Address,
    String Occupation, String State, String Postcode, String Country, String Email, String Phone) {
 
   
   
   
  }
 
 }

This is what you will get as output

[TestNG] Running:
  /private/var/folders/xd/h0rwdyp90g740mr3rt5lrpgc0000gn/T/testng-eclipse-1286226461/testng-customsuite.xml

Tom
T
TomT
20
TomLand, Australia
Servive
NSW
2000
Australia
tom@t.com
123456
Dick
D
DickD
30
Dickland, Australia
Business
NSW
2000
Australia
Dick@d.com
999999
Harry
H
HarryH
40
HarryLand, Australia
Cook
NSW
2000
Australia
Harry@h.com
231421323
Dave
DaveD
60
HarryLand, Australia
Retired
NSW
2000
Tom
T
TomT
20
TomLand, Australia
Servive
NSW
2000
Australia
tom@t.com
123456
Dick
D
DickD
30
Dickland, Australia
Business
NSW
2000
Australia
Dick@d.com
999999
Harry
H
HarryH
40
HarryLand, Australia
Cook
NSW
2000
Australia
Harry@h.com
231421323
Dave
DaveD
60
HarryLand, Australia
Retired
NSW
2000
PASSED: enterDataInForm(null, null, null, null, null, null, null, null, null, null, null)
PASSED: enterDataInForm("Tom", "T", "TomT", "20", "TomLand, Australia", "Servive", "NSW", "2000", "Australia", "tom@t.com", "123456")
PASSED: enterDataInForm("Dick", "D", "DickD", "30", "Dickland, Australia", "Business", "NSW", "2000", "Australia", "Dick@d.com", "999999")
PASSED: enterDataInForm("Harry", "H", "HarryH", "40", "HarryLand, Australia", "Cook", "NSW", "2000", "Australia", "Harry@h.com", "231421323")
PASSED: enterDataInForm("Dave", null, "DaveD", "60", "HarryLand, Australia", "Retired", "NSW", "2000", null, null, null)

===============================================
    Default test
    Tests run: 5, Failures: 0, Skips: 0
===============================================


===============================================
Default suite
Total tests run: 5, Failures: 0, Skips: 0
===============================================

[TestNG] Time taken by org.testng.reporters.XMLReporter@7a46a697: 15 ms
[TestNG] Time taken by org.testng.reporters.EmailableReporter2@77afea7d: 8 ms
[TestNG] Time taken by [FailedReporter passed=0 failed=0 skipped=0]: 4 ms
[TestNG] Time taken by org.testng.reporters.jq.Main@1055e4af: 44 ms
[TestNG] Time taken by org.testng.reporters.SuiteHTMLReporter@2e5c649: 15 ms
[TestNG] Time taken by org.testng.reporters.JUnitReportReporter@57fa26b7: 4 ms

As you can see in the first test instead of header text (which is our first row) i am sending null, this would be a good negative test case by passing invalid / null values. [Ideally i should have eliminated that.

******************************************************************************************************************************************************************

Example

 package TestNG;  
 import java.io.File;  
 import java.io.FileInputStream;  
 import java.io.FileNotFoundException;  
 import java.io.IOException;  
 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.Test;  
 import jxl.Cell;  
 import jxl.Sheet;  
 import jxl.Workbook;  
 import jxl.read.biff.BiffException;  
 public class DataProvider {  
      public static void main(String[] args) {  
           // TODO Auto-generated method stub  
 //          new DataProvider().datagen();  
      }  
      private int collength;  
      private String[][] doubleDim;  
      private String[][] jxlarray;  

       @org.testng.annotations.DataProvider (name="dpTest")  
      // data generator using APACHE POI  
      public String[][] datagen() {  
           File file = new File("C://Test//SQL.xlsx");  
           try {  
                XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(file));  
                XSSFSheet sheet = workbook.getSheetAt(0);  
                System.out.println(sheet.getLastRowNum());  
                int rowlength = sheet.getLastRowNum() - sheet.getFirstRowNum();  
                XSSFRow rows;  
                //rows.getLastCellNum() - rows.getFirstCellNum();  // if you want value of col 2  
                collength =1;           // if you need value of only col 1  
                doubleDim = new String[rowlength][collength];  
                for (int i = 0; i < rowlength; i++) {  
                      rows= sheet.getRow(i);  
                     for (int j = 0; j < collength; j++) {  
                          System.out.println(rows.getCell(j).getStringCellValue());  
                          doubleDim[i][j] = rows.getCell(j).getStringCellValue();  
                     }  
                }  
 workbook.close();  
           } catch (FileNotFoundException e) {  
                // TODO Auto-generated catch block  
                e.printStackTrace();  
           } catch (IOException e) {  
                // TODO Auto-generated catch block  
                e.printStackTrace();  
           }  
           return doubleDim;  
      }  
       @Test(dataProvider="dpTest1",priority=1)  
       public void testjxlDP(String reportName)  
       {  
           System.out.println(reportName);   
       }  
       @Test(dataProvider="dpTest",priority=0)  
       public void testpoiDP(String reportName)  
       {  
           System.out.println(reportName);   
       }  
       @org.testng.annotations.DataProvider(name ="dpTest1")  
           public String[][] datagenJXL() {  
                Workbook workbook;  
                try {  
                     File file = new File("C://Test//JSQL.xls");  
                     workbook = Workbook.getWorkbook(file);  
                     Sheet sheet = workbook.getSheet(0);  
                     int totalRows = sheet.getRows();  
                     int totalColumns = 1;          //sheet.getColumns(); // I am using only Col1 values  
                     jxlarray = new String[totalRows][totalColumns];  
                     for (int i = 0; i < totalRows; i++) {  
                          for (int j = 0; j < totalColumns; j++) {  
                               Cell cells = sheet.getCell(j,i);  
                               jxlarray[i][j]=cells.getContents();  
                               System.out.println(cells.getContents());  
                          }  
                     }  
                } catch (BiffException e) {  
                     // TODO Auto-generated catch block  
                     e.printStackTrace();  
                } catch (IOException e) {  
                     // TODO Auto-generated catch block  
                     e.printStackTrace();  
                }  
                return jxlarray;  
           }  
 }  

OUTPUT