How to read data from excel sheet using data provider in POM maven project

I am unable to read data from excel using data provider.. I am creating one class to read data from excel but getting error. [screenshot attached] now how to use same in the Test class to call them.

public class DataProvider extends Base {
    // ExcelApiTest eat = null;
    //String xlfilePath = "";
    //String SheetName = "";


    @DataProvider(name = "UserData")
    public Object[][] userFormData() throws Exception {
        Object[][] data = testData(xlfilePath, SheetName);
        return data;
    }


    public Object[][] testData(String xlfilePath, String SheetName) throws Exception {
        Object[][] excelData = null;
        eat = new ExcelApiTest(xlfilePath);

        int rows = eat.getRowCount(SheetName);
        int columns = eat.getColumnCount(SheetName);

        excelData = new Object[rows - 1][columns];

        for (int i = 1; i < rows; i++) {
            for (int j = 0; j < columns; j++) {
                excelData[i - 1][j] = eat.getcellData(SheetName, j, i);
            }
        }
        return excelData;
    }
}

Solution

You can use below code for username and password and modify it for more columns or fields :

import java.io.FileInputStream;
import java.io.IOException;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelRedaer {

     /**
     * @param filePath  excel file path
     * @param sheetName  sheet name in xlsx file
     * @return excel data
     * @throws InvalidFormatException
     * @throws IOException
     */
    public static Object[][] readExcel(String filePath, String sheetName) throws InvalidFormatException, IOException {
            FileInputStream file= new FileInputStream(filePath);
            XSSFWorkbook wb = new XSSFWorkbook(file);
            XSSFSheet sheet = wb.getSheet(sheetName);
            int rowCount = sheet.getLastRowNum();
            int column = sheet.getRow(0).getLastCellNum();
            Object[][] data = new Object[rowCount][column];
            for (int i = 1; i <= rowCount; i++) {
                XSSFRow row = sheet.getRow(i);
                for (int j = 0; j < column; j++) {
                    XSSFCell cell = row.getCell(j);
                    DataFormatter formatter = new DataFormatter();
                    String val = formatter.formatCellValue(cell);
                    data[i - 1][j] = val;
                }
            }

            return data;
        }
}

and use like that in your test :

import java.io.IOException;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.testng.annotations.DataProvider;
import org.testng.annotations.Test;



public class DataProviderDemo {

    private String filePath = "D:\\ECLIPSE-WORKSPACE\\playground\\src\\main\\resources\\demo.xlsx";
    private String sheetName = "demo";

    @Test(dataProvider = "excelData")
    public void read(String username, String password) {
        System.out.println(username + ":" + password);

    }

    @DataProvider(name="excelData")
    public Object[][] readExcel() throws InvalidFormatException, IOException {
        return ExcelRedaer.readExcel(filePath, sheetName);
    }

}

You are getting error in your code because you do not have all the methods of class ExcelApiTest , May be when you copied code from where you have got this , there should have been a class named ExcelApiTest which contains some methods like getRowCount(SheetName) and getColumnCount(SheetName) etc.

You can use this code for your demo purpose for username and password fields.

Also your class name is DataProvider which will again give you an error because testng has also this class: org.testng.annotations.DataProvider. So there will be a conflict and you should change your class name also.

My excel :

Hope it might help you:)