passing dynamic parameter value from an excel sheet

[Smita Sinha]

Hi All,

I have to read value from an excel sheet.

This excel sheet can have any no. of rows and any no. of columns.They are not fixed.

My question is 

Is it possible to do it by using @dataProvider ?

or should I use @factory or anything else.

Can someone please help me with a small example to solve this.

Thanks,

Smita

[Krishnan Mahadevan]

Smita,

You normally would use a @Factory annotation, when you would need to generate Test classes.

Here's a full fledged example that uses the @DataProvider annotation. The data provider would take care of figuring out the iterations (because you would be implementing that logic within your data provider)

package testng.samples;

import java.io.File;

import java.io.FileInputStream;

import java.io.FileNotFoundException;

import java.io.IOException;

import java.io.InputStream;

import java.util.ArrayList;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import org.apache.poi.ss.usermodel.Row;

import org.apache.poi.ss.usermodel.Sheet;

import org.apache.poi.ss.usermodel.Workbook;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import org.testng.annotations.DataProvider;

import org.testng.annotations.Test;

public class ReadFromExcel {

String fileName = "src/test/resources/Book1.xls";

@Test(dataProvider = "dp")

public void testMethod(String value) {

System.out.println(value);

}

@DataProvider(name = "dp")

public Object[][] getData() throws IOException {

Workbook workBook = null;

ArrayList<String> values = new ArrayList<String>();

InputStream inputStream = getInputFileStream(fileName);

if (fileName.toLowerCase().endsWith("xlsx") == true) {

workBook = new XSSFWorkbook(inputStream);

} else if (fileName.toLowerCase().endsWith("xls") == true) {

workBook = new HSSFWorkbook(inputStream);

}

inputStream.close();

Sheet sheet = workBook.getSheet("Sheet1");

int rows = sheet.getPhysicalNumberOfRows();

for (int i = 1; i < rows; i++) {

Row row = sheet.getRow(i);

values.add(row.getCell(1).getStringCellValue());

}

Object[][] returnValue = new Object[values.size()][1];

for (int i = 0; i < values.size(); i++) {

returnValue[i][0] = values.get(i);

}

return returnValue;

}

private InputStream getInputFileStream(String fileName) throws FileNotFoundException {

ClassLoader loader = this.getClass().getClassLoader();

InputStream inputStream = loader.getResourceAsStream(fileName);

if (inputStream == null) {

inputStream = new FileInputStream(new File(fileName));

}

return inputStream;

}

}

I depended on 

<dependency>

<groupId>org.apache.poi</groupId>

<artifactId>poi-ooxml</artifactId>

<version>3.6</version>

</dependency>

for working with the excel sheets.

The output:

[TestNG] Running:

  C:\Users\krmahadevan\AppData\Local\Temp\testng-eclipse--1051612080\testng-customsuite.xml

a

b

c

d

e

f

g

h

i

j

PASSED: testMethod("a")

PASSED: testMethod("b")

PASSED: testMethod("c")

PASSED: testMethod("d")

PASSED: testMethod("e")

PASSED: testMethod("f")

PASSED: testMethod("g")

PASSED: testMethod("h")

PASSED: testMethod("i")

PASSED: testMethod("j")

===============================================

    Default test

    Tests run: 10, Failures: 0, Skips: 0

===============================================

Thanks & Regards
Krishnan Mahadevan

"All the desirable things in life are either illegal, expensive, fattening or in love with someone else!"

--
You received this message because you are subscribed to the Google Groups "testng-users" group.
To post to this group, send email to testng...@googlegroups.com.
To unsubscribe from this group, send email to testng-users...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/testng-users?hl=en.

[Smita Sinha]

Thanks Krishnan,

This piece of code did the magic!!

Object[][] returnValue = new Object[values.size()][1];

-Smita

[raghavi latha]

Hi All,

I have to read only rows  from an excel sheet.

This excel sheet can have any no. of rows and any no. of columns.They are not fixed.

Can someone please help me with a small example to solve this.

Thanks,

Raghavi