Header Ads

How To Convert JUnit Report Produced by ANT to Excel

ANT-Excel Reporting is an important area where automation needs to fine-tune.
Every test should have some objectives. That is finite and achievable. So reports coming straight from automation tool should tell clearly the result of the testcase. There could be two options pass or failed. If failed if it gives the reason and if can pinpoint if there is already any bug on that, that would be awesome. Otherwise result may mislead the testing.Result should also provide screenshots for passed and failed, testdata, environment details , step details and other information to nail the issue.
Now analysis of these failures are tedious and boring job, takes several time of a test engineers.With meaningful failure message, it is possible to automate the result analysis part. the below section talks about the automation of such report coming straight from JUnit.Many a times, we need to convert JUnit report to Excel. Some need this for his own reporting purpose. Functional QA needs it for analysis and documentation. Automation test engineers need this converted data for
further script/test fix.
I tried to google the same.
I found this-Convert an XML file into an Excel spreadsheet 
with this technique. Here the technique is little complex.
I found in google many test engineers need this for their analysis.
 And most of them around 16 links in google to find out the failed
and Error testcases.

I am creating a simple project to extract all errors and failure testcases in excel.

To run this what you need:
Java 1.7
JSoup latest
Eclipse
POI
Before we really start please go through the below links for further understanding:
  1. JSoup A Nice Initiative To Fetch Information From HTML
  2. How To Read Data From HTML Via JSoup In Java
The algorithm is as follows:
  1. Take JUnit report from User using swing input
  2. Read all tables and filter out errors and failures into two different lists using JSoup
  3. Create Excel and sheet using POI
  4. Put all data into the sheet using POI and java
Implementation:
So the first step take eclipse or any java editor. Install or extract into a folder. Create a workspace for this. Now create a project. The structure of the project will look like--
Project Structure

























Name of the project-Result Analyser.
Now while coding our first objective to create
 a user interface. From this interface user will
 will provide JUnit report to the system.

input
Code for user Input:
package main.java;
import javax.swing.JOptionPane;
public class Client {
public static void main(String[] args) throws Exception { 
 
        ReadHTML readHtml=new ReadHTML(); 
        readHtml.getValues(JOptionPane.showInputDialog("Please input mark for test 1: "),"Test"); 
        CreateExcelFile cef=new CreateExcelFile(); 
        cef.getExcelFileName();

    }

} 

Now I am creating an Object ReadHTML.So let us implement ReadHTML

package main.java;

import java.io.IOException;
import java.net.URL;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.jsoup.Jsoup;
import org.jsoup.nodes.Document;
import org.jsoup.nodes.Element;
import org.jsoup.select.Elements;


public class ReadHTML {
 public  void  getValues(String url,String SheetName) throws Exception
 {
  String overViewSummaryURL=url+"overview-summary.html";
  String allFailureDetailsURL=url+"alltests-fails.html";
  String allErrorDeatilsURL=url+"alltests-errors.html";
  System.out.println("Processing Overview Summary");
  URL getUrl=new URL(overViewSummaryURL);
  Document doc = Jsoup.parse(getUrl, 3000);
  Element table = doc.select("table[class=details]").first();
  Iterator<Element> iteh = table.select("th").iterator();
  //This is for fetching header values
  String test= iteh.next().text();
  String fail= iteh.next().text();
  String err= iteh.next().text();
  String knonIss=iteh.next().text();
  String pass= iteh.next().text(); 
  String skip= iteh.next().text();
  String suc_rate=iteh.next().text();
  String time=iteh.next().text();

  Iterator<Element> ite = table.select("td").iterator();

  //This is for fetching row values
  String testV=ite.next().text();
  String failV=ite.next().text();
  String errorV=ite.next().text();
  String knownIssueV=ite.next().text();
  String passV=ite.next().text();
  String skipV=ite.next().text();
  String sucv=ite.next().text().split(":")[1].split("%")[0].trim();
  String timeV=ite.next().text();

  System.out.println("Value of: "  +test+  " is " + testV );
  System.out.println("Value of: "  +fail+  " is " + failV);
  System.out.println("Value of: "  +err+  " is "  +errorV);
  System.out.println("Value of: "  +knonIss+  " is " +knownIssueV );
  System.out.println("Value of: "  +pass+  " is "  +passV);
  System.out.println("Value of: "  +skip+  " is "  +skipV );
  System.out.println("Value of: "  +suc_rate+  " is " + sucv);
  System.out.println("Value of: "  +time+  " is " +timeV );
  System.out.println("Processing All Failure Summary");
  getUrl=new URL(allFailureDetailsURL);

  Document docfailure = Jsoup.parse(getUrl, 3000);
  Elements tableFailure = docfailure.select("table[class=details]");

  int rowCount=tableFailure.select("tr[class=Failure]").size();
  

  System.out.println(rowCount+"row");

  System.out.println("Processing All Failure Summary result");

  List<List> recordToAdd = new ArrayList<List>();
  
  List<String> headerRow = new ArrayList<String>();
  
  headerRow.add("Class Name");
  headerRow.add("Script Name");
  headerRow.add("altId");
  headerRow.add("TestCase Name");
  headerRow.add("Status");
  headerRow.add("Log Information");
  headerRow.add("Class Path");
  headerRow.add("Reason for Failure");
  
  recordToAdd.add(headerRow);
  ArrayList<String>[] firstRow_rowfail_tds;
              if(Integer.parseInt(errorV)>0) {
            //List<String> firstRow_rowfail_tds = new ArrayList<String>();
            firstRow_rowfail_tds = (ArrayList<String>[]) new ArrayList[rowCount + Integer.parseInt(errorV)];
                    }
  else
                    {
            firstRow_rowfail_tds = (ArrayList<String>[]) new ArrayList[rowCount];
                    }
            int i=0;
          for (Element tablefail : docfailure.select("table[class=details]")) {
           for (Element rowfail : tablefail.select("tr[class=Failure]")) {
    Elements tds = rowfail.select("td");
                
    System.out.println("Class Name - " + tds.get(0).text());  
    System.out.println("Script Name - " + tds.get(1).text());
    System.out.println("altId - " + tds.get(2).text());
    System.out.println("TestCase Name - " + tds.get(3).text());
    System.out.println("Status- " + tds.get(4).text());
    System.out.println("URL-->"+tds.select("a").attr("href"));
    System.out.println("");  
   firstRow_rowfail_tds[i] = new ArrayList<String>();
     
     firstRow_rowfail_tds[i].add((String) tds.get(0).text());
     firstRow_rowfail_tds[i].add((String) tds.get(1).text());
     firstRow_rowfail_tds[i].add((String) tds.get(2).text());
     firstRow_rowfail_tds[i].add((String) tds.get(3).text());
     firstRow_rowfail_tds[i].add((String) tds.get(4).text());
     firstRow_rowfail_tds[i].add((String) tds.get(5).text());
     firstRow_rowfail_tds[i].add((String) getClassInformation(tds.select("a").attr("href"),(String) tds.get(0).text()));
                    recordToAdd.add(firstRow_rowfail_tds[i]);
     System.out.println(recordToAdd);
     i=i+1;
     System.out.println(i+"val of i");
     if (i==tablefail.select("tr[class=Failure]").size())
      break;
   }
  }
        System.out.print("errorV"+errorV);
  if(Integer.parseInt(errorV)>0)
  {
     getUrl=new URL(allErrorDeatilsURL);
  Document docError = Jsoup.parse(getUrl, 3000);
  Elements tableError = docError.select("table[class=details]");
  int rowCountError=tableError.select("tr[class=Error]").size();
     System.out.println(rowCountError+"row");
  System.out.println("Processing All Error Summary result");
  int j=0;
  for (Element tablefailError : docError.select("table[class=details]")) {
   for (Element rowfailError : tablefailError.select("tr[class=Error]")) {
    Elements tds = rowfailError.select("td");
                
    System.out.println("Class Name - " + tds.get(0).text());  
    System.out.println("Script Name - " + tds.get(1).text());
    System.out.println("altId - " + tds.get(2).text());
    System.out.println("TestCase Name - " + tds.get(3).text());
    System.out.println("Status- " + tds.get(4).text());
    System.out.println("");  
     firstRow_rowfail_tds[i+j] = new ArrayList<String>();
     firstRow_rowfail_tds[i+j].add((String) tds.get(0).text());
     firstRow_rowfail_tds[i+j].add((String) tds.get(1).text());
     firstRow_rowfail_tds[i+j].add((String) tds.get(2).text());
     firstRow_rowfail_tds[i+j].add((String) tds.get(3).text());
     firstRow_rowfail_tds[i+j].add((String) tds.get(4).text());
     firstRow_rowfail_tds[i+j].add((String) tds.get(5).text());

     firstRow_rowfail_tds[i].add((String) getClassInformation(tds.select("a").attr("href"),(String) tds.get(0).text()));
     recordToAdd.add(firstRow_rowfail_tds[i+j]);
     System.out.println(recordToAdd);
     j=j+1;
     System.out.println(j+"val of j");
     if (j==tableError.select("tr[class=Error]").size())
      break;
   }

        }
  }
  System.out.print("recordToAdd"+recordToAdd);
  CreateExcelFile cls = new CreateExcelFile(recordToAdd,SheetName);


 }

 private String getClassInformation(String attr, String text) {
        attr.toString();
  // TODO Auto-generated method stub
  return null;
 }

 
 
}


Now I am creating another object class:CreateExcelFile CreateExcelFile cef=new CreateExcelFile(); So let us implement CreateExcelFile class:

package main.java;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Collection;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;

/*
 * Here we will learn how to create Excel file and header for the same.
 */
public class CreateExcelFile {

  int rownum = 0;
  HSSFSheet firstSheet,SecondSheet ;
 Collection<File> files;
  HSSFWorkbook workbook;
 File exactFile;
 {
  workbook = new HSSFWorkbook();
  firstSheet = workbook.createSheet("FIRST SHEET");


  Row headerRow = firstSheet.createRow(rownum);
  headerRow.setHeightInPoints(40);
 }
 public CreateExcelFile(){
 }
 CreateExcelFile(List<List> l1,String FileName) throws Exception {
  FileOutputStream fos = null;
  try {
   workbook = new HSSFWorkbook();
   firstSheet = workbook.createSheet("FIRST SHEET");

   }
        catch (Exception e)
        {
   e.printStackTrace();
  }
  
  try {
                for (int j = 0; j < l1.size(); j++) {
             Row row = firstSheet.createRow(j);
    List<String> l2= l1.get(j);
    for(int k=0; k<l2.size(); k++)
    {
     Cell cell = row.createCell(k);
     cell.setCellValue(l2.get(k));
    }
    rownum++;
   }

  } catch (Exception e) {
   e.printStackTrace();
  } finally {

  }

  fos=new FileOutputStream(new File(getPath(FileName)));
        System.out.print(getPath(FileName));
  HSSFCellStyle hsfstyle=workbook.createCellStyle();
  hsfstyle.setBorderBottom((short) 1);
  hsfstyle.setFillBackgroundColor((short)245);
  workbook.write(fos);
        fos.close();
        
        
 }
    
public String getTimeStamp()

{
return new SimpleDateFormat("yyyyMMdd").format(Calendar.getInstance().getTime()); 
}
public String getPath(String FileName) throws IOException
{
 String path=new File(".").getCanonicalPath() + File.separator;
 //path=path+getTimeStamp()+"analysis"+".xls";
    path=path+"analysis_"+FileName+".xls";
 return path;
}

public String getExcelFileName()
{String path="";
    try {
         path=new File(".").getCanonicalPath() + File.separator;
    } catch (IOException e) {
        e.printStackTrace();
    }
    int totalFileCount = 0;
     int fileCount = 0;
     ArrayList<String> aList = new ArrayList<String>();
    File folder = new File(path);
    File[] listOfFilesInDirectory = folder.listFiles();
    try {
        for (int numberOfFilesInFolder = 0; numberOfFilesInFolder < listOfFilesInDirectory.length; numberOfFilesInFolder++) {

            if (listOfFilesInDirectory[numberOfFilesInFolder].isFile()) {
                totalFileCount++;
                String files = listOfFilesInDirectory[numberOfFilesInFolder]
                        .getName();
                if (files.endsWith(".xls") || files.endsWith(".XLS")
                        || files.endsWith(".xlsx")
                        || files.endsWith(".XLSX")) {
                    aList.add(files);
                    fileCount++;
                }
            }
        }
    } catch (Exception e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
    System.out.println("Total file count = " + totalFileCount);
    System.out.println("Required file count = " + fileCount);

    return "";
}
}

That's it. Now run your client to fetch the JUnit report. After Processing it will create the excel with all failures,Errors and known issues with the failure message in excel.

The output will be:

The output excel will look like-
With this report a automatic excel will be generated to give proper guidance  to automation test engineer to analyze more in details.
As we know advertisement plays a great role or huge role in terms of successful products. It creates the awareness among the consumer of the products. Sharing the extract from test automation result with management and other stakeholders play a significant role for advertisement of automation. It is having multi folded benefits..
1. All stakeholders become serious while developing or making changes in application. Slowly they tend to think about testing team which intern gives birth of test driven development.
2.All shareholders become aware about the effort automation test engineers putting into automating the application. This gives a solid management backing.
3. Bugs caught in automation gets visible in very early stage in very quick time. Also automation logs shows the different areas, it touched. Functional QA and dev team will have a clear idea where the application is working fine and where it is broken.
Powered by Blogger.