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

Question

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

What is the error? where are you getting it? post it as text, not as image. — Guy, Jul 26 '18 at 06:59
Hello, I have added as text only... ExcelApiTest, getRowCount, getColumnCount mark as errror in red color.. — Himanshu Midha, Jul 26 '18 at 07:06
what is 'ExcelApiTest'? If that is a custom class, you got to debug it and see what is failing. — Renjith, Jul 26 '18 at 07:10
You just posted the code again, post the **complete** error message. You can add it to the question using the *edit* button. — Guy, Jul 26 '18 at 07:10
Error is in the code itself, I am not trying to run the program as i am writing this code to call the excel data using data provider, i am creating a class to read data then only i will call them. now while creating readdata class i am getting error in the code itself. u can check my code. thanks — Himanshu Midha, Jul 26 '18 at 07:23
@HimanshuMidha OK, the error is in the code. What is it? where is it? If you don't give this information no one will be able to help you. If the error leads to another class post this class code as well. You didn't add any picture or link to your question. And you shouldn't do it anyway. Post all relevant code and error message in the question. — Guy, Jul 26 '18 at 07:32
@Guy I am unable to share image file.. can u share me ur mail id so that i can provide — Himanshu Midha, Jul 26 '18 at 08:44
@HimanshuMidha **Do not post code/error as image**. Post it as text. — Guy, Jul 26 '18 at 08:47

dangi13 · Accepted Answer · 2018-08-06T12:16:16.857

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:)

@dangi13 It would be great, if you can add separate excel reader using this same code. To reduce call on data provider method for everytime. — Ishita Shah, Aug 06 '18 at 11:47
This is just an example @Ishita and of course it should be added in a separate class which can be re used any times.I was just providing an example and nothing else. — dangi13, Aug 06 '18 at 12:03
@dangi13 Actually I have replace my existing one, with yours. And it worked as expected. But looking for solution with separate method calling, in to Data provider. — Ishita Shah, Aug 06 '18 at 12:05
Hi @Ishita, please have a look at updated answer,is it that what you want? — dangi13, Aug 06 '18 at 12:17
@dangi13 Perfect. Thank you for quick response and solution. Its worked without iterator or arraylist. +1 — Ishita Shah, Aug 06 '18 at 12:23
It would be great if you can share article or source link for future reference. — Ishita Shah, Aug 06 '18 at 12:26
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/177493/discussion-between-dangi13-and-ishita-shah). — dangi13, Aug 06 '18 at 12:27

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

1 Answers1