Generate Excel Report in Selenium using TestNG




In software test automation after completion of the test suite execution we need to have a report for the execution status and it is the only way of evidence for the pass and fail status of the tests. And most of the clients bother about the detailed report of the execution status. Most of the licensed automation testing tools will have in built rich report option to show the execution status. But coming to open source automation tools lacking in this option. Same way, Selenium also does not have the automation report generation option. But to execute the test suite, selenium is depending on unit testing frameworks like TestNG and Junit.

After test suite execution using TestNG or Junit, by default they will give the HTML reports, but the information provided by these reports are not so good. And apart from these reports sometimes there is need to generate Excel Reports as people habituated to Excel. And the information should be very clear and should show the summary also for the PASS, FAIL and SKIP status of the entire test suite in the form of PIE chart. To generate these kind of reports we need to write so much of logic. But to avoid that and generate nice Excel reports we can download this ATExcelReport jar file and can generate the excel report. And this report has dependency with the TestNG. So, to generate this kind of report we need to run the entire test suite using TestNG before generating the excel report.

To generate excel report we will take two sample scripts and will execute the same with testng.xml file first then we can generate the excel report. Here, for each test tag in the testng.xml file; it will create separate sheet in the excel and will provide the test result in that sheet. Apart from this result, it will create a sheet called Summary sheet and it will give the total tests pass fail status in the form of PIE chart.




Below are the sample scripts:

First Sample Script:

import org.testng.Assert;
import org.testng.SkipException;
import org.testng.annotations.AfterSuite;
import org.testng.annotations.Test;

public class TestClassOne
{
    @Test
    public void testMethodPass()
    {
   	 Assert.assertTrue(true);
    }
    
    @Test
    public void testMethodFail()
    {
   	 Assert.assertTrue(false);
    }
    
    @Test
    public void testMethodSkip()
    {
   	 throw new SkipException("Skipped Intentionally");
    }
}




Second Sample Script:

import org.testng.Assert;
import org.testng.SkipException;
import org.testng.annotations.Test;

public class TestClassTwo
{
    @Test
    public void testMethodPass()
    {
   	 Assert.assertTrue(true);
    }
    
    @Test
    public void testMethodFail()
    {
   	 Assert.assertTrue(false);
    }
    
    @Test
    public void testMethodSkip()
    {
   	 throw new SkipException("Skipped Intentionally");
    }
}

Now will execute both the tests using testng.xml file. For each test we will create a separate test tag. Below is the testng.xml file to execute above tests.

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE suite SYSTEM "http://testng.org/testng-1.0.dtd">
<suite name="Suite">
  <test name="TestOne">
    <classes>
      <class name="TestClassOne"/>
    </classes>
  </test>
  <test name="TestTwo">
    <classes>
      <class name="TestClassTwo"/>
    </classes>
  </test> 
</suite>

After completion of the execution, we need to create a separate class and need to write the below code to generate the excel report. We have a Class called “Xl” and two static methods called “generateReport(“folderLocation”, “xlFileName”)” and “Xl.generateReport(“xlFileName”)”. These two methods are overloaded methods. First method will accept two parameters; one is to mention the folder location where we want to generate the excel report and the second is excel file name what we want to put. And the second method accepts only one parameter that is excel report name what we want to put and the file will be saved under the test-output folder which will generate by default after executing the tests using TestNG.

Below is the code to generate the excel report. And it should be under the same project where your tests are:

public class GenerateReport {

    public static void main(String[] args) throws Exception
    {
   	 Xl.generateReport("excel-report.xlsx");
    }
}

Below is the the sample excel report which is generated by the above code:

AT Excel Report