Create Required .xls Files For Selenium WebDriver Data Driven Framework

STEP 6

Prerequisite : All required class files should be created as described In STEP 5.

After creating all required classes under different packages, Now It Is time to create required .xls files. Please note here that this framework will not support .xlsx files but It will support only .xls files so please create only .xls files as described bellow. In our selenium WebDriver software testing Framework, We will
Programmatically read the different .xls files to retrieve Suites Name, SuiteToRun flags, Test cases names, CaseToRun flags, Test data for different test cases and many more things and then feed all these data to our test suites and test cases. Also we will write the results or execution status In .xls files at the end of test cases. Anyways, Don't consider all these things at this stage because we will learn all these things step by step In my upcoming posts. Right now your task Is to add bellow given three .xls files under com.stta.ExcelFiles package.

Click on bellow given links one by one to download .xls files and then add them In com.stta.ExcelFiles ("E:\backup\Training\WDDF\src\com\stta\ExcelFiles") package Folder.

  1. TestSuiteList.xls
  2. SuiteOne.xls
  3. SuiteTwo.xls
Now "com.stta.ExcelFiles" package will looks like bellow.


Note : Don't change any thing In any .xls files.

"TestSuiteList.xls" File Explaination
Now open "TestSuiteList.xls" file. If you look In file, 1st sheet's name Is "SuitesList". Inside "SuitesList" sheet, There are total three columns as bellow.
  1. SuiteName : It will be used for listing all available test suites. Initially we are using only two test suites In our software testing data driven framework creation so listed only two test suites(SuiteOne and SuiteTwo). Later on you can add more test suites In this list.
  2. SuiteToRun : This column will be used to decide that specific test suite should be executed or not. If set "Y" In this column then that specific test suite will be executed. If set "N" In this column describes that specific suite will not execute or you can say you wants to skip execution of that specific test suite.
  3. Skipped/Executed : This column will be used to write specific test suite was executed or not. Right now It Is blank but It will be set to "Skipped" or "Executed" during test suite execution.



"SuiteOne.xls" and "SuiteTwo.xls" Files Explaination
If you will open any of the file from "SuiteOne.xls" and "SuiteTwo.xls", 1st sheet name will be "TestCasesList". This sheet has total three columns as bellow.
  1. TestCaseName : Test cases of that specific suite will be listed In this column.
  2. CaseToRun : You can set test case execution mode In this column.
  3. Pass/Fail/Skip : Test case "Pass" or "Fail" or "Skip" status will be reported automatically In this column at the end of each test case execution.

There are more two sheets Inside "SuiteOne.xls" and "SuiteTwo.xls" files. Both those sheets will be used to store test data for specific test case of software application. Example : In "SuiteOne.xls" file, "SuiteOneCaseOne" sheet will be used to store data of test case class "SuiteOneCaseOne.java". Same way "SuiteOneCaseTwo" sheet will be used to store data of test case class "SuiteOneCaseTwo.java". Same thing apply for "SuiteTwo.xls" file.

Rule : 
Please Remember always :- Test class name of your eclipse project(Example : SuiteOneCaseOne.java), TestCaseName In "TestCasesList" sheet(SuiteOneCaseOne) and Test Data Sheet name (SuiteOneCaseOne) must be always same as shown In bellow given Image. So when ever you create new test case under any test suite In this framework, Keep this thing In your mind.



Test Data Sheets Explanation
If you will open any data sheet(Example : SuiteOneCaseOne) from "SuiteOne.xls" and "SuiteTwo.xls" files, you will see bellow given columns In each sheet.
  1. Data 1 Column, Data 2 Column, Data 3 Column : All these three columns will be used to store test data for that specific test case for software web application. You can Increase or decrease these column's name as per your test case data requirement.
  2. Expected Result Column : You can store expected result data In this column to compare with actual result.
  3. DataToRun : This column will be used to decide that specific data line should be executed or not. If set "N" then that specific data set line will be not executed. If set "Y" then that specific data set line will be executed.
  4. Pass/Fail/Skip : Result will be reported automatically In this column As per expected and actual result matching criteria. Result can be "Pass" or "Fail" or "Skip".

This way, You need to add above given .xls files with specified format for your data driven framework of software web application. Initially we will add only two test suites each having two test cases. Each test case have two data sets.

21 comments:

  1. add excel in folder not able to view in tree view of eclipse.. kindly help

    ReplyDelete
    Replies
    1. Right Click on the project and select refresh button. Also check if you have the files present in G:\backup\training\src\com\stta\ExcelFiles ( G can be replaced by the drive of your system)

      Delete
  2. Yes..I am also getting the same.

    ReplyDelete
  3. Verify if you have added the excel files in the right folder.
    i.e. it should be under [Package]/src/com/stta/ExcelFiles if you are following the notation specified by the author.
    Refresh the Package explorer after you add them
    It should be visible after that.

    ReplyDelete
  4. Many Thanks Aravind. All this tutorial are really helpful.

    ReplyDelete
  5. I have added the files in the Correct folder location and refreshed the package in Eclipse but still unable to view the files from Eclipse. Please help. Thanks in Advance.

    ReplyDelete
    Replies
    1. not able to open the excel file in eclipse getting the following error
      This is probably because there is no OLE editor registered against the type of file you were trying to open.
      Failed to create Ole Client. result = -2147221164
      please help me on this issue.

      Delete
    2. No need to open the excel file from the eclipse. You may open it by navigating to the folder G:\backup\training\src\com\stta\ExcelFiles ( you can replace G: with your drive)

      Delete
  6. Hi Aravind, what modification we should do so that the framework supports .xlsx fil as well?

    ReplyDelete
  7. not able to open the excel file in eclipse getting the following error
    This is probably because there is no OLE editor registered against the type of file you were trying to open.
    Failed to create Ole Client. result = -2147221164
    please help me on this issue

    ReplyDelete
  8. Go to Window >> Preferences >> General >> Editors >> File Associations, click 'Add' right to the File Types list, add .xls, OK.

    Now select .xls from the list, click 'Add' right to the Associated editors list, select External Programs Check Box, select Microsoft Excel, OK.


    Don't thank me. Thanks this site rather.

    http://stackoverflow.com/questions/26002978/preventing-eclipse-from-opening-external-program-inside-eclipse-editor

    ReplyDelete
  9. Thanks for helping..
    Really great tutorials. Step by step and easy way of explanation.

    ReplyDelete
  10. I am getting below when running SuiteOneCaseOne.java. Not sure if I missed step or configured wrong.
    C:\BackUp\Training\WDF\src\com\stta\ExcelFiles\TestSuiteList.xls (The system cannot find the file specified)
    at java.io.FileInputStream.open0(Native Method)
    at java.io.FileInputStream.open(Unknown Source)
    at java.io.FileInputStream.(Unknown Source)
    at java.io.FileInputStream.(Unknown Source)
    at com.stta.utility.Read_XLS.(Read_XLS.java:25)
    at com.stta.TestSuiteBase.SuiteBase.init(SuiteBase.java:14)
    at com.stta.SuiteOne.SuiteOneCaseOne.checkCaseToRun(SuiteOneCaseOne.java:20)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)

    ReplyDelete
    Replies
    1. At this point of tutotrial , we have nothing written inside the classes. How you are running this class SuiteOneCaseOne.java ? Is there some code inside the class ?

      Also did you change the name from WDDF to WDF ?

      Delete
  11. m nt gettng xls file frm com.stta.Excelfiles package..please somebody help me ..m tryng to do add xls file going through windows > preference > general > editor > assotiations but nt gettng result

    ReplyDelete
    Replies
    1. @sibani Goto G:\backup\training\src\com\stta\ExcelFiles and add excel files by pasting there. and refresh your project you will be able to see

      Delete
  12. My requests to download the excel files are denied. Can you please help? Thanks.

    ReplyDelete