Generate Customized ExcelReports Using TestNG in Selenium


There are many ways to Generate Reports in Selenium. Now I’d like to generate an excel reports. This report will give you the number test cases has been Passed and Failed or Skipped. It’s quite simple to generate this report. All you need to do is just giving a Proper Name and Destination Path (if you’re using the latest jar 4.0.1).

Note: TestNG is required. Without testNG this code won’t work.
There are few steps to be followed to generate Excel Reports. Here I am sharing the snippet.

Lets-start-something-NEW-280x132

Click here to get 0.0.1 jar (older version).

Download the latest Jar from here ExcelReportGenerator version 4.0.1. Add this Jar into your project BuildPath.

If You’re using Jar 4.0.1 version then you can specify the desired location and File name in the code itself.

Follow the Steps Specified :

Step1: Create a Package ‘ExcelResults’ under your Project (Optional, if you’re using 4.0.1 version).

step2: Create  the test cases which you’d like to automate using TestNg (by using @Test, BeforeTest…….) as Shown.

import org.openqa.selenium.WebDriver;
import org.testng.annotations.Test;

public class Test_98 {
@Test(priority = 1)
public void VerfyingTestCaseID_001() {
System.out.println(“test”);
System.out.println(“this”);
}

@Test(priority = 2)
public void VerfyingTestCaseID_002() {
System.out.println(“test”);
System.out.println(“this”);
}

@Test(priority = 3)
public void VerfyingTestCaseID_003() {
System.out.println(“test”);
System.out.println(“this”);
}

@Test(priority = 4)
public void VerfyingTestCaseID_004() {
System.out.println(“test”);
System.out.println(“this”);
}

@Test(priority = 5)
public void VerfyingTestCaseID_005() {
System.out.println(“test”);
System.out.println(“this”);
Assert.assertEquals(“validText”, “InvalidText”);
}

@Test(priority = 6)
public void VerfyingTestCaseID_006() {
System.out.println(“test”);
System.out.println(“this”);
}

@Test(priority = 7)
public void VerfyingTestCaseID_007() {
System.out.println(“test”);
System.out.println(“this”);
Assert.assertEquals(“validText”, “InvalidText”);

}

@Test(priority = 8)
public void VerfyingTestCaseID_008() {
System.out.println(“test”);
System.out.println(“this”);
}

@Test(priority = 9)
public void VerfyingTestCaseID_009() {
System.out.println(“test”);
System.out.println(“this”);
}

@Test(priority = 10)
public void VerfyingTestCaseID_010() {
System.out.println(“test”);
System.out.println(“this”);
}

@Test(priority = 11)
public void VerfyingTestCaseID_011() {
System.out.println(“test”);
System.out.println(“this”);
Assert.assertTrue(false);
}

@Test(priority = 12)
public void VerfyingTestCaseID_012() {
System.out.println(“test”);
System.out.println(“this”);
Assert.assertTrue(false);

}

@Test(priority = 13)
public void VerfyingTestCaseID_013() {
System.out.println(“test”);
System.out.println(“this”);
}

@Test(priority = 14)
public void VerfyingTestCaseID_014() {
System.out.println(“test”);
System.out.println(“this”);
}
}

Step3 : Create a testng.xml file under your Project as Shown.

<suite name=”Build 2.0.1″>

<test name=”TestReport”>
<classes>
<class name=”Test_98″ />
</classes>
</test>
</suite>

Now Run the testng.xml file.

Step 4 : Now Create a Class ‘ExcelGenerate’  and paste the following code.

import java.io.IOException;

import javax.xml.parsers.ParserConfigurationException;

import org.xml.sax.SAXException;
public class ExcelGenerate {

public static void main(String[]args) throws ParserConfigurationException, IOException, SAXException
{
ExcelReportGenerator.generateExcelReport(“MyProject.xls”, “D:\\workspace”);

}
}

Step5: Refresh the package ‘ExcelResults’ (If you’re using older version).

Step5 : Results will be generated at your desired path given. (If you’re using 4.0.1 version).

Click here to see the generated report.

Now you see the excelReport Generated. Its so simple huh !!!

*If you see the columns in the excel were disturbed please change the settings or Use the latest Version. I have made this using  ‘Libre’.

Advertisements

70 thoughts on “Generate Customized ExcelReports Using TestNG in Selenium

  1. Anonymous June 8, 2015 / 9:46 pm

    excellent work man

    Like

  2. Anonymous June 8, 2015 / 9:47 pm

    Loved it.

    Like

  3. Anonymous June 8, 2015 / 10:35 pm

    Can you share the jar file code ?

    Like

  4. Anonymous June 8, 2015 / 10:56 pm

    This is amazing really.But my question is why do they need excel report.There are many third party tools for report generation like reportNG testNG etc….anyway thanks for sharing.I really liked this work.

    Like

    • charan163 June 8, 2015 / 11:10 pm

      It all depends on you and your client.Its always good to learn new things ;)

      Like

      • Anonymous June 9, 2015 / 2:38 pm

        Good work dollar!

        Liked by 1 person

      • charan163 June 9, 2015 / 2:40 pm

        TY :)

        Like

    • Anonymous November 17, 2015 / 3:29 pm

      package example;

      import java.io.IOException;

      import javax.xml.parsers.ParserConfigurationException;

      import org.xml.sax.SAXException;

      public class ExcelGenerate {
      public static void main(String[]args) throws ParserConfigurationException, IOException, SAXException
      {
      ExcelReportGenerator.generateExcelReport(“MyProject.xls”, “D:\\workspace”);

      }

      }

      For the above program the error file was like this

      Exception in thread “main” java.lang.Error: Unresolved compilation problems:
      ExcelReportGenerator cannot be resolved
      Syntax error on token “Invalid Character”, delete this token
      MyProject cannot be resolved to a variable
      Syntax error on token “.”, delete this token
      Syntax error, insert “)” to complete MethodInvocation
      Syntax error, insert “;” to complete BlockStatements

      at example.ExcelGenerate.main(ExcelGenerate.java:12)

      Like

  5. Anonymous June 8, 2015 / 11:26 pm

    Your reading testng xml file ? your using parser ? thanks for sharing the idea.

    Like

  6. Anonymous June 8, 2015 / 11:30 pm

    Bhai please post related to testNG. i am new to selenium.your posts rocks.

    Like

    • charan163 June 8, 2015 / 11:34 pm

      Sure ! I’d like to help.

      Like

  7. uday June 9, 2015 / 11:08 pm

    Hello All,

    I have followed above mentioned all the steps but I am not sure the .xls file location.
    I didn’t find any xls file after refresh package name.

    Who has an idea how to do this, and can give me solution.

    Thanks in advance!

    Thanks & regards,
    Uday Reddy.S

    Like

    • charan163 June 10, 2015 / 9:23 am

      No need to give the entire path Uday.Just mention your filename.xls
      It will be created in your ExcelResults Package

      Like

      • uday June 10, 2015 / 10:25 am

        HI Charan..Thanks for your reply..

        Everything is working fine but Where Can i See the Generated excelReport file in folders.

        Thanks & regards,
        Uday Reddy.S

        Like

      • charan163 June 10, 2015 / 12:13 pm

        I have mentioned in the above steps, You have to create a Package ‘ExcelResults’ under your Project src.

        once you run this ExcelGenerate class,automatically ExcelReport will be generated in that mentioned Package.

        Like

      • uday June 10, 2015 / 3:38 pm

        Hi Charan,

        I have followed the above steps what you mentioned & I have clear Idea up to 3rd.
        in 4th I have created class and entered name “xyz.xls”

        please find below steps.

        1) I have downloaded ExcelReportGenerator & added to Project build path “External Jars”
        2) Created package
        3) Created Class in package
        4) created .xml file & executed through TestNG test suite.
        5) created test class, pasted above mentioned code.
        6) refreshed package
        7) Here my question is from where can i see the .xls report

        Please do not hesitate to let me know, should you need any clarification in this regard.

        Thanks in advance!

        Like

      • charan163 June 10, 2015 / 4:26 pm

        Please Name the Package as ‘ExcelResults’
        If you create package with different name,this wont work.Because i have made a program in that way. Hope this solves your issue.

        Liked by 1 person

  8. Rahul June 10, 2015 / 9:25 am

    Thanks a lot.This is really helpful.

    Liked by 1 person

  9. Anonymous June 10, 2015 / 8:27 pm

    ThankYou

    Like

  10. armatearjun June 15, 2015 / 11:19 am

    Not working for me,i have given package name as “excelResults” starts with small e, and i have kept both ExcelGenerate.java and test_98.java in same package as said above and if i run ExcelGenerate.java gives error as below… as we investigate the exception it says testng-results.xml path is missing but i have already the test_98.java so my results.xml is in place, not sure what may be the problem is?….

    Exception in thread “main” java.io.FileNotFoundException: C:\Documents%20and%20Settings\A-5874\workspace\pageLoadAndPerformanceChecks\src\..\test-output\testng-results.xml (The system cannot find the path specified)
    at java.io.FileInputStream.open(Native Method)
    at java.io.FileInputStream.(Unknown Source)
    at java.io.FileInputStream.(Unknown Source)
    at sun.net.www.protocol.file.FileURLConnection.connect(Unknown Source)
    at sun.net.www.protocol.file.FileURLConnection.getInputStream(Unknown Source)
    at org.apache.xerces.impl.XMLEntityManager.setupCurrentEntity(Unknown Source)
    at org.apache.xerces.impl.XMLVersionDetector.determineDocVersion(Unknown Source)
    at org.apache.xerces.parsers.XML11Configuration.parse(Unknown Source)
    at org.apache.xerces.parsers.XML11Configuration.parse(Unknown Source)
    at org.apache.xerces.parsers.XMLParser.parse(Unknown Source)
    at org.apache.xerces.parsers.DOMParser.parse(Unknown Source)
    at org.apache.xerces.jaxp.DocumentBuilderImpl.parse(Unknown Source)
    at javax.xml.parsers.DocumentBuilder.parse(Unknown Source)
    at com.raybiztech.commonexcelreport.ExcelReportGenerator.GenerateExcelReport(ExcelReportGenerator.java:72)
    at excelResults.ExcelGenerate.main(ExcelGenerate.java:13)

    Please check and revert me back, help will be appreciated…

    Like

    • charan163 June 15, 2015 / 11:24 am

      Please Name it as “ExcelResults’ and make sure that you’re using TestNG.The exception saying that there’s no main method.Please Run it in Main method.

      Liked by 1 person

  11. armatearjun June 15, 2015 / 11:52 am

    Working now, but how can i save the file to some other location say “F:\Project\GenerateExcelReportNewProject” some other folder…

    Like

    • charan163 June 15, 2015 / 11:56 am

      Im sorry You cant save it in other location. I didnt write the code for that.It will be generated in the Package itself.

      ThankYou !

      Liked by 1 person

  12. armatearjun June 15, 2015 / 12:28 pm

    Thank you so much for all the help and if you can do that like give the path of file to be saved to some other location will be very helpful as in package – src/test nobody wants to put the excel report, if not please try put the source files on github so that we can also contribute to do the same… But anyway awesome work… thanks for this bro…

    Like

    • charan163 September 30, 2015 / 7:39 pm

      Hi Armatearjun, Latest version of ExcelReportGeneration Jar has been released which you’re looking for.Please take a look at same article.
      Thankyou

      Like

  13. Praveen Ambi July 2, 2015 / 5:28 pm

    Charan

    Its not working for me i followed all the steps what u mentioned and did all the process ,my xml file is running but when i refresh the package , i m not able to see the Excel file. But only once i got 3 Excel files after refreshing the package .

    Here is my xml code

    Please help me …

    Thanks in Advance

    Regards
    Praveen A

    Like

    • charan163 July 2, 2015 / 8:53 pm

      I didnt get your question, Did you get any Excel report ? Excel Report will generated once you refresh the package.It wont generate while running the Xml.

      Like

      • Praveen Ambi July 3, 2015 / 11:46 am

        Charan ,

        i m getting excel 3files when i run my xml file 7 times, for each run of xml file i m not getting excel files, this is the issue
        kindly help

        Regards
        Praveen A

        Like

      • charan163 July 3, 2015 / 12:14 pm

        Could you Please delete all the files in that package and run the Xml only one time.It should generate only one Excel Report.Please lemme know once its done.We never faced this issue,logically its not possible.

        ThankYou…!

        Like

  14. Praveen Ambi July 2, 2015 / 5:31 pm

    Like

  15. uday July 3, 2015 / 12:59 pm

    Hi Charan,

    I have one doubt here!

    1) I Created & Run testng.xml file, next created a Class ‘ExcelGenerate’ but not using this class to Run then what is the benefit with class?

    2) What I have to mention in “YourTestName.xls” (Random name or I have to mention any specific)

    ExcelReportGenerator exe= new ExcelReportGenerator();
    exe.GenerateExcelReport(“YourTestName.xls”);

    Thanks in Advance!

    Uday Reddy.S

    Like

    • charan163 July 3, 2015 / 6:01 pm

      for the 1) if you’re not using the class ‘ExcelGenerate’ then you cant generate excel report.

      2) You can mention any name. Maybe your project name or any other name…!

      Like

      • uday July 5, 2015 / 9:45 pm

        HI Charan,

        Thanks for your reply..

        In above steps you didn’t mention when we have to execute Test Class..

        Can You please let me know..

        Thannks & Regards,
        Uday Reddy.S

        Like

      • charan163 July 6, 2015 / 8:18 pm

        When ever You’d like to Execute your test scripts then run that class and Run ExcelGenerate class for excel report…!

        Like

  16. Praveen Ambi July 3, 2015 / 6:58 pm

    Charan,

    I created a new Project and added testng and followed your steps , Still i m not getting excel file when i run my xml file and Refresh the Package .

    Here is my xml code

    Thanks In Advance

    Like

    • charan163 July 3, 2015 / 7:01 pm

      I couldnt able to see your xml code.

      Like

  17. Robert July 26, 2015 / 1:45 pm

    Wow This working like a charm

    Like

  18. Ananth kumar jadhav August 8, 2015 / 7:47 pm

    Will this work for JUNIT ? Please fix the issue. I am unable to generate report using JUNIT

    Like

  19. Arthi October 23, 2015 / 4:13 pm

    Charan,
    I followed your steps but still could not get excel report.
    I have refreshed the package and also Run excel class.
    Anything else I would require to do??

    Like

    • charan163 October 23, 2015 / 7:59 pm

      Are you facing any exception ?
      Make sure that you executed testNG Xml file.

      Like

      • Anonymous October 27, 2015 / 12:00 pm

        Ya I have run the testing XML only and I didn’t get any exception. I have got result in console.
        I have created the path d:\workspace but not created the excel file prior to refreshing to excel class.
        But still excel report is not created, am I missing something? Plz help me resolving this.

        Like

      • charan163 October 27, 2015 / 8:33 pm

        Could you please share the code ?
        Did you give the extension at the end ?

        Like

  20. balaram November 17, 2015 / 3:32 pm

    Hi charan,
    package example;

    import java.io.IOException;

    import javax.xml.parsers.ParserConfigurationException;

    import org.xml.sax.SAXException;

    public class ExcelGenerate {
    public static void main(String[]args) throws ParserConfigurationException, IOException, SAXException
    {
    ExcelReportGenerator.generateExcelReport(“MyProject.xls”, “D:\\workspace”);

    }

    }

    ERROR FILE:
    Exception in thread “main” java.lang.Error: Unresolved compilation problems:
    ExcelReportGenerator cannot be resolved
    Syntax error on token “Invalid Character”, delete this token
    MyProject cannot be resolved to a variable
    Syntax error on token “.”, delete this token
    Syntax error, insert “)” to complete MethodInvocation
    Syntax error, insert “;” to complete BlockStatements

    at example.ExcelGenerate.main(ExcelGenerate.java:12)

    help me to solve this

    Like

    • charan163 November 18, 2015 / 3:58 pm

      Hi, Please check the period “.” and the colon “;” It’s a syntax error not a code issue

      Like

  21. Tanmay January 6, 2016 / 6:46 pm

    Hello,
    This is amazing :-) . I am exploring on same. As per my understanding we need to run both testng xml and ExcelGenerate class to generate Excel reports. Please Correct me if i’m wrong.

    Is there any configuration we can made for customizing excel reports further?

    Thanks

    Like

    • charan163 January 6, 2016 / 8:14 pm

      Yea, you’re right. What kind of customization you would like to do?

      Like

      • Tanmay January 7, 2016 / 10:34 am

        Customization like changing header row color , add new column etc. Currently I’m using 4.01 and the header text in report is not readable due to dark cell color.

        Also if I have multiple test classes/suites can we segregate result based on class/suite name in same excel file?

        Thanks

        Like

      • Tanmay January 7, 2016 / 1:49 pm

        Hey my report is generated but header row text like status passed/Failed , Executed TestCase etc. is not visible due to dark cell color.

        Also if i have different test classes/suites in my test ng xml then can we segregate results based on class/ suite name in same report excel file.

        Thanks

        Like

  22. Tanmay January 7, 2016 / 5:55 pm

    Can we have separate results in same excel report file for test executed parallel on different environments?

    Like

    • charan163 January 8, 2016 / 8:11 pm

      No, Results will be generated in one single Excel file.

      Header cell format is not visible?
      Are you using MS Excel or Libre ? Please specify the version.

      If you need the source code, please drop a mail mentioning your company name and details

      Regards
      Charan
      dollarstrike163@gmail.com

      Like

  23. kotes May 6, 2016 / 6:18 pm

    Hi Charan,
    I am getting error like below

    Exception in thread “main” java.lang.NoClassDefFoundError: org/apache/poi/ss/usermodel/CellStyle
    at ExcelResults.ExcelGenerate.main(ExcelGenerate.java:21)
    Caused by: java.lang.ClassNotFoundException: org.apache.poi.ss.usermodel.CellStyle
    at java.net.URLClassLoader.findClass(URLClassLoader.java:381)
    at java.lang.ClassLoader.loadClass(ClassLoader.java:424)
    at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:331)
    at java.lang.ClassLoader.loadClass(ClassLoader.java:357)
    … 1 more

    Like

  24. kotes May 6, 2016 / 6:28 pm

    Exception in thread “main” java.lang.NoClassDefFoundError: org/apache/poi/ss/usermodel/CellStyle

    Like

  25. Neha August 10, 2016 / 3:56 pm

    Hi Charan,
    Excel sheet is not getting created for me. Can you help me?

    Like

    • Hari Charan August 10, 2016 / 7:32 pm

      What’s the version you’re using? Are you using TestNG?

      Like

  26. Anonymous January 31, 2017 / 12:49 pm

    How to add a browser column next to executed test cases. Will be of much help for cross browser testing

    Like

  27. Vrushabh February 23, 2017 / 7:03 pm

    Facing below issue. Kindly help

    Error
    DOMSource cannot be processed: check that saxon8-dom.jar is on the classpath
    Error
    DOMSource cannot be processed: check that saxon8-dom.jar is on the classpath
    Error
    DOMSource cannot be processed: check that saxon8-dom.jar is on the classpath
    Exception in thread “main” org.apache.poi.openxml4j.exceptions.OpenXML4JRuntimeException: Fail to save: an error occurs while saving the package : The part /docProps/core.xml fail to be saved in the stream with marshaller org.apache.poi.openxml4j.opc.internal.marshallers.ZipPackagePropertiesMarshaller@f5ac9e4
    at org.apache.poi.openxml4j.opc.ZipPackage.saveImpl(ZipPackage.java:602)
    at org.apache.poi.openxml4j.opc.OPCPackage.save(OPCPackage.java:1557)
    at org.apache.poi.POIXMLDocument.write(POIXMLDocument.java:248)
    at com.common.excelreport.ExcelReportGenerator.generateExcelReport(ExcelReportGenerator.java:229)
    at ExcelResults.ExcelGenerate.main(ExcelGenerate.java:14)
    Caused by: org.apache.poi.openxml4j.exceptions.OpenXML4JException: The part /docProps/core.xml fail to be saved in the stream with marshaller org.apache.poi.openxml4j.opc.internal.marshallers.ZipPackagePropertiesMarshaller@f5ac9e4
    at org.apache.poi.openxml4j.opc.ZipPackage.saveImpl(ZipPackage.java:582)
    … 4 more

    Like

    • Hari Charan February 23, 2017 / 7:10 pm

      Have you added all required Jars for the project? Could you try the same by adding Saxon jar of the package net.sf.saxon

      Like

      • Vrushabh February 24, 2017 / 11:18 am

        saxon-8.7.jar is available but still facing issue. Kindly help

        Like

      • Vrushabh February 24, 2017 / 11:25 am

        I have added latest saxon-9.1.0.8.jar just now and it is working fine now.. Thank you :)

        Like

  28. Vrushabh February 24, 2017 / 12:03 pm

    I have executed 4 tests, in excel report summary it is showing proper result but separate report wise summary only showing for 3 reports and 4th report is not generated in excel sheet. Kindly help. PFB for testng-results.xml file.










    Like

  29. Vrushabh February 24, 2017 / 12:06 pm

    Once again i executed and now it is working and also generating proper report. Thank You :)

    Like

  30. Hari Borra March 2, 2017 / 10:44 pm

    Hi Charan,

    I’m getting the below exception when I run the ExcelGenerate class after running the testng.xml. But what I see in my case test-output folder generated outside the target folder. And unfortunately I could not change target location in your jar file. Could you please help here?

    Exception in thread “main” java.io.FileNotFoundException: C:\Hari\eclipse_Work_Directory\plan-selection-tool\target\test-classes\..\test-output\testng-results.xml (The system cannot find the path specified)
    at java.io.FileInputStream.open(Native Method)
    at java.io.FileInputStream.(FileInputStream.java:138)
    at java.io.FileInputStream.(FileInputStream.java:93)
    at sun.net.www.protocol.file.FileURLConnection.connect(FileURLConnection.java:90)
    at sun.net.www.protocol.file.FileURLConnection.getInputStream(FileURLConnection.java:188)
    at org.apache.xerces.impl.XMLEntityManager.setupCurrentEntity(Unknown Source)
    at org.apache.xerces.impl.XMLVersionDetector.determineDocVersion(Unknown Source)
    at org.apache.xerces.parsers.XML11Configuration.parse(Unknown Source)
    at org.apache.xerces.parsers.XML11Configuration.parse(Unknown Source)
    at org.apache.xerces.parsers.XMLParser.parse(Unknown Source)
    at org.apache.xerces.parsers.DOMParser.parse(Unknown Source)
    at org.apache.xerces.jaxp.DocumentBuilderImpl.parse(Unknown Source)
    at javax.xml.parsers.DocumentBuilder.parse(DocumentBuilder.java:205)
    at com.common.excelreport.ExcelReportGenerator.generateExcelReport(ExcelReportGenerator.java:72)
    at excelreports.ExcelGenerate.main(ExcelGenerate.java:14)

    Like

    • Hari Charan March 3, 2017 / 12:37 pm

      Which version of Jar you’re using? Please use latest version (provided downloadable link) where you can change location of report. You just need to pass location in the agument

      Like

      • Hari Borra March 3, 2017 / 8:42 pm

        Thanks for the quick response Charan. I am using the 4.0.1 version. Can you resend the downloadable link where I can change the location pls.

        Like

      • Hari Charan March 3, 2017 / 10:49 pm

        Sorry I mistook the point, you cannot change the location of test output folder using the jar, in fact you need to change it in your pom.xml. Due to few reasons I cannot make this jar as an open source.

        Like

  31. Hari Borra March 3, 2017 / 11:01 pm

    Ok, I will take a look at there. Thanks!

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s