Passing data to DataProvider from Excel Sheet in TestNG

Updated 16 May 2023

Facebook Linkedin

In TestNG DataProvider helps  us to send multiple sets of data  from our excel sheet to a test method. But  here we need to make sure that the data returned by data provider should match with the parameter which we provide in Test method.

I explained in Below example how to pass the data to Data providers by reading the data from Excel sheet. We will write a simple program in which we will Validate the product addition by taking details of products from Excel sheet.

Step 1: First create a method to read excel data and return string array.
Step 2: Create before class and after class methods which helps in getting the browser and closing them when done.
Step 3: Create a data provider which actually gets the values by reading our excel sheet.
Step 4: Create a Test which takes different parameters i.e Name, Description, Weight, price, Result .
Step 5: Add dataprovider name for @Test method to receive data from dataprovider.

Below i mentioned code for my HSSFWorkbook, HSSFSheet worksheet , file location initialization and keep in mind that If you use the common interfaces like Workbook, you code is  workable with both HSSF and XSSF

Below I mentioned program of Data provider which provide my data from excel sheet.

Data Set / Test Data Examples

The below is the excel sheet for “Add product in akeneo with Valid Data”

Why and How to use Data Set / Test Data in Program:

There are many cases where you may need to check calculation / percentage by adding more number of records. In such cases we have to use data set and pass different values.

We need to make sure to identify the number of fields in the function / page and define the number of columns. On the basis of these columns, data will be provided to input fields in the application.

Below I mentioned  program for akeneo product addition by getting data from excel sheet.

Below program for writeResult program in Excel sheet:

Here Passed Results in my excel sheet are:

You may view Magento 2 Helpdesk, an all-in-one support ticketing solution to help your customers resolve their queries.

Category(s) Selenium TestNG
. . .

Leave a Comment

Your email address will not be published. Required fields are marked*


  • daniel ?? it would be best if we could get this code in github, or I can upload it to a website
    • Komal Sharma (Moderator)
      Hello Daniel,

      This blog is not a maintainer blog. It is only on techincal purpose blog.

      Kind Respects,
      Uvdesk Team

  • varaprasad
    String value=formatter.formatCellValue(cell); I am getting error at formatter saying that it is an unresolved type though i have used below import statement
    import org.apache.poi.ss.usermodel.DataFormatter;
    • Komal Sharma (Moderator)
      Hello Varaprasad,

      This blog is not a maintainer blog. It is only on techincal purpose blog.

      Thanks and Regards,
      Uvdesk Team!!!

  • www.qacreators.com
    Nice Article. I have been searching for this article in many blogs for a long time. Very good detailed explanation, found it in this website.
    Keep up the good work. Expecting more this kind of content from you.
    • Himani Gupta (Moderator)
      Hello there,
      Many thanks for reading and appreciation.
      Keep staying with us!!
      Thanks & Regards
      UVdesk Team
  • Dileep
    Good One!
    • Himani Gupta (Moderator)
      Hello there,
      Thanks for your appreciation.
      Keep staying with us!!
      Thanks & Regards
      UVdesk Team
  • Vishnu Godse

    This is super!!

    in one shot i can finish my requirement of test development.

    Very much appreciated..


    • Himani Gupta (Moderator)
      Hello Vishnu Godse,
      Glad to know that this guide helped you.
      Thanks for appreciation and reading the blog.
      Keep staying with us.
      Thank You!!
  • sree
    its so helpful…very clearly it was written each and every point..nic blog..expecting more information lik dis..tq
    • Komal Sharma (Moderator)
      Thanks for your appreciation sree.
  • vassa
    Thank you very much. Your code resolve my problem. Please continue in great work. I’m really appreciate it.
    • Himani Gupta (Moderator)
      Thanks for your appreciation Vassa 🙂
  • ziv sivron
    hi can you clarify this line please : Write obj1=new Write(); ?
    im trying to implement it in my code and an unable to. i cant find which dependency allowes me to use this method. have you written it by yourself? thanks.
    • Komal Sharma (Moderator)
      Hello Ziv Sivron,

      This blog is not a maintainer blog. It is only on techincal purpose blog.

      Thanks and Regards,
      Uvdesk Team!!!