Add .xls File Reading And Writing Utility In WebDriver Data Driven Framework

By AravindSelenium 2, selenium webdriver, WebDriver, WebDriver Datadriven Framework, WebDriver Examples, webdriver tutorials
STEP 7

Prerequisite : All the steps from STEP 1 to STEP 6 should be followed properly as described.
Till now we have performed many different actions on selenium webdriver software automation testing framework creation – Starting from work space creation to .xls files creation In STEP 1 to STEP 6. Now Its time to Introduce some

coding stuff In our webdriver software automation testing data driven framework creation. As you know, We have to read different types of .xls files(As described In STEP 6) In different ways In our software automation testing data driven framework. For reading data from .xls files and writting results In .xls files, We are going to use apache POI API.


Add “Read_XLS.java” File In “com.stta.utility” Package
To read data In specific format from .xls files and write results In .xls files, I have prepared Read_XLS.java class file using Apache POI API. This file contains many different functions as listed bellow which will helps you to work with .xls files.

Read_XLS.java Functions
  • retrieveNoOfRows(String wsName) :- It will return No of Rows from worksheet of .xls file.
  • retrieveNoOfCols(String wsName) :- It will return No of Columns from worksheet of .xls file.
  • retrieveToRunFlag(String wsName, String colName, String rowName) :- You can use this function to read “SuiteToRun” flag from “SuitesList” sheet and “CaseToRun” flag from “TestCasesList” sheet.
  • public String[] retrieveToRunFlagTestData(String wsName, String colName) :- This function will help you to read “DataToRun” flag from different test cases data sheets.
  • public Object[][] retrieveTestData(String wsName) :- This function will helps you to read Test Data from different test cases data sheets.
  • public boolean writeResult(String wsName, String colName, int rowNumber, String Result) :- This function will helps you to write Pass/Fail/Skip results In “TestCasesList” sheets and test cases data sheets.
  • public boolean writeResult(String wsName, String colName, String rowName, String Result) :- This function will helps you to write execution status Skipped/Executed In “SuitesList” sheet.

You will get all above listed features from Read_XLS.java file. 
To add Read_XLS.java file In your webdriver software automation testing framework, Follow the bellow given steps.
  • Download Read_XLS.java File
  • Copy “Read_XLS.java” file from downloaded location and paste It On “com.stta.utility” package of “WDDF” project In Eclipse. It will ask for overwriting file – Click on “Yes” as shown bellow. It will add Read_XLS.java file In your framework’s “com.stta.utility” package.
Now You can open “Read_XLS.java” file In eclipse to view all above listed functions and we are ready to use them In our framework.
Add “SuiteUtility.java” File In “com.stta.utility” Package
Function of “SuiteUtility.java” File

“SuiteUtility.java” file will work as Intermediate file between “Read_XLS.java“ file and your test suite or test cases files. It will get data reading or data writing requests from test suite or or test cases files and send all those requests to “Read_XLS.java“. And then “Read_XLS.java” will perform required data reading or data writing action on targeted sheet.
To add SuiteUtility.java file In your framework, Follow the bellow given steps.

  • Download SuiteUtility.java File. 
  • Copy “SuiteUtility.java” file from downloaded location and paste It On “com.stta.utility” package of “WDDF” project In Eclipse. It will ask for overwriting file – Click on “Yes” as shown bellow. It will add SuiteUtility.java file In your framework’s “com.stta.utility” package.
Now our framework Is ready to read data from different targeted .xls files and write results In targeted .xls file.

35 thoughts on “Add .xls File Reading And Writing Utility In WebDriver Data Driven Framework”

  3. I'm gettgin error in "CellTo String" mehod at "case 0 :
    result = cell.getNumericCellValue();" showing error — "
    Type mismatch – Cannot convert from double to Object. "
    Could you please help me on this.

    Reply

  5. Hi, This is fixed by changing JRE compliler to 1.7 which was asking for to set to 1.5 first and changed manually to 1.7 in projet properties. Not sure why this is happenning, but I'm doing this change for all TESTNG projects.

    Reply

  6. Why should we use SuiteUtility class as an inter-mediator between Read_XLS class and test cases? Can't we access Read_XLS directly from test cases file? Logic between two/three (including test cases files) classes are hard to understand for me.
    Can you suggest any method to understand these logic??

    Reply

  11. 1. In Read_XLS.java , there is function retrieveNoOfRows (String wsName),
    here wsName is declared, but where this is assigned

    eg:int sheetIndex=wb.getSheetIndex(wsName);

    here what i should consider this wsName as where this is assigned.

    2.Similarly i get the same doubt under function
    retrieveToRunFlag(String wsName, String colName, String rowName)

    here where these wsName,colName,rowName are assigned?

    Pls help me to clear my doubt.

    Reply

  14. Hi Aravind,
    I'm kinda beginner here so the Read_XLS.java is quite hard to understand. Could you please take some time to explain so all beginners here can follow easier.

    Thanks,
    MF

    Reply

  15. Hi Aravind,
    Could you please explain the purpose of this code:
    if(Suiterow.getCell(i).getStringCellValue().equals(colName.trim())){
    colNumber=i;
    }

    Is it right that Suiterow.getCell(i).getStringCellValue() is to get the cell value? Why do we compare it with column name?

    And why we set colNumber=i when both values equal?

    Reply

  22. Hi everyone,
    I got the errors too, I tried to add all jar files to Libraries as usual instead of in "JarFiles" folder in step 4. Then I can run the project successfully in Step 8. So, let try that one. Good luck. 🙂

    Reply

  25. Hi Arvind,
    In your example you are using only 3 columns data. If someone wants to use more columns in their testing then where exactly we have to make changes in Read_XLS script?
    ie. I have 5 columns data for my testing when I use your excel script, it says the data provider is trying to pass 3 parameters but the method takes 2.

    Reply

  26. Hi Arvind,
    In your example you are using 3 column, but if someone need to use more column's data for their testing then where we have to make changes in Read_XLS script?
    ie. I have 8 column's data, when I run same code as link yours, it gives me error stating that the data provider is trying to pass 3 parameters but the method takes 2.

    Reply

  27. Hi,
    i am getting an error in Read_xls.java at line number 41 as"The method GetTestDataUtility(Read_XLS, String) is undefined for the type SuiteUtility" kindly resolve my issue

    Reply

  28. Hi,
    i am getting an error in Read_xls.java in line number 41 as "The method GetTestDataUtility(Read_XLS, String) is undefined for the type SuiteUtility" pls resolve my issue

    Reply

  34. Hello I am getting an error as cell.getCellType() and suggestion indicate that Change type of 'type' to 'CellType'. When I change type as per suggestion, I am getting an error in case as type mismatch. And also getting an error as CELL_TYPE_STRING cannot be resolved or is not a field.

    Please guide me!

    Thank You!

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *