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.
at July 13, 2014
Related Articles : , , , , ,

35 comments:

  1. Unknown11 August 2014 at 06:05

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

    Thanks,
    Anu.

    ReplyDelete
    Replies
    1. Unknown18 July 2015 at 22:10

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

      Delete
    2. umang rawal22 September 2015 at 23:31

      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. Unknown20 August 2014 at 10:10

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

    ReplyDelete
  3. Unknown31 August 2014 at 12:48

    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. Aravind31 August 2014 at 18:46

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

      Delete
    2. Unknown1 September 2014 at 06:35

      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. Tarun2 October 2014 at 03:08

    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. Unknown18 July 2015 at 22:30

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

      Delete
  5. kalyani2 January 2015 at 02:34

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

    ReplyDelete
    Replies
    1. Aravind2 January 2015 at 07:44

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

      Delete
  6. Unknown5 March 2015 at 00:28

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

    -Jesse

    ReplyDelete
  7. Unknown5 March 2015 at 00:30

    Super like for the Automation Framework Explanation.

    ReplyDelete
  8. Manju1 July 2015 at 08:00

    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. Unknown17 September 2015 at 19:44

    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. Unknown21 September 2015 at 01:44

    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. Aravind21 September 2015 at 08:52

      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. Unknown22 September 2015 at 17:14

      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. Aravind22 September 2015 at 17:55

      Yes you can write..

      Delete
    4. Unknown23 September 2015 at 17:51

      I got the idea, many thanks Aravind!

      Delete
  11. rulzzz7 January 2016 at 22:45

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

    ReplyDelete
  12. Unknown4 March 2016 at 22:41

    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. Gaurav chaudhari28 April 2016 at 05:28

    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. Gaurav chaudhari5 May 2016 at 05:20

    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. Unknown30 June 2016 at 10:53

    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. Unknown30 June 2016 at 10:57

    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. Unknown24 January 2017 at 23:18

    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. 蒼墨空间26 April 2017 at 01:09

      Hi, May I know how u downloaded the java files?

      Delete
  18. Unknown24 January 2017 at 23:25

    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. 蒼墨空间26 April 2017 at 01:08

      Hi, may I know how u download the java file?

      Delete
    2. 蒼墨空间26 April 2017 at 01:08

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

      Delete
  19. Unknown20 April 2018 at 01:49

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

    Mohd Yusuf

    ReplyDelete
  20. Unknown6 August 2018 at 04:49

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

    ReplyDelete
  21. Unknown26 March 2019 at 21:11

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

    ReplyDelete
  22. Jalpesh Patel14 April 2019 at 12:54

    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

Subscribe to: Post Comments (Atom)