Hii frnds….i need info about getting data from excel…how to get numeric data from excel???

Answered

I m unable to get numeric data example mobile number from excel n pass that value to some field…I used getnumericcellvalue() method to fetch the data…as send keys(char sequence) accepts char sequence I m unable to get neric data…m getting exception….pls do help me to sort this prblm…

Regards
Pradeep

Pradeep Train Asked on December 10, 2014 in Selenium WebDriver.
Add Comment
5 Answer(s)
Best answer

Hi Pradeep, you can also add the ‘ character at the beginning of the number stored in the cell to change the format to string in the excel file, this way you can get the info like any other cell.

Juliow Train Answered on December 11, 2014.

OK vl add a single character at the starting of the numeric data…but the thing s that character also added in my code rite….I just want only numeric data stored into the perticular field ,in that situation this vl fail so plZ do sujjest some other wayway
Thanks fr the rply

on December 11, 2014.

Have you added the (  `   ) sign present next to numeric key 1 in your keyboard??

on December 12, 2014.
Add Comment

Hi Pradeep, You can take the return type of readExcelData() as show below.

public static Object[][] readExcelData()
{
//paste above all the code here
return obj;
}

Thanks & Regards
Samba.

samba Train Answered on December 13, 2014.
Add Comment

Hi Pradeeep,
Here is the complete solution for your problem.


Util Class to read data from excel file

package com.syner.poi;
import java.io.FileInputStream;

import java.io.FileNotFoundException;

import java.io.IOException;

import java.io.InputStream;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;

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

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.ss.usermodel.WorkbookFactory;
public class Util {

public static Object[][] readExcelData() throws InvalidFormatException, IOException

{

Object[][] data=new Object[4][2];

InputStream f=new FileInputStream("GmailTestData.xlsx");

Workbook workbook=WorkbookFactory.create(f);

Sheet sheet=workbook.getSheetAt(0);

int rc=sheet.getLastRowNum();

for(int i=0;i<rc;i++)

{

Row r=sheet.getRow(i+1);

int cc=r.getLastCellNum();

for(int j=0;j<cc;j++)

{

Cell c=r.getCell(j);

if(c.getCellType()==c.CELL_TYPE_STRING)

{

data[i][j]=c.getStringCellValue();

}

else if(c.getCellType()==c.CELL_TYPE_NUMERIC)

{

//data[i][j]=c.getNumericCellValue();

data[i][j]=new Long((long) c.getNumericCellValue());

}

System.out.println("Data: "+data[i][j]);
}

}
return data;
}
}
ExcelFile class to get data from Util class and execute your test cases.

package com.syner.poi;
import java.io.IOException;

import java.lang.annotation.Retention;

import java.lang.annotation.RetentionPolicy;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;

import org.openqa.selenium.By;

import org.openqa.selenium.WebDriver;

import org.openqa.selenium.firefox.FirefoxDriver;

import org.testng.annotations.AfterMethod;

import org.testng.annotations.BeforeMethod;

import org.testng.annotations.DataProvider;

import org.testng.annotations.Test;
public class ExcelFile {

WebDriver driver;
@DataProvider(name="excelData")

public Object[][] readData() throws InvalidFormatException, IOException

{

return Util.readExcelData();

}
@BeforeMethod

public void setUp()

{

driver=new FirefoxDriver();

driver.manage().window().maximize();

driver.get("https://accounts.google.com/SignUp?service=mail&continue=https%3A%2F%2Fmail.google.com%2Fmail%2F&ltmpl=default");

}
@Test(dataProvider="excelData")

public void testApp(String firstName,long mobileNumber)

{

driver.findElement(By.xpath(".//*[@id='FirstName']")).sendKeys(firstName);

driver.findElement(By.xpath(".//*[@id='RecoveryPhoneNumber']")).sendKeys(""+mobileNumber);

}
@AfterMethod

public void tearDown() throws InterruptedException

{

Thread.sleep(3000);

driver.close();

}
}

In this example I used google singup page.
I hope this is useful for you, If you have any doubts regarding the problem, please let me know.
Thnaks & Regards
Samba.

samba Train Answered on December 14, 2014.

Hi samba..
Very gud morning..
Hey samba m bit unfamiliar with the @dataprovider..vl u give me how it works n all….
u know m using POM(Page Object Model) Framework….hope u used some other Framework itseems….

any how thank u very much for ur valuable time….n thanks fr helping me…..m bigginner in selenium so ur help vl b valuable….Thanks alot

on December 15, 2014.
Add Comment

Hi Pradeep,

If can show your code it would be great to help you.

Still you can check your method return type is string or integer,  I assume that your method type is string that’s why you are not able to return integer type value.

regards,
siddhesh.

siddheshlabdhe Train Answered on December 11, 2014.
Add Comment

Hi Pradeep
Here I am writing a sample program to read both String and Numeric Data from  Excel file.

public  class ReadExcelData{
public static void main(String[] args){
Object[][] obj=new Object[6][2];
InputStream f=new FileInputStream(“Path of your Excel File”);
Workbook wb=WorkBookFactory.create(f);
Sheet s=wb.getSheetAt(0);
int rc=s.getLastRowNum();
for(int i=0;i<rc;i++)
{
Row r=s.getRow(i+1);
int cc=r.getLastCellNum();
for(int j=0;i<cc;j++)
{
Cell c=r.getCell(j);
if(c.getCellType()==c.CELL_TYPE_STRING)
{
obj[i][j]=c.getStringCellValue();
}
else if(c.getCellType()==c.CELL_TYPE_NUMERIC)
{
obj[i][j]=c.getNumericCellValue();
}

}
}
System.out.println(obj[i][j]);
}
}

Thanks & Regards
Samba.

samba Train Answered on December 11, 2014.

Hi samba
I want clarification regarding return type of the Readexceldata method coz as u mentioned in code value vl b different i’e numeric n string so how u decide return type fr dat method in excel library

on December 12, 2014.

Hi Pradeep, You can take the return type of readExcelData() as show below.

public static Object[][] readExcelData()
{
//paste above all the code here
return obj;
}

Thanks & Regards
Samba.

on December 13, 2014.

Thanks fr ur help samba…..but my requirement is I need to cal readexcelData() from another class and that method should give me the data, so that I can send that data to perticular field in my application….example
Class A
Readexceldata()
.
.
Some code.
.
.

Vl call that method in below class
Class B
A a1=new A()
String x=a1.readexceldata();

Send the above string to application using send keys

LoginPage lap=new LoginPage();
lap.send keys(x):

In such a situation how u differentiate between string data n numeric data…hope u got clear idea

on December 13, 2014.
Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.