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");
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.

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");
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.

Keyboard Porn

I have been browsing the web, looking for a nice computer keyboard. Programming is a big part of my job, a comfortable keyboard is therefore important to my daily life.

I have some nostalgia for the original Microsoft natural keyboard, the white one with standard home and end keys. When it came out it looked revolutionary. I remember really improving my typing speed on it. The only minor annoyance was the heavy and loud space bar. It's sad that MS does not make those in azerty anymore (I know qwerty is better for programming - I used it while I was working in the US, but it's just too annoying to have a different layout from 95% of the people around). The new MS ergonomic model just looks ugly and scary with all those extra keys.

At home I have a Logitech K800, very practical as I often use it from the couch and at nighttime. When I tried it, I was impressed with the key feel: the action is a bit longer than a laptop keyboard (especially the Apple ones), and smooth. But one day, my son, by keeping on hammering on it with all the force of a 2 years old, damaged it. Some keys would print many times other letters. Still after unmounting some keys, and after washing the keyboard several times and waiting  a couple of weeks it started working again. But now the keys feel very mushy and not very nice to type on. This is obviously due to the strong abuse, and the fact that the back is flexible plastic probably did not help. And I wonder if a new K800 (or a Logitech Illuminated) would not just age the same way.

I looked and looked, read the forums. The hip keyboards seem to be the mechanical ones. It tempted me to get a WASD keyboard even though I never liked the feel and sound of my uncle's IBM model M keyboard.  WASD offer the combination of red cherry switches and o rings which should be significantly better for me.

Somehow despite all this research for a better keyboard, I kind of like my current work keyboard, which is just a cheap Microsoft Wired Keyboard 600. It does feel comfortable, and easy to type on. If it ages, it is just $17 to replace. I am less convinced that another keyboard would improve things significantly, I disliked the very clicky feeling of IBM model M and the old, not so comfortable (because relatively high), straight keyboards. I found also the Apple keyboards to be sadly a bit tiring with their very short action, I enjoyed their compactness but I suspect this is what made me place my hands in bad positions.

I manage to do around 70 wpm (words per minute) on the Microsoft Wired Keyboard 600, as well as on some very basic 4€ keyboard (old style slightly noisy chinese rubber dome Atlantis Land K2101), but on the Logitech K800, I only do 55 wpm. So there it is for the 80€ keyboard compared to the 4€ one. Those numbers are not very precise, I only tried a silly simple wpm website: http://www.typeonline.co.uk/typingspeed.php

I am still a bit curious about those MS comfort curve keyboards, they don't have great reviews but it might be a small improvement over the basic Wired Keyboard 600.

Keyboard Porn

I have been browsing the web, looking for a nice computer keyboard. Programming is a big part of my job, a comfortable keyboard is therefore important to my daily life.

I have some nostalgia for the original Microsoft natural keyboard, the white one with standard home and end keys. When it came out it looked revolutionary. I remember really improving my typing speed on it. The only minor annoyance was the heavy and loud space bar. It's sad that MS does not make those in azerty anymore (I know qwerty is better for programming - I used it while I was working in the US, but it's just too annoying to have a different layout from 95% of the people around). The new MS ergonomic model just looks ugly and scary with all those extra keys.

At home I have a Logitech K800, very practical as I often use it from the couch and at nighttime. When I tried it, I was impressed with the key feel: the action is a bit longer than a laptop keyboard (especially the Apple ones), and smooth. But one day, my son, by keeping on hammering on it with all the force of a 2 years old, damaged it. Some keys would print many times other letters. Still after unmounting some keys, and after washing the keyboard several times and waiting  a couple of weeks it started working again. But now the keys feel very mushy and not very nice to type on. This is obviously due to the strong abuse, and the fact that the back is flexible plastic probably did not help. And I wonder if a new K800 (or a Logitech Illuminated) would not just age the same way.

I looked and looked, read the forums. The hip keyboards seem to be the mechanical ones. It tempted me to get a WASD keyboard even though I never liked the feel and sound of my uncle's IBM model M keyboard.  WASD offer the combination of red cherry switches and o rings which should be significantly better for me.

Somehow despite all this research for a better keyboard, I kind of like my current work keyboard, which is just a cheap Microsoft Wired Keyboard 600. It does feel comfortable, and easy to type on. If it ages, it is just $17 to replace. I am less convinced that another keyboard would improve things significantly, I disliked the very clicky feeling of IBM model M and the old, not so comfortable (because relatively high), straight keyboards. I found also the Apple keyboards to be sadly a bit tiring with their very short action, I enjoyed their compactness but I suspect this is what made me place my hands in bad positions.

I manage to do around 70 wpm (words per minute) on the Microsoft Wired Keyboard 600, as well as on some very basic 4€ keyboard (old style slightly noisy chinese rubber dome Atlantis Land K2101), but on the Logitech K800, I only do 55 wpm. So there it is for the 80€ keyboard compared to the 4€ one. Those numbers are not very precise, I only tried a silly simple wpm website: http://www.typeonline.co.uk/typingspeed.php

I am still a bit curious about those MS comfort curve keyboards, they don't have great reviews but it might be a small improvement over the basic Wired Keyboard 600.