A few days ago the blog entry CKEditor 3.0 is here! caught my attraction. I know Pebble is using FCKEditor, so i read the whole news and wanted to feel the amazing difference...

To use CKEditor with this bundled version of Pebble i repackaged the editor as web.ckeditor and added it to the Equinox platform. With a little help from spring-osgi and a single line in the MANIFEST.MF the editor is deployed as /ckeditor

Web-ContextPath: /ckeditor

In the pebble.web bundle i replaced the FCKEditor related lines in the commentForm.jsp with:

<script type="text/javascript">
CKEDITOR.replace( 'commentBody',
	    {
	        toolbar : 'Basic'
	    });
</script>

And in the main page.tag as follows:

<script type="text/javascript" src="/ckeditor/ckeditor.js"></script>

That's almost everything to do. Deleting one character 'F' in a Maven configuration file and the truly amazing result:

Works like charm! Congratulations!

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
Map headerMapping = createHeaderMapping(headers, headerRow);

...
HSSFRow currentRow = sheet.getRow(i);
...
HSSFCell cell = currentRow.getCell(headerMapping.get(header));
if (cell != null) {
    // add String or Integer
    if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
        row.add(cell.getRichStringCellValue().getString());
    } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
        double value = cell.getNumericCellValue();
        row.add(Integer.valueOf((int) Math.round(value)));
    } else {
        return rows;
    }
} else {
    // no more valid data found
    return rows;
}

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 se in the last entry 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...

In Part I we squeezed raw data out of an Excel sheet containing the SCRUM story cards. With a little bit of groovy this raw data got converted to XML. I the 2nd Part we created the XML needed as input for our PdfGenerator. In Part III we pimped an Apache FOP example and used an XSL-FO style sheet to render the PDF generated by the SCRUM StoryCards Generator.

We ended up with two OSGi services:

1. pdfGenerator
2. storyCardBuilder

To include the OSGi services into our web application we ask Spring to handle all that low level stuff and inject the services directly into our controller:

<osgi:reference id="pdfGenerator" interface="de.datenkollektiv.util.fop.PdfGenerator" />
<osgi:reference id="storyCardBuilder" interface="de.datenkollektiv.scrum.cards.StoryCardBuilder" />

We want spring-osgi to deploy the story card generator into the web context path '/scrum'. This is done with a little hint in MANIFEST.MF

Import-Package:
...
 de.datenkollektiv.util.fop,
 de.datenkollektiv.util.poi,
 de.datenkollektiv.scrum.cards,
...
Web-ContextPath: /scrum

Next we configure the basic part of spring-mvc. Let's have a look at the OSGi aware web.xml:

<context-param>
  <param-name>contextClass</param-name>
  <param-value>org.springframework.osgi.web.context.support.OsgiBundleXmlWebApplicationContext</param-value>
</context-param>
<listener>
  <listener-class>org.springframework.web.context.ContextLoaderListener</listener-class>
</listener>

<servlet>
  <servlet-name>scrum</servlet-name>
  <servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
  <load-on-startup>1</load-on-startup>
</servlet>

One additional context listener is needed to work with session scoped beans.

<listener>
  <listener-class>org.springframework.web.context.request.RequestContextListener</listener-class>
</listener>

Now let's look into the spring-mvc configuration itself: The well known viewResolver and the Apache commons multipartResolver (for the Excel file upload - surprise, surprise ;-)

<bean id="viewResolver" class="org.springframework.web.servlet.view.InternalResourceViewResolver">
  <property name="prefix" value="/WEB-INF/jsp/" />
  <property name="suffix" value=".jsp" />
</bean>

<bean id="multipartResolver" class="org.springframework.web.multipart.commons.CommonsMultipartResolver" />

The two controllers storyCardsController and fileUploadController are wired with the needed OSGi services and the session data per constructor for OSGi services and data alike.

<bean id="sessionData" class="de.datenkollektiv.scrum.web.SessionData" scope="session">
  <!-- this next element effects the proxying of the surrounding bean -->
  <!-- required to inject the scoped bean into controllers -->
  <aop:scoped-proxy />
</bean>

<bean id="storyCardsController" class="de.datenkollektiv.scrum.web.StoryCardsController">
  <property name="pdfGenerator" ref="pdfGenerator" />
  <property name="sessionData" ref="sessionData" />
</bean>
<bean id="fileUploadController" class="de.datenkollektiv.scrum.web.FileUploadController">
  <property name="storyCardBuilder" ref="storyCardBuilder" />
  <property name="sessionData" ref="sessionData" />
</bean>

Generating the story cards involves two steps. Upload the Excel sheet and generate the XML datastructure using the OSGi service storyCardBuilder is the first step: /upload.form. (Exception handling has been removed for better readability)

@RequestMapping(value = "/upload.form", method = RequestMethod.POST)
public String onSubmit(HttpServletRequest request, byte[] file) throws Exception {

  ExcelTemplate template;
  template = new ExcelTemplate(file);
  List> data = template.getRows(HEADERS);
  String dataAsXml = storyCardBuilder.buildXml(data);
  sessionData.setData(dataAsXml);
  request.setAttribute("downloadAvailable", true);
  request.setAttribute("storyCardCount", data.size());
  return "storycards";
}

@InitBinder
public void initBinder(WebDataBinder webDataBinder) {
  webDataBinder.registerCustomEditor(byte[].class, new ByteArrayMultipartFileEditor());
}

If the uploaded Excel has been successfully transformed into the XML representation we present the user the /storycards.pdf download link where he can download/trigger the generation of the PDF.

@RequestMapping("/storycards.pdf")
public void generateStoryCards(HttpServletRequest request, HttpServletResponse response) throws Exception {

  Resource xsl = new DefaultResourceLoader().getResource("/StoryCard.xsl");
  InputStream inputStream = xsl.getInputStream();
  String data = sessionData.getData();
  byte[] pdf = pdfGenerator.generatePdf(new ByteArrayInputStream(data.getBytes()), inputStream);

  response.setContentType("application/pdf");
  byte[] buf = new byte[1024];
  OutputStream out = null;
  InputStream in = null;
  try {
    out = response.getOutputStream();
    in = new ByteArrayInputStream(pdf);
    int len;
    while ((len = in.read(buf)) < 0) {
      out.write(buf, 0, len);
    }
    in.close();
  } catch (IOException e) {
  } finally {
...

How to customize the generated PDF with the help of a bundle fragment ist coming next...sorry, Moritz ;-)