0

So I'm trying to store a web element such as id in an excel spreadsheet, I then want to call the id from the sheet and place it in a test.

Would this work? The theory is then that others with less tech knowledge can modify tests within my workplace without editing code.

I am using the following code to read from the spreadsheet and place the cell data into a string. 

    File src=new File("C:\\Users\\Admin\\Documents\\ExcelData\\TestData.xlsx");
    FileInputStream fis=new FileInputStream(src);

    XSSFWorkbook wb=new XSSFWorkbook(fis);
    XSSFSheet sheet1=wb.getSheetAt(0);

    String data0=sheet1.getRow(0).getCell(0).getStringCellValue();

The difficulty I am having is when testing for example.

driver.findElement(By.name("q")).sendKeys("Java");

if I replace this with

driver.findElement(By.name(data0).sendKeys("Java");

this will not work.

Krishnan Mahadevan
  • 14,121
  • 6
  • 34
  • 66
Russ
  • 1
  • 1
  • It should but is this the best option? Please do some research first, add the code you tried and be more specific.I think you can find other type of files that would work better than excel. See similar question http://stackoverflow.com/questions/15641666/java-programming-best-way-to-save-read-data-for-an-application – lauda Nov 09 '16 at 10:08
  • Thanks for taking the time to respond Lauda. I've been asked by a client to specifically use a spreadsheet to store the data, they will then edit the cell for example cell C3 from "x" to "y" to change the text box they're automating. The idea is the code will call cell C3 for the id in the test. Sorry if my questions are silly but I've only really been using Java/Selenium for a couple of weeks. – Russ Nov 09 '16 at 10:16
  • You need to find a library that will help you to read from excel then, check fire libraries and see wich one is simplest to use,also check this similar question for java and excel http://stackoverflow.com/questions/1516144/how-to-read-and-write-excel-file-in-java – lauda Nov 09 '16 at 10:23
  • I don't think so this would be the good approach, as in very rare cases ID would gets change in HTML. and while tracing and any change in test would complex the things mean you have to take care excel sheet and test side by side. – Umais Gillani Nov 09 '16 at 10:28
  • I've been using Apache POI to read a spreadsheet. The following code has worked fine. File src=new File("C:\\Users\\Admin\\Documents\\ExcelData\\TestData.xlsx"); FileInputStream fis=new FileInputStream(src); XSSFWorkbook wb=new XSSFWorkbook(fis); XSSFSheet sheet1=wb.getSheetAt(0); String data0=sheet1.getRow(0).getCell(0).getStringCellValue(); This reads the spreadsheet and stores the data from the cell within "data0" – Russ Nov 09 '16 at 10:30

1 Answers1

0

Yes, you can get the input data from the external sheet as you asked. This can be done using Apache POI Jars support. Hope you are coding in Java. The below link gives you enough code to your requirement. -> Apache POI Examples

Code to read the data from Excel.

File file= new File(PATH);
FileInputStream inputStream = new FileInputStream(file);
Workbook workbook =new XSSFWorkbook(inputStream);
Sheet sheet = workbook.getSheet(SHEETNAME);
String[][] number=new String[10][10]; // temp array
for(int i=0;i<rowCount+1;i++)
    {
        Row row=s.getRow(i);        
        for(int j=0;j<=row.getLastCellNum();j++)
        {
             number[i][j]=  row.getCell(j).toString(); // works only for cells of string type
         }
    }

Then you fetch data from number array.

Chandra Shekhar
  • 664
  • 2
  • 9
  • 24
  • Thanks for your reply. I have managed to read from the spreadsheet already using Apache POI, but I'm struggling to pass the variable into the test in order to find the web element on the page. – Russ Nov 09 '16 at 10:51
  • @Russ , You need to provide some code you tried/ Exceptions faced – Chandra Shekhar Nov 09 '16 at 10:54