Excel Bulk Entry of Jira using Apache HttpClient & POI

Tue, Jul 31, 2012 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");
httppost.setEntity(entity);
DefaultHttpClient httpclient = new DefaultHttpClient();
CookieStore cookieStore = new BasicCookieStore();
httpclient.setCookieStore(cookieStore);
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);
httppost.setEntity(entity);
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());
				list.add(timeLine);
			}
		}
	}
	i++;
}

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