DDT with Excel in Rest Assured

Reading Time: 3 minutes

We have already discussed Rest assured in some of our previous blog posts. The scope of this blog will be DDT only. DDT stands for data driven testing. It is a practice of creation of test script where test data is read from data files instead of old school hard coded values each time scripts run. This way, testers can test how the application handles various inputs effectively.
Data files could be any of the following :-

  • Excel files
  • CSV files
  • ODBC source
  • ADO objects
  • Data pools

I will assume here that we have already set a maven project and our focus will be only to integrate our rest assured project with DDT testing methodology. Our job from here will be only to get values for testing from an external file. Here we go.

Maven dependencies for DDT in Rest assured :



Understanding the DDT rest assured strategy

I will be taking a XLSX fromat sheet to take data for testing. Basically I used a google sheet and then downloaded it that format, there are other formats as well but it is totally upto you what format you prefer. You can store your test data file anywhere you want but I will suggest you to put that file inside a resource package on your project(another best practice stuff).
Sheet’s snap shot :

We have mentioned all the test cases in a column and there test data in the succeeding row. We can not just select a sheet we need to select the whole worksheet only after that we can select particular sheet. Now we have the sheet we need to identify the test case column by scanning the entire first row. Once we get the column we need to scan that column and identify the test case. We can easily identify the test case by the name now we need to get all the data in that row but remember a row is collection cell. And therefore we will be needing a cell iterator to get that the data. Below is logic for the same.

    public ArrayList<String> getData(String testcaseName) throws IOException {
        //fileInputStream argument
        ArrayList<String> dataList = new ArrayList<String>();

        FileInputStream fis = new FileInputStream("..//demo//src//test//Resources//testFile.xlsx");
        XSSFWorkbook workbook = new XSSFWorkbook(fis);

        int sheets = workbook.getNumberOfSheets();
        for (int i = 0; i < sheets; i++) {
            if (workbook.getSheetName(i).equalsIgnoreCase("ReqRes")) {
                XSSFSheet sheet = workbook.getSheetAt(i); //Identify Testcases column by scanning the entire 1st row

                Iterator<Row> rows = sheet.iterator(); // sheet is collection of rows
                Row firstrow = rows.next();
                Iterator<Cell> ce = firstrow.cellIterator(); //row is collection of cells
                int k = 0;
                int coloumn = 0;
                while (ce.hasNext()) {
                    Cell value = ce.next();

                    if (value.getStringCellValue().equalsIgnoreCase("TestCases")) {
                        coloumn = k;



                ////once coloumn is identified then scan entire testcase column to identify purchase testcase row
                while (rows.hasNext()) {

                    Row r = rows.next();

                    if (r.getCell(coloumn).getStringCellValue().equalsIgnoreCase(testcaseName)) {

                        ////after you grab purchase testcase row = pull all the data of that row and feed into test

                        Iterator<Cell> cv = r.cellIterator();
                        while (cv.hasNext()) {
                            Cell cell = cv.next();
                            if (cell.getCellTypeEnum() == CellType.STRING) {

                            } else {




        return dataList;


If you see this code carefully I did the same what I have mentioned earlier.

Steps to get the test case data

  1. Get the test data file
  2. Parse it into the workbook
  3. Get the desired sheet by specifying the sheet name
  4. Identify the Testcases columns by scanning the first row
  5. Identify your test case by scanning the Testcases column
  6. Get all the test data from test case row by iterating over it.

Here I have kept the test data in a ArrayList so that I can use it anywhere I want. Hope you guys like this post stay tuned for more interested posts.

References :

Written by 

Alok Jha is the QA Consultant at Knoldus Software LLP. He has good knowledge of languages Java, Java 8, Rust and JavaScript. As a QA, he always tries to explore the different type of software and tools.