How can I read different datatypes from excel using @dataprovider in testNG

Question

I am trying to get input from excel for my testcase . My input contains both string and numeric values. I know how to retrieve value for single datatype using the below code

public Object[][]  readnumericvalue() throws IOException {
    File src = new File("filepath");    
    FileInputStream fis = new FileInputStream(src);
    XSSFWorkbook    wb = new XSSFWorkbook(fis);
    XSSFSheet sheet1 = wb.getSheetAt(1);
    XSSFRow row = sheet1.getRow(0);
    int rowcount = sheet1.getLastRowNum();
    int columnCount = row.getLastCellNum();
    Object data1[][]=new Double[rowcount+1][columnCount];
    columnCount = columnCount-1;

    for(int i=0;i<=rowcount;i++) {
        for(int j=0;j<=columnCount;j++) {
            data1[i[j]= sheet1.getRow(i).getCell(j).getNumericCellValue();
        }
    }
    return data1;
}

I tried to read both the datatypes in single dataprovider but I don't know how to initialize 2D-array for multiple datatypes

File src = new File("");

FileInputStream fis = new FileInputStream(src);
XSSFWorkbook    wb = new XSSFWorkbook(fis);
XSSFSheet sheet1 = wb.getSheetAt(0);
XSSFRow row = sheet1.getRow(0);
int rowcount = sheet1.getLastRowNum();
int columnCount = row.getLastCellNum();     
Object data1[][]=new String[rowcount+1][columnCount];
columnCount = columnCount-1
for(int i=0;i<=rowcount;i++) {
    for(int j=0;j<=columnCount;j++) {
        Cell cell = row.getCell(j);
        switch (cell.getCellTypeEnum()) {
            case STRING:
                data1[i][j]=sheet1.getRow(i).getCell(j).getStringCellValue();
                break;

            case NUMERIC:               
                data1[i][j]=sheet1.getRow(i).getCell(j).getNumericCellValue();
                break;                              
        }
    }   
}
return data1;

Answer 1

If you would like to accommodate both the data types in the same data provider, then you can basically define the array as an Object array.

Here's a sample.

Lets say the excel workbook looks like below

| Name | Age |
|------|-----|
| Jack | 24  |
| Jill | 23  |
| Bob  | 30  |

Here's a sample code, that reads this data

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.Iterator;

public class SampleTestClass {

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

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

        int rowcount = sheet1.getPhysicalNumberOfRows();
        int columnCount = sheet1.getRow(0).getLastCellNum();
        Object objects[][] = new Object[rowcount-1][columnCount];
        int rowCounter = 0;

        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();
            int colCounter = 0;
            while (cellIterator.hasNext()) {
                Cell cell = cellIterator.next();
                switch (cell.getCellType()) {
                    case Cell.CELL_TYPE_STRING:
                        objects[rowCounter][colCounter] = cell.getStringCellValue();
                        break;

                    case Cell.CELL_TYPE_NUMERIC:
                        objects[rowCounter][colCounter] =  new Double(cell.getNumericCellValue()).intValue();
                        break;
                }
                colCounter++;
            }
            rowCounter++;
        }
        return objects;
    }
}

Here's the output

Name :Jack, Age :24
Name :Jill, Age :23
Name :Bob, Age :30

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