Exporting Sonar reported issues to excel

Recently, I was asked by one of my blog reader to share an approach for exporting issues reported by Sonar to excel. Though the same can be achieved using Sonar plugins having commercial licences & may be few by open source plugins, it is as well possible to fetch the same using the webservice API exposed by the Sonar platform.

More on this webservice java API & javadoc can be found here – http://docs.sonarqube.org/display/SONAR/Using+the+Web+Service+Java+client

I have written a simple java client which makes a webservice call to Sonar platform running in my local machine & then uses Apache POI (http://poi.apache.org/download.html) to generate the corresponding excel.

Here is the java code (Note – The below code snippet is not exhaustive because of the time constraint. Currently, it just try to fetch all the Critical, Major & Minor issues of the projects analysed by Sonar. I’ll try to enhance the same in future!) –

package com.sanjit;

import java.io.FileOutputStream;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.sonar.wsclient.SonarClient;
import org.sonar.wsclient.issue.Issue;
import org.sonar.wsclient.issue.IssueClient;
import org.sonar.wsclient.issue.IssueQuery;
import org.sonar.wsclient.issue.Issues;

public class Sample {

public static void main(String args[]) {

String login = "admin";
String password = "admin";

SonarClient client = SonarClient.create("http://localhost:9000");
client.builder().login(login);
client.builder().password(password);

IssueQuery query = IssueQuery.create();
query.severities("CRITICAL", "MAJOR", "MINOR");

IssueClient issueClient = client.issueClient();
Issues issues = issueClient.find(query);
List<Issue> issueList = issues.list();
createExcel(issueList);
}

private static void createExcel(List<Issue> issueList) {
// TODO Auto-generated method stub

try {
String filename = "D:/SonarIssues.xls";

HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("FirstSheet");

HSSFRow rowhead = sheet.createRow((short) 0);
rowhead.createCell(0).setCellValue("Project Key");
rowhead.createCell(1).setCellValue("Component");
rowhead.createCell(2).setCellValue("Line");
rowhead.createCell(3).setCellValue("Rule Key");
rowhead.createCell(4).setCellValue("Severity");
rowhead.createCell(5).setCellValue("Message");

for (int i = 0; i < issueList.size(); i++) {
HSSFRow row = sheet.createRow((short) i+1);
row.createCell(0).setCellValue(issueList.get(i).projectKey());
row.createCell(1).setCellValue(issueList.get(i).componentKey());
row.createCell(2).setCellValue(
String.valueOf(issueList.get(i).line()));
row.createCell(3).setCellValue(issueList.get(i).ruleKey());
row.createCell(3).setCellValue(issueList.get(i).severity());
row.createCell(3).setCellValue(issueList.get(i).message());
}

FileOutputStream fileOut = new FileOutputStream(filename);
workbook.write(fileOut);
fileOut.close();
System.out.println("Your excel file has been generated!");

} catch (Exception ex) {
System.out.println(ex);

}
}
}

You will need to put the below jars in your classpath –

http://central.maven.org/maven2/org/codehaus/sonar/sonar-ws-client/4.3/sonar-ws-client-4.3.jar
http://www.apache.org/dyn/closer.cgi/poi/dev/bin/poi-bin-3.11-beta3-20141111.zip

Advertisements