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

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 comments:

  1. I am getting red cross marks while pasting above two files.

    Thanks,
    Anu.

    ReplyDelete
    Replies
    1. hiiee...me too facing the samd issuee.can u help me,how to fix this

      Delete
    2. Hi.. Create new Java project with selecting 'JavaSE-1.7' in JRE section and then follow all steps.. I was facing same issue and it resolved by following this step..

      Delete
  2. i want complete excelsheet can u pls send me.
    mukesh89jh@gmail.com

    ReplyDelete
  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.

    ReplyDelete
    Replies
    1. Do you have followed step 1 to 6 properly? anyone else Is not getting such error. Please follow proper steps.

      Delete
    2. 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.

      Delete
  4. 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??

    ReplyDelete
    Replies
    1. Me too have the same doubt..can anyone explain this logic?

      Delete
  5. While downloading SuiteUtility.java file, it displayed 'Apologies.
    There is no preview available' message. Can you please provide file again?

    ReplyDelete
    Replies
    1. click on download button bellow There is no preview available' message. It will download file for you..

      Delete
  6. You are a Gem of Person.Superlike for your blog.

    -Jesse

    ReplyDelete
  7. Super like for the Automation Framework Explanation.

    ReplyDelete
  8. 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.

    ReplyDelete
  9. 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

    ReplyDelete
  10. 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?

    ReplyDelete
    Replies
    1. Yes.. Suiterow.getCell(i).getStringCellValue() will get cell value..

      You can debug steps by step. You will get Idea why we need It.

      Delete
    2. Could you please explain more on debug? Is it right that I should write a main method insides Read_XLS.java to debug?

      Delete
    3. I got the idea, many thanks Aravind!

      Delete
  11. Hi Arvind Location of your files are missing . Can you kindly Update it

    ReplyDelete
  12. 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. :)

    ReplyDelete
  13. Cant reach to site error when click on download Read_xls.java link, can someone please provide me the correct link or code?

    ReplyDelete
  14. This site can’t be reached

    The connection was reset.
    ERR_CONNECTION_RESET when click on Download Read_XLS.java File

    Can someone please help me on it?

    ReplyDelete
  15. 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.

    ReplyDelete
  16. 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.

    ReplyDelete
  17. 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

    ReplyDelete
    Replies
    1. Hi, May I know how u downloaded the java files?

      Delete
  18. 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

    ReplyDelete
    Replies
    1. Hi, may I know how u download the java file?

      Delete
    2. Hi, May I know how u get the java files?

      Delete
  19. Awesome, best explanation I have ever gone through. Thank you so much.

    Mohd Yusuf

    ReplyDelete
  20. Read_XLS.java - in eclipse at line 174, 190 272 shows warninig MEthod type is Deprecated. need to revise the file

    ReplyDelete
  21. Please help me getting problem in Read_XLS Showing error in CELL_TYPE_STRING .I am using SE 1.8.Please help

    ReplyDelete
  22. 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!

    ReplyDelete