How do I use DataProvider with Apache POI

Question

Please help!. Am newbie with Selenium frameworks, I have a method that accepts 5 parameters for booking a party. It uses TestNG DataProvider to read from excel file. The problem is(as shown below) It uses JXL imports which only supports XLS files (excel 2003 or older). I need help with a similar code that uses Apache POI instead so that it will support XLSX and new versions of excel (2007+). Can someone help me please?

package com.suite1;
import util.TestUtil;
import java.io.File;
import java.io.IOException;
import jxl.Sheet;
import jxl.Workbook;

import org.openqa.selenium.By;
import org.openqa.selenium.WebElement;
import org.testng.Assert;
import org.testng.SkipException;
import org.testng.annotations.AfterTest;
import org.testng.annotations.BeforeTest;
import org.testng.annotations.DataProvider;
import org.testng.annotations.Test;



public class CreatePartyTest extends TestBase1 {
    Workbook wb;     
    Sheet sh1;
    int numrow;

    @BeforeTest
    public void beforeTest() throws IOException
    {   
        initialize();
        if (TestUtil.isSkip("CreatePartyTest"))
        {
            throw new SkipException("Skipping test, check run mode");       
        }
        dr.get(CONFIG.getProperty("testSiteName"));
        getobject("signin_link").click();
        getobject("username_Signin_input").sendKeys("alexy.dsouza");
        getobject("password_input").sendKeys("testing123");
        getobject("submit_button").click();

    }
    @Test(dataProvider="Partydata")
    public void createParty (String Partyname, String Date, String Firstname, String Lastname, String email, String mobile) throws InterruptedException
    {
        getobject("party_link").click();
        getobject("start_party_link").click();
        getobject("partyname_input").sendKeys(Partyname);
        getobject("partydate_input").sendKeys(Date);
        getobject("hostfirstname_input").sendKeys(Firstname);
        getobject("hostlastname_input").sendKeys(Lastname);
        getobject("hostemail_input").sendKeys(email);
        getobject("hostmobile_input").sendKeys(mobile);
        getobject("make_reservation").click();
    }
    //source 
        @DataProvider(name="Partydata")
        public Object[][] TestDataFeed(){
        try {
        // load workbook: this is where i store my excel
        wb=Workbook.getWorkbook(new File("C://Workspace//Max//excelfiles//Partydata.xls"));
        // load sheet in my case I am referring to first sheet only
        sh1= wb.getSheet(0);
        // get number of rows so that we can run loop based on this
        numrow=  sh1.getRows();
        }
        catch (Exception e)
        {
        e.printStackTrace();
        }
        // Create 2 D array and pass row and columns
        Object [][] Accountdata=new Object[numrow-1][sh1.getColumns()];
        // This will run a loop and each iteration it will fetch new row
        for(int i=0,j=1;i<numrow-1;i++){
            // Fetch first row Accountname
                Accountdata[i][0]=sh1.getCell(0,j).getContents();
            // Fetch first row BankName
                Accountdata[i][1]=sh1.getCell(1,j).getContents();
            // Fetch everything else before an empty column
                Accountdata[i][2]=sh1.getCell(2,j).getContents();
                Accountdata[i][3]=sh1.getCell(3,j).getContents();
                Accountdata[i][4]=sh1.getCell(4,j++).getContents();

        }// Return 2d array object so that test script can use the same
        return Accountdata;
        }   
}

Answer 1

I cannot solve your exact query , but you can take reference from my code in which i have used .xlsx workbook only and it is working fine for me. I am able to read data from excel sheet(.xlsx) .

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;

import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.testng.annotations.DataProvider;
import org.testng.annotations.Test;

public class DataProvidersConcept {


    @DataProvider(name="Excelsheet")
    public Object[][] readData() throws Exception
    {
        File f = new File("C:/Users/Vikrant/Documents/MavenTesting.xlsx");
        FileInputStream fis = new FileInputStream(f);
        XSSFWorkbook workBook = (XSSFWorkbook) WorkbookFactory.create(fis);
        XSSFSheet sheet=workBook.getSheet("Sheet1");
        Object array[][]=new Object[2][2];
        for(int i =0;i<2;i++)
        {
            for( int j=0;j<2;j++)
            {
                array[i][j]=sheet.getRow(i).getCell(j).toString();

            }
        }
        return array;



    }

    @Test(dataProvider="Excelsheet")
    public void testData(String Username , String password)
    {
        System.out.println(Username);
        System.out.println("Username tested successfully");
        System.out.println(password);
        System.out.println("password tested successfully");
    }
}

enter code here