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

8 thoughts on “Exporting Sonar reported issues to excel

    • Hi,
      You can use the “GET api/issues/search” which is available since sonar web service API version 3.6.

      This API takes an optional parameter called “componentKeys” to retrieve issues associated to a specific list of components sub-components (comma-separated list of component keys). A component can be a view, developer, project, module, directory or file.

      Below is an example URL –

      http://localhost:9000/api/issues/search?componentKeys=sanjit:JavaProject

      where “sanjit:JavaProject” is my sonar project key.

      The above URL will fetch all the issues for the given project and return as JSON.

      Note:- There exist a limitation with this API. If the number of issues is greater than 10,000, only the first 10,000 ones are returned by the web service.

      _Sanjit

      Like

  1. Hi sanjit,
    Your post helped me create an excel that can be used for our groups requirement with assignment of issues to teams. Thanks.
    Is there a way to export all the configured rules with their id, title & description into an excel? I want to be able to send this list to a few people who can filter out the really relevant ones for us.
    I tried using ProfileQuery & Profile.Rule but it doesn’t provide title & descr.
    Regards,
    Deepak

    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