Experience on Apache POI

Today, I am doing experiment on Apache POI. Company asked me to create plugin for JIRA which is not within my scope as Confluence support engineer. However, I really enjoy it. One feature that I create in the plugin is an ability to convert data within JIRA to excel file. Actually, there are several options that I can use to export to excel file. I have searched through several websites where another peoples have done some research on it and they come with the conclusion that Apache POI is better than the other.

The most important thing on Apache POI is HSSFWorkbook object. This object represents one excel file. One workbook consists of one or several sheets. And one sheet contains several rows and columns.

Below is an example on how to create workbook:

HSSFWorkbook workBook = new HSSFWorkbook();
sheet = workBook.createSheet("Product");
HSSFRow row = sheet.createRow(1);
HSSFCell cell = row.createCell((short) (1));

What a simple code...... hehehe.... Note that the second line is used for creating a sheet named Product and POI will start the index of row and column with 0 instead of 1. Thus the above code will create object row in line 2 (in the excel file, row is started from 1 instead of 0) and will create a cell in column B(in the excel file, column is started from A instead of 0).

In order to fill in the value in the cell, POI has provided you a method setCellValue(). Please take a look at the API documentation for detail information regarding this method.

Another thing that I really like with POI is the ability to manipulate style in every cell. The style is including a way to format the data inside cell. In the plugin that I create, I use an ability of POI to manipulate border of each cell in the sheet. Below is an example to use HSSFCellStyle:
HSSFCellStyle style = workBook.createCellStyle();
style.setBorderTop(style.BORDER_THIN);
style.setBorderBottom(style.BORDER_THIN);
style.setBorderLeft(style.BORDER_THIN);
style.setBorderRight(style.BORDER_THIN);
style.setAlignment(style.ALIGN_CENTER);
style.setFillForegroundColor(HSSFColor.GREY_40_PERCENT.index);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

In order to apply the style on each cell, POI has provided us a method named cell.setCellStyle(style). This method has a parameter HSSFCellStyle which you has manipulated in the previous example.