Selenium Webdriver How to select records from table by fetching Excel Input

Question

im struggeling for below scenario.

Application displayed records of 100 suppliers in one table have three columns namely as ID,Company name and Subscription name.
i want to take input from my excel sheet say company name"xyz" and using that input i have to click on subscription name details link so application will navigates me next page.

Sample code i have created as below:

`public static void main(String[] args) throws BiffException, IOException, Exception { WebDriver driver = new FirefoxDriver(); driver.manage().window().maximize(); driver.manage().timeouts().implicitlyWait(30,TimeUnit.SECONDS); //Workbook location Workbook wBook = Workbook.getWorkbook(new File("C:\Users\amit.bhagwat\Documents\TestData\SampleData.xls")); //get sheet jxl.Sheet Sheet = wBook.getSheet(0);

    //loop
    for(int i=1; i<Sheet.getRows(); i++)

        {
    driver.get("http://206.132.42.243/Web");
    driver.manage().timeouts().implicitlyWait(30,TimeUnit.SECONDS);
    driver.findElement(By.xpath("//input[@id='UserName']")).sendKeys(Sheet.getCell(0, i).getContents());
    driver.manage().timeouts().implicitlyWait(30,TimeUnit.SECONDS);
    driver.findElement(By.xpath("//input[@id='Password']")).sendKeys(Sheet.getCell(1, i).getContents());
    driver.findElement(By.xpath("//input[@id='Password']")).sendKeys(Sheet.getCell(1, i).getContents());
    Thread.sleep(40);
    driver.findElement(By.xpath("//input[@name='Login']")).click();
    driver.manage().timeouts().implicitlyWait(30,TimeUnit.SECONDS);
    driver.manage().timeouts().implicitlyWait(30,TimeUnit.SECONDS);
    driver.findElement(By.xpath("//a[contains(text(),'Task')]")).click();
    driver.manage().timeouts().implicitlyWait(30,TimeUnit.SECONDS);
    driver.findElement(By.xpath("//a[contains(text(),'Data Checking')]")).click();
jxl.Sheet Sheet2 = wBook.getSheet(0); 
    WebElement kancheck = driver.findElement(By.name("Grant & Brown"));
    kancheck.click();
    System.out.println(kancheck.isSelected());
    driver.findElement(By.xpath("//a[contains(text(),'Data Checking')]")).sendKeys(Sheet2.getCell(1, i).getContents());
    Thread.sleep(40);` enter code here

Fahim Hossain · Accepted Answer · 2014-10-16 10:02:49Z

As far as I could understand, you are trying to read the file from a remote location and then read the information from it. It would be a good practice if you can use Apache POI library to read contents at run-time.

In my project, I read all the contents from an excel sheet usingApache POI library to set the values of my variables. Here is a code snippet on how i achieved it. Hopefully this will guide you to a proper solution. :)

    public void readExcelDoc() throws FileNotFoundException, IOException
{

    POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream("excelDoc//scripts.xls"));
    HSSFWorkbook wb = new HSSFWorkbook(fs);
    HSSFSheet sheet = wb.getSheetAt(0);
    HSSFRow row = null;
    HSSFCell cell = null;

    int rows = 0; // No of rows
    //      rows = sheet.getPhysicalNumberOfRows();
    rows = sheet.getLastRowNum();

    int cols = 2; // No of columns
    int tmp = 0;

    // This trick ensures that we get the data properly even if it doesn't start from first few rows
    for(int i = 0; i < 10 || i < rows; i++) {
        row = sheet.getRow(i);
        if(row != null) {

            tmp = sheet.getRow(i).getPhysicalNumberOfCells();

            if(tmp > cols) cols = tmp;
        }

    }
    int testRowNo = 0;
    String rowName = "Test Name";
    String columnValue = " ";
    //Iterate through Row and columns here. Excluding 1st row for title names
    for(int r = 1; r <= rows; r++) {

        row = sheet.getRow(r);
        if(row != null) {
            //Browse through columns using c
            for(int c = 0; c < cols; c++) {                     
                if(c==0) //Only taking data from Cell 0; Ignoring any other inputs
                {           
                    cell = row.getCell((short)c);
                    try
                    {
                        if(cell.getStringCellValue().contains(rowName)) 
                        {           
                            testRowNo =row.getRowNum();
                        }   

                    if(testRowNo > 0 )
                    {
                        if(cell.getColumnIndex() == 0 && row.getRowNum() > testRowNo && cell.getStringCellValue().length() !=0)
                        {
                            try{
                                String cellValue = cell.getStringCellValue().toLowerCase();
                                //System.out.println(cellValue);
                                scriptType.add(cellValue);
                            }
                            catch(IllegalStateException e)
                            {
                                e.printStackTrace();
                                scriptType.add(cell.getStringCellValue());
                            }
                        }
                    }
                    }
                    catch(NullPointerException e)
                    {

                    }

                }
                if(c==1)
                {
                    cell = row.getCell((short)c); //this sets the column number
                    if(testRowNo == 0)
                    {       
                        try{
                            String cellValue = cell.getStringCellValue();
                            //System.out.println(cellValue);
                            columnValue = cellValue;
                        }
                        catch(IllegalStateException e)
                        {                           
                            String cellValue = cell.toString();
                            columnValue = cellValue;                                            
                        }
                        catch(NullPointerException e)
                        {
                            String cellValue = nodata;
                            columnValue = cellValue;
                        }
                    }
                }
                if(c==2)
                {
                    cell = row.getCell((short)c); //this sets the column number
                    if(testRowNo == 0)
                    {

                        try{
                            String cellValue = cell.getStringCellValue();
                            //System.out.println(cellValue);
                            inputParameters.put(cellValue, columnValue);
                        }
                        catch(IllegalStateException e)
                        {                           
                            String cellValue = cell.toString();
                            inputParameters.put(cellValue, columnValue);                                        
                        }
                        catch(NullPointerException e)
                        {
                            String cellValue = nodata;
                            inputParameters.put(cellValue, columnValue);
                        }
                    }
                }

            }
        }
    }
    System.out.println("---------The parameters set from excel are :  ---------");
    @SuppressWarnings("rawtypes")
    Iterator iterator = inputParameters.keySet().iterator();  

    while (iterator.hasNext()) {  
        String key = iterator.next().toString();  
        String value = inputParameters.get(key).toString();  
        System.out.println(key + " : " + value);  
    } 
}

Hi Fahim appreaciated for your reply but im able to read information from excel sheet the issue on web application only im not able to select those LINK as i mentioned (subscription details) of any records bcoz every records have same link so how to select link of say records number 29 whos company name is "ABC ". in excel sheet i have provded only company name so selenium should get these company name as input value from my excel sheet and then it will select the subscription link in application thats what a expecting but not able to go so far...need small hint only rest i will do it...
If I got you right this time, then you are able to read all the contents from the excel sheet. Then what you should do is to iterate through the list of company names in the excel sheet and save it in an array in one step . Afterwards, send the array[index] to your .sendKeys function.

Collectives™ on Stack Overflow

Selenium Webdriver How to select records from table by fetching Excel Input

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related