How to send rows with flag to dataprovider

Question

I am using below code to pass the rows to data provider with flag M, But my test is running for all the rows.

File filpath = new File(FilePath);
            FileInputStream ExcelFile = new FileInputStrea(filpath);
            ExcelWBook = new XSSFWorkbook(ExcelFile);
            ExcelWSheet = ExcelWBook.getSheetAt(0);
            int startRow = 1;
            int startCol = 0;
            int ci, cj;
            int totalRows = ExcelWSheet.getLastRowNum();
            //System.out.println("total rows in Excel"+ totalRows);
            int totalCols =  ExcelWSheet.getRow(0).getLastCellNum();
            //System.out.println("total columns in Excel"+totalCols);       
            tabArray = new String[totalRows][totalCols];        
            for (int k = 1; k < totalRows;)
            {
            if(EOTdata.ExcelWSheet.getRow(k).getCell(0).getStringCellValue().equalsIgnoreCase("M"))
            {
System.out.println(k +" "+EOTdata.ExcelWSheet.getRow(k).getCell(0).getStringCellValue().equalsIgnoreCase("M"));
        ci = 0;
        for (int i = startRow; i <= totalRows;  i++ , ci++) {
            cj = 0;
            for (int j = startCol; j <totalCols; j++, cj++) {                   tabArray[ci][cj] = getCellData(i, j);
System.out.println("total array "+ tabArray[ci][cj]);

Answer 1

Lets say you have an excel spreadsheet, that has data as shown in the below table

| Name    | Age | Run |
|---------|-----|-----|
| Raghu   | 24  | Y   |
| Ramu    | 23  | N   |
| Shekhar | 30  | Y   |

Lets say we would like to run all rows that have a "Y" in them. Here's a simple example that shows how to do this

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.testng.annotations.DataProvider;
import org.testng.annotations.Test;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

public class TesclassSample {

    @Test(dataProvider = "dp")
    public void testMethod(String name, int age, String run) {
        System.err.println("Name :" + name + ", Age :" + age + ", Run value " + run);
    }

    @DataProvider(name = "dp")
    public Object[][] readnumericvalue() throws IOException {
        File src = new File("src/test/resources/47032451.xlsx");
        FileInputStream fis = new FileInputStream(src);
        XSSFWorkbook wb = new XSSFWorkbook(fis);
        XSSFSheet sheet1 = wb.getSheetAt(0);

        int columnCount = sheet1.getRow(0).getLastCellNum();
        List<List<Object>> objects = new ArrayList<>();

        Iterator<Row> rowIterator = sheet1.iterator();
        boolean firstRow = true;
        while (rowIterator.hasNext()) {
            Row currentRow = rowIterator.next();
            if (firstRow) {
                firstRow = false;
                continue;
            }
            Iterator<Cell> cellIterator = currentRow.iterator();
            List<Object> iterationRow = new ArrayList<>();
            while (cellIterator.hasNext()) {
                Cell cell = cellIterator.next();
                switch (cell.getCellType()) {
                    case Cell.CELL_TYPE_STRING:
                        iterationRow.add(cell.getStringCellValue());
                        break;

                    case Cell.CELL_TYPE_NUMERIC:
                        iterationRow.add(new Double(cell.getNumericCellValue()).intValue());
                        break;
                }
            }
            //Consider the iteration for adding only if the "Run" column had a "Y"
            if ("y".equalsIgnoreCase(iterationRow.get(2).toString().trim())) {
                objects.add(iterationRow);
            }
        }
        //Now that we have the arraylist, lets translate it back to a 2D array.
        Object toReturn[][] = new Object[objects.size()][columnCount];
        for (int i = 0; i < objects.size(); i++) {
            toReturn[i] = objects.get(i).toArray();
        }
        return toReturn;
    }
}

Here's the output:

Name :  Raghu, Age :24, Run value Y
Name :Shekhar, Age :30, Run value Y

===============================================
Default Suite
Total tests run: 2, Failures: 0, Skips: 0
===============================================