Can some one please provide codes/logic to retrive excel(.xlsx) data like ""driver.findElement(By.id("")).sendkeys(getExceldata(row,column));"

Question

Can some one please provide codes/logic to retrieve Excel (.xlsx) data. I need the data to be retrieved in such a way that I can get the value and pass it any where in code for testing web page. This is the code how I need to get:

driver.findElement(By.id("")).sendkeys(getExceldata(row, column));

I just need to define the row and column and it should get the data from Excel sheet by using any method like getExceldata(1,2)

public String cellValue(String filepath,String sheetname,int r,int c)
{
        try{
            FileInputStream fis = new FileInputStream(new File(filepath));              
            book = WorkbookFactory.create(fis);
            sh = book.getSheet(sheetname);

            System.out.println(sh.getSheetName());              

            row = sh.getRow(r);
            cell = row.getCell(c);

            return cell.getStringCellValue();               
        }catch(Exception e)
        {
            return null;
        }
    }

    public int getRows(String filepath,String sheetname)
    {
     try{
        FileInputStream fis= new FileInputStream(filepath);
        book= new XSSFWorkbook(fis);

        return book.getSheet(sheetname).getLastRowNum();
     }
     catch(Exception e)
     {
         return 0;
     }

----------------------------New class below--------------------------------

WebDriver driver;

    Excel excel= new Excel();

    public static String filepath="‪D:\\Chinmaya Work\\Work Space\\simpleProject\\src\\Newcustomerdata.xlsx";
    public static String sheetname="newcusotomer";

    public void setUp() throws InterruptedException
    {
        driver=new FirefoxDriver();
        driver.get("http://www.demo.guru99.com/V4/index.php");
        Thread.sleep(5000);
    }

    public void loginToApplication()
    {
        Homepagegurru99 home=new Homepagegurru99(driver);

        home.enterUsername("mngr45812");
        home.enterPassword("chinu@221");
        home.clickLoogin();
        driver.manage().timeouts().implicitlyWait(60,TimeUnit.SECONDS);
    }

    public void gotoNewcustomepage()
    {
        Dashbardgurru99 dash=new Dashbardgurru99(driver);
        dash.gotonewCustomerpage();
        driver.manage().timeouts().implicitlyWait(60,TimeUnit.SECONDS);
    }

    public void createNewcustomer()
    {
        Newcustomeradd create=new Newcustomeradd(driver);           
        int rowCount=excel.getRows(filepath, sheetname);            
        System.out.println(rowCount);


        create.eneterCustomername(excel.cellValue(filepath, sheetname, 2,0));
        create.selectSex();
        create.enterDob(excel.cellValue(filepath, sheetname, 3,0));
        create.enterAddress(excel.cellValue(filepath, sheetname, 4,0));
        create.enterCity(excel.cellValue(filepath, sheetname, 5,0));
        create.enterState(excel.cellValue(filepath, sheetname, 6,0));
        create.enterPin(excel.cellValue(filepath, sheetname, 7,0));
        create.enterMobileno(excel.cellValue(filepath, sheetname, 8,0));
        create.enterEmail(excel.cellValue(filepath, sheetname, 9,0));
        create.enterPassword(excel.cellValue(filepath, sheetname, 10,0));
        create.clickSubmit();
        driver.manage().timeouts().implicitlyWait(60,TimeUnit.SECONDS);
        driver.switchTo().alert().accept();    

    }

    public void logOut()
    {
        driver.findElement(By.linkText("Log out")).click();
        driver.manage().timeouts().implicitlyWait(60,TimeUnit.SECONDS);
        driver.switchTo().alert().accept();

        driver.close();
    }

please let me know above code is fine or not..but i am not able to retrive data from excel ,getting values as null all the time. — chinmaya, Aug 19 '16 at 17:00

score 0 · Answer 1 · edited May 23 '17 at 12:32

Dependencies (poi-ooxml.jar)

import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

To Persist into an Excel File

fis = new FileInputStream(new File(fileName));
XSSFWorkbook workbook = new XSSFWorkbook (fis);
XSSFSheet sheet = workbook.getSheetAt(0);

XSSFRow row1 = sheet.createRow(0);
XSSFCell r1c1 = row1.createCell(0);
r1c1.setCellValue("Demo");

fis.close();

FileOutputStream fos = new FileOutputStream(new File(outputFile));
workbook.write(fos);
fos.close();

To Retrieve from an Excel File follow the link, Get Cell Value from Excel Sheet with Apache Poi .

HTH

Can some one please provide codes/logic to retrive excel(.xlsx) data like ""driver.findElement(By.id("")).sendkeys(getExceldata(row,column));"

1 Answers1