Framework For selenium WebDriver - Report Test Data Result In Excel

STEP 15
Prerequisite : Selenium WebDriver Framework creation steps from STEP 1 to STEP 14 should be Implemented as described In previous posts.

We have Implemented software test data skip function In STEP 14. Now let we go ahead one more step In selenium testing data driven framework creation. If you know, We have a "Pass/Fail/Skip" column In our excel data
sheets. That column Is reserved to report the software test data execution results like "PASS", "FAIL" or "SKIP". If you look at our test cases, We have not Introduced webdriver In our software test suites but we will Implement It very soon.

Read more tutorials on selenium WebDriver @Tutorials Part 1 and @Tutorials Part 2.

How To Report Test Data Execution Result In Excels
Same as reporting suite execution status as described In STEP 12, We will use "WriteResultUtility" function of SuiteUtility class In our test cases to report test data execution results In excel sheets. We will set flag "Testskip" = "True" In our software test cases (In @Test method) If test data has to skip. Same way, we will set "Testfail" = "True" If actual and expected values not match. And then we will create new method using testng's @AfterMethod  annotation In all software test cases to report the test data set execution results on end of every test data set execution. "Testskip" and "Testfail" flags will be used In @AfterMethod method to write the results In excel sheets.

Download Updated Test Cases and Excel Files
I have updated all 4 test cases files to report software test execution results In related data excel sheet. I have Implemented logic of Summation, Subtraction, Multiplication and Division of test data In software test cases to get actual result. Also updated few expected result data Intentionally wrong In test cases data sheets to report test data as "FAIL". Download bellow given zip folder to get all the files.
Extract the folder and copy-paste folder's files In "WDDF" project In Eclipse to replace existing files as described bellow.
(Note : It should replace existing file when paste It on package. It should not Insert new file.)
  • Paste SuiteOneCaseOne.java File on com.stta.SuiteOne Package.
  • Paste SuiteOneCaseTwo.java File on com.stta.SuiteOne Package.
  • Paste SuiteTwoCaseOne.java File on com.stta.SuiteTwo Package.
  • Paste SuiteTwoCaseTwo.java File on com.stta.SuiteTwo Package.
  • Paste SuiteOne.xls File on com.stta.ExcelFiles Package.
  • Paste SuiteTwo.xls File on com.stta.ExcelFiles Package.
Run Test Suites To Report Test Data Result
Now we are all set to run our test suites to verify that our test data result reporting function Is working fine or not.
Note : Close all excel files before running your test suite. Otherwise you will get an error during your test suite execution.

Now Run your test suites from testng.xml file. On completion of test execution, Open SuiteOne.xls and SuiteTwo.xls files from com.stta.ExcelFiles Package. You will see that all the data set line's "Pass/Fail/Skip" column Is updated with "PASS" or "FAIL" or "SKIP" as shown In bellow given Image.
  • If result Is "PASS" : Means expected and actual values are match.
  • If result Is "FAIL" : Means expected and actual values are not match.
  • If result Is "SKIP" : Means "DataToRun" flag Is set to "n" so that test data set execution has been skipped.

If you see In above Image, 4 test data line's has been reported as "SKIP" because their "DataToRun" flag Is set to "n". 3 test data lines has been reported as "PASS" because their expected and actual result has been match during test execution. 1 data line has been reported as "FAIL" because Its  expected and actual result do not match.

So we have successfully Implemented data set result reporting function In our selenium software automation framework which Is driven by external data sheet data.

22 comments:

  1. Thank you for the useful blog. I have encountered a couple of issues:
    1. In SuiteOne.xls sheet TestCasesList pass/fail/skip is not populated even if all tests pass.
    2. If test is configured to skip an entire test suite, the method which uses the data provider (e.g. SuiteTwoCaseTwoTest) is marked with a null pointer exception because parameters FilePath, TestCaseName are null. I think this is an issue with how TestNG handles data providers.

    ReplyDelete
    Replies
    1. 1. Test case list status update Is pending and you will get It In next or next to next post.

      2. I am not see any null pointer exception. Can you please tell me the exact configuration?

      Delete
    2. This comment has been removed by the author.

      Delete
  2. For 1. In file TestSuiteList.xls set SuiteToRun to Y for SuiteOne and N for SuiteTwo. Then execute by running testng.xml through Eclipse. When viewing index.html or "Results of running suite" entries for SuiteTwoCaseOneTest and SuiteTwoCaseTwoTest, the error message is
    SuiteTwoCaseOneTest java.lang.RuntimeException: java.lang.NullPointerException
    Caused by: java.lang.NullPointerException
    at com.stta.Utility.SuiteUtility.GetTestDataUtility(SuiteUtility.java:30)
    at com.stta.SuiteTwo.SuiteTwoCaseOne.SuiteTwoCaseOneData(SuiteTwoCaseOne.java:99)
    ... 21 more
    ... Removed 28 stack frames

    Thank you.

    ReplyDelete
    Replies
    1. I am trying to find out some solution for this. Let we Ignore It at this stage because It Is not affecting anyway to our test execution and reports.

      Delete
    2. Hope this is fixed now. May be in next steps?

      Delete
  3. I am also getting nullpointer exception.
    Detail TestNG stack trace is-
    java.lang.NullPointerException
    at com.stta.SuiteOne.SuiteOneCaseOne.SuiteOneCaseOneTest(SuiteOneCaseOne.java:58)
    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)
    at org.testng.internal.MethodInvocationHelper.invokeMethod(MethodInvocationHelper.java:84)
    at org.testng.internal.Invoker.invokeMethod(Invoker.java:714)
    at org.testng.internal.Invoker.invokeTestMethod(Invoker.java:901)
    at org.testng.internal.Invoker.invokeTestMethods(Invoker.java:1231)
    at org.testng.internal.TestMethodWorker.invokeTestMethods(TestMethodWorker.java:127)
    at org.testng.internal.TestMethodWorker.run(TestMethodWorker.java:111)
    at org.testng.TestRunner.privateRun(TestRunner.java:767)
    at org.testng.TestRunner.run(TestRunner.java:617)
    at org.testng.SuiteRunner.runTest(SuiteRunner.java:335)
    at org.testng.SuiteRunner.runSequentially(SuiteRunner.java:330)
    at org.testng.SuiteRunner.privateRun(SuiteRunner.java:291)
    at org.testng.SuiteRunner.run(SuiteRunner.java:240)
    at org.testng.SuiteRunnerWorker.runSuite(SuiteRunnerWorker.java:52)
    at org.testng.SuiteRunnerWorker.run(SuiteRunnerWorker.java:86)
    at org.testng.TestNG.runSuitesSequentially(TestNG.java:1224)
    at org.testng.TestNG.runSuitesSequentially(TestNG.java:1220)
    at org.testng.TestNG.runSuitesLocally(TestNG.java:1149)
    at org.testng.TestNG.run(TestNG.java:1057)
    at org.testng.remote.RemoteTestNG.run(RemoteTestNG.java:111)
    at org.testng.remote.RemoteTestNG.initAndRun(RemoteTestNG.java:204)
    at org.testng.remote.RemoteTestNG.main(RemoteTestNG.java:175)

    Note- i have followed all steps (1-15) as mentioned. It was working fine upto Step-14.Once i pasted the .java files mentioned in Step-15 folder,i am getting the issue.

    Can you pls help me out?

    Regards
    Abhisek

    ReplyDelete
    Replies
    1. Let us Ignore It right now because It Is not affecting anyway to our test execution and reports.

      Delete
  4. hi

    how to use Constant to replace "Pass/Fail/Skip" column like
    public static final String KEYWORD_PASS = "PASS";
    public static final int COL_TEST_CASE_RESULT = 10;

    so it will become
    SuiteUtility.WriteResultUtility(FilePath_TestResult, TestCaseName, Constant.COL_TEST_CASE_RESULT, DataSet+1, Constant.KEYWORD_PASS);

    ReplyDelete
  5. Hi, in apache POI HSSF

    how to set cell color Pass = green, Fail = red?

    ReplyDelete
  6. This helps you to generate reports in Excel

    ReplyDelete
  7. Hi, Mr. Acavind,
    I'm good till now. just want let you know I think you are a great guy in the world.

    Mak

    ReplyDelete
  8. nothing running in your project at this stage with all current API.

    ReplyDelete
    Replies
    1. Please follow steps properly as many people are able to implement it successfully. Read latest comment(31 May 2016) given by "JMan" on this page -> http://www.software-testing-tutorials-automation.com/2014/09/capture-screenshot-on-failure-or-pass.html?showComment=1464686583939#c8210083461956219711

      Delete
    2. i am using XSSF for excel sheet

      Delete
  9. all of excel getting corrept just running project

    ReplyDelete
  10. can you upload new code with current API

    ReplyDelete
  11. I use data type String not integer when running results always "failed" in excel

    ReplyDelete
  12. it works when i run the test individually and then i run through testng xml. Even then, it doesn't work for other test cases until i run them individually.

    ReplyDelete
  13. Hi Arvind,

    I just tried step#15 and I am still facing same issue which was reported earlier. I
    am not seeing the test results in the excel file with pass/fail status.

    Thanks
    Prashant

    ReplyDelete
  14. Hi Arvind,

    I just tried step#15 but I don't see Pass/Fail results in excel sheet.

    Thanks
    Prashant

    ReplyDelete
  15. How can we report all sheet results in one sheet instead of each sheet for each test case.

    ReplyDelete