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.
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’.
excellent work man
LikeLike
Loved it.
LikeLike
Can you share the jar file code ?
LikeLike
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.
LikeLike
It all depends on you and your client.Its always good to learn new things ;)
LikeLike
Good work dollar!
LikeLiked by 1 person
TY :)
LikeLike
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)
LikeLike
Your reading testng xml file ? your using parser ? thanks for sharing the idea.
LikeLike
Bhai please post related to testNG. i am new to selenium.your posts rocks.
LikeLike
Sure ! I’d like to help.
LikeLike
Thanks for sharing
LikeLiked by 1 person
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
LikeLike
No need to give the entire path Uday.Just mention your filename.xls
It will be created in your ExcelResults Package
LikeLike
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
LikeLike
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.
LikeLike
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!
LikeLike
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.
LikeLiked by 1 person
Thanks a lot.This is really helpful.
LikeLiked by 1 person
ThankYou
LikeLike
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…
LikeLike
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.
LikeLiked by 1 person
Working now, but how can i save the file to some other location say “F:\Project\GenerateExcelReportNewProject” some other folder…
LikeLike
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 !
LikeLiked by 1 person
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…
LikeLike
Hi Armatearjun, Latest version of ExcelReportGeneration Jar has been released which you’re looking for.Please take a look at same article.
Thankyou
LikeLike
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
LikeLike
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.
LikeLike
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
LikeLike
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…!
LikeLike
LikeLike
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
LikeLike
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…!
LikeLike
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
LikeLike
When ever You’d like to Execute your test scripts then run that class and Run ExcelGenerate class for excel report…!
LikeLike
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
LikeLike
I couldnt able to see your xml code.
LikeLike
Wow This working like a charm
LikeLike
Will this work for JUNIT ? Please fix the issue. I am unable to generate report using JUNIT
LikeLike
Sorry It will not work for Junit.
LikeLike
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??
LikeLike
Are you facing any exception ?
Make sure that you executed testNG Xml file.
LikeLike
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.
LikeLike
Could you please share the code ?
Did you give the extension at the end ?
LikeLike
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
LikeLike
Hi, Please check the period “.” and the colon “;” It’s a syntax error not a code issue
LikeLike
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
LikeLike
Yea, you’re right. What kind of customization you would like to do?
LikeLike
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
LikeLike
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
LikeLike
Can we have separate results in same excel report file for test executed parallel on different environments?
LikeLike
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
LikeLike
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
LikeLike
Exception in thread “main” java.lang.NoClassDefFoundError: org/apache/poi/ss/usermodel/CellStyle
LikeLike
Hi, Could you please share the code
LikeLike
Hi Charan,
Excel sheet is not getting created for me. Can you help me?
LikeLike
What’s the version you’re using? Are you using TestNG?
LikeLike
How to add a browser column next to executed test cases. Will be of much help for cross browser testing
LikeLike
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
LikeLike
Have you added all required Jars for the project? Could you try the same by adding Saxon jar of the package net.sf.saxon
LikeLike
saxon-8.7.jar is available but still facing issue. Kindly help
LikeLike
I have added latest saxon-9.1.0.8.jar just now and it is working fine now.. Thank you :)
LikeLike
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.
LikeLike
Once again i executed and now it is working and also generating proper report. Thank You :)
LikeLike
Good to hear!
LikeLike
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)
LikeLike
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
LikeLike
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.
LikeLike
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.
LikeLike
Hi ,
I am also getting the same issue, I have created the project in maven.
LikeLike
Ok, I will take a look at there. Thanks!
LikeLike