How to read a specific value from Excel sheet using Selenium WebDriver TestNG Data Provider Method?

Question

Following is the code:-

package sanityTests;

import java.io.File;
import java.io.IOException;

import org.openqa.selenium.By;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.chrome.ChromeDriver;
import org.testng.Assert;
import org.testng.annotations.DataProvider;
import org.testng.annotations.Test;


import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

public class DataDrivenTest2 {


    @Test (dataProvider= "testdata")  //attribute

    public void add(String x , String y){
        int a= Integer.parseInt(x);   //Convert String to integer
        int b = Integer.parseInt(y);
        int z=a+b;
        System.out.println(z);
    }


    @DataProvider(name="testdata")   //annotation for TestNG


    public Object [] [] readExcel() throws BiffException, IOException   //Way of Object Declaration of Array.Main Method Removed because of test NG Framework
{       

        File f = new File("C:/Users/Ishan/Desktop/Input2.xls");  //Create File Object to locate file on system
        Workbook w = Workbook.getWorkbook(f); //Activate the Workbook
        Sheet s = w.getSheet(0);  //Activate Sheet (either by name or id). Id starts with 0
        int rows= s.getRows() ; //Get Row Count
        System.out.println(rows); //Get Column Count
        int columns = s.getColumns();
        System.out.println(columns);

    String InputData[] [] = new String [rows] [columns];  //Array to read data from excel file

    for(int i=0; i<rows; i++){
        for(int j=0; j<columns;j++){
            Cell c =s.getCell(j, i);  // Getting location of cell

            InputData[i][j] =c.getContents();
        System.out.println(InputData[i][j]);


    }

}
    return InputData;
}
}

Now it is returning me all the values and the sum of the values mentioned in the Input Excel. Following is the input values

1 2 3 4 5 6

Now I want to only select values from specific row and column say Row 1 , Column 1 and add them and show the sum. I have tried to enter 1,1 in the Input Data Field but I am not getting the output. Please help.

The excel sheet contains 3 rows and 2 columns...... First Row( values 1 and 2), Second Row( values 3 and 4), Third Row( values 5 and 6). Now I want to select ony value 3 and 4 which are part of Second row (row index[1] and column index[1]. Not sure how to proceed. — Ishan Dhaliwal, Mar 13 '16 at 10:59
Just 2 columns and 3 rows.... 1 2 3 4 5 6 I want to pick up value from the second row (i.e.3(column 1) and 4(column 2) and add them to get sum 7.... — Ishan Dhaliwal, Mar 16 '16 at 15:34

score 0 · Answer 1 · answered Mar 17 '16 at 02:09

If you want data from specific cells then you can do like:

        File f = new File("/Users/Pankaj/Desktop/QA_Automation/qascripting/Stacks/files/Input2.xls");  //Create File Object to locate file on system
        Workbook w = Workbook.getWorkbook(f); //Activate the Workbook
        Sheet s = w.getSheet(0);  //Activate Sheet (either by name or id). Id starts with 0
        int rows= s.getRows() ; //Get Row Count
        int columns = s.getColumns();

        //sheet's columns and rows are started with (column = 0, row = 0)
        int firstColumn = 0;
        int secondColumn = 1;
        int secondRow = 1;

        int firstColumnData = Integer.parseInt(s.getCell(firstColumn, secondRow).getContents());
        int secondColumnData = Integer.parseInt(s.getCell(secondColumn, secondRow).getContents());

        int desiredData = firstColumnData + secondColumnData;

        System.out.println("First Column: "+firstColumnData + " Second Column Data: "+secondColumnData + " Sum Data: "+desiredData);

How to read a specific value from Excel sheet using Selenium WebDriver TestNG Data Provider Method?

1 Answers1