Building A SCRUM StoryCards Generator - Part I

Sick of all those HelloWorld OSGi tutorials? Follow this short series of blog entries and learn how to build a SCRUM StoryCards Generator: A small webapplication running inside an OSGi environment.

An Excel list containing the stories for a sprint like this one:

will be transformed into a PDF suitable for a SCRUM task board.

Today in the first step we squeeze the storycard data out of the Excel sheet with the help of Apache POI. The poi library "Love At First Sight."? I thought the code i'd write would look anything like this:

new ExcelSheet("./sample.xsl", "sprint3").read("B6:G10");

No, not really - at least not with my knowledge of the library :( After some cups of coffee a first version of my ExcelTemplate was ready:

public class ExcelTemplate {

  private final HSSFWorkbook workbook;

  public ExcelTemplate(byte[] content) {
    InputStream stream = null;
    try {
      stream = new ByteArrayInputStream(content);
      workbook = new HSSFWorkbook(stream);
    } catch (IOException e) {
      throw new IllegalArgumentException("can't open Excel file from given content");
    finally {
      try {
        if (stream != null) {
          stream.close();
        }
      } catch (IOException e) {
        LOG.warn("can't close created content stream");
      }
    }
  }

Now let's squeese data out of the ExcelTemplate. Let's keep things simple. We expect data from the first sheet only. Use the first row with a given String as header row and add the following rows as String and Integer as plain List.

HSSFSheet sheet = workbook.getSheetAt(0);

// find header row (with hint)
HSSFRow headerRow = findHeader("description");
// map header names to sheet columns

This was no fun. I'm sure there is a better way to extract a data blob from an Excel sheet but this works for now. Let's do some OSGi packaging: With the ExcelTemplate taking a byte[] as constructor argument and the squeeze method returning a plain List we hide the poi API completely from bundles using the ExcelTemplate.

Export-Package: de.datenkollektiv.util.poi

The using bundle needs only a single import in the MANIFEST.MF: All the ugly code is buried in the util.poi bundle and any person with a deeper knowledge of the poi library can replace it (without stopping the webapplication if need be).

Import-Package: de.datenkollektiv.util.poi

The following code shows a sample usage of the ExcelTemplate taken from the spring-mvc controller of the StoryCards Generator example as we will see in the last post of this series.

List<List<Object>> rowDataAsList = new ExcelTemplate(data).getRows(HEADERS);

Next step is to transform the raw data into an XML representation using the Groovy MarkupBuilder.

The story continues...this post is part of a series about SCRUM storycards.

▶︎ Building A SCRUM StoryCards Generator - Part I

▷ Building A SCRUM StoryCards Generator - Part II

▷ Building A SCRUM StoryCards Generator - Part III

▷ Building A SCRUM StoryCards Generator - Roundup

Header cover Photo by G. Crescoli