Tuesday, July 31, 2012

Excel Bulk Entry of Jira using Apache HttpClient & POI

Where I work, I have to regularly enter my time in JIRA using their crappy portlet interface. Because of French regulations and bad design, one can enter time for at most 1 day at a time. This is very annoying especially to enter vacation days. I decided to spend some time (took me around 2 hours - I thought it would be much more) to enter the time from a local Excel spreadsheet (via with OpenOffice), and use Java to populate JIRA.

First I had to find out what where the relevant requests. Firefox has several extensions for that, but I found Tamper Data to be the easiest to work with (hint: use copy/paste in the Tamper Data window to get the full request in a nice format).

Apache HttpClient provides an easy way to do HTTP requests and handles cookies almost automatically in Java. Here is the login phase:

List<NameValuePair> formparams = new ArrayList<NameValuePair>();
formparams.add(new BasicNameValuePair("os_username", "mouse@thecat"));
formparams.add(new BasicNameValuePair("os_password", "DEADDEAD"));
UrlEncodedFormEntity entity = new UrlEncodedFormEntity(formparams, "UTF-8");
HttpPost httppost = new HttpPost("https://jira.calypso.com/rest/gadget/1.0/login");
DefaultHttpClient httpclient = new DefaultHttpClient();
CookieStore cookieStore = new BasicCookieStore();
ResponseHandler<byte[]> handler = new ResponseHandler[]>() {
public byte[] handleResponse(HttpResponse response)
throws ClientProtocolException, IOException {
System.out.println("<-" + response.getStatusLine());
HttpEntity entity = response.getEntity();
if (entity != null) {
return EntityUtils.toByteArray(entity);
} else {
return null;
System.out.println("->" + httppost.getURI());
byte[] response = httpclient.execute(httppost, handler);

Then a request to our JIRA portlet looks like:

formparams = new ArrayList<NameValuePair>();
formparams.add(new BasicNameValuePair("inline", "true"));
formparams.add(new BasicNameValuePair("decorator", "dialog"));
formparams.add(new BasicNameValuePair("startDate", startDate));
formparams.add(new BasicNameValuePair("timeLogged", timeLogged));
formparams.add(new BasicNameValuePair("id", id));
formparams.add(new BasicNameValuePair("adjustEstimate", "auto"));
entity = new UrlEncodedFormEntity(formparams, "UTF-8");
httppost = new HttpPost("https://jira.calypso.com/secure/CreateWorklog.jspa");
httppost.addHeader("Referer", "https://jira.calypso.com/browse/"+ jiraCAL);
System.out.println("->" + httppost.getURI());
response = httpclient.execute(httppost, handler);

Parsing Excel with Apache POI is a bit annoying, but I kept fixed conventions to make things simple:

InputStream inp = new FileInputStream(file);
HSSFWorkbook wb = new HSSFWorkbook(new POIFSFileSystem(inp));
List list = new ArrayList();
HSSFSheet sheet = wb.getSheetAt(0);
boolean isEmpty = false;
int i = 0;
while (!isEmpty) {
HSSFRow row = sheet.getRow(i);
if (row == null) { isEmpty=true; break;}

HSSFCell dateCell = row.getCell(0);
HSSFCell calCell = row.getCell(1);
HSSFCell idCell = row.getCell(2);
HSSFCell percentCell = row.getCell(3);
if (dateCell == null) {
isEmpty = true;
} else if (dateCell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC && calCell != null){
TimeLine timeLine = new TimeLine();
timeLine.date = HSSFDateUtil.getJavaDate(dateCell.getNumericCellValue());
if (timeLine.date.after(startDate)
&& timeLine.date.before(endDate)) {
timeLine.jiraCAL = calCell.getStringCellValue();
if (timeLine.jiraCAL != null && timeLine.jiraCAL.length() > 0) {
timeLine.id = Integer.toString((int)idCell.getNumericCellValue());
timeLine.percent = Integer.toString((int)percentCell.getNumericCellValue());

Obviously, this is not clean code, the goal was only to do something quick and dirty to solve my immediate problem.

No comments :

Post a Comment