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

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.

    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.

    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.

    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.

    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.

    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.