Extending the export functionality of the Display tag library

Display tag

Display tag is a library we’ve been using at work when developing web applications in Java. It provides a fast and easy way to generate simple HTML tables from collections of objects.

Problems

The tables can also be exported into several alternative formats like CSV, XML, PDF or Excel. The exporting functionality is very basic and especially the output of the default Excel export implementation looks quite crappy:

Display tag Excel export output

How the default excel output looks

Another problem we had is that the export only exports the table (can’t complain though, that’s what the library is supposed to do, tables). Many of the tables in our product are generated based on selections the user makes and we want these selections to be visible in the exports they make. By default you can only add a simple one line caption and a footer to the exported table. We could just stuff all the selection information there on that one line but it would not look good because we have many types of selections all of which can have multiple options.

This is how we would like to have the export look like (manually sketched in Excel):

Export result mock up

The way we want it to look like

Solution

Implement the BinaryExportView

According to the Display tag website I need to write my own implementation of the BinaryExportView interface. There is also a TextExportView interface but because we want to output Excel data with style information like font sizes, font weights etc, we need the binary export.  Since I had no idea how to go on with generating Excel, I looked at the DefaultHssfExportView which is one of the default Excel export classes that come with the library. (If you are wondering about the “Hssf”, Display tag’s binary excel export uses the Apache POI project’s library called POI-HSSF to access and modify Excel workbooks.

Looking at the DefaultHssfExportView’s doExport() method, it doesn’t do any of the Excel generation there but delegates the task to a HssfTableWriter in which the table generation is nicely separated into methods like writeCaption(), writeTableHeader() and so on. So I clearly need my own writeCaption() in the HssfTableWriter and need generate my custom caption there.

My first thought was to extend the DefaultHssfExportView and HssfTableWriter and just override the essential methods since the rest of the export works well enough for us. However, I couldn’t really do this since all the fields in those classes were private so I couldn’t for example access the HSSFWorkbook wb -field in my subclass. That’s why I ended up copy / pasting (*cringe*) the classes and replacing the appropriate methods and imports with my own implementation (and fixing a bug* in the original HssfTableWriter).

Get the caption data to the new BinaryExportView

I still had one open question: how to get the caption information (header, selections) easily to my HssfTableWriter since the caption field in the TableModel is the only way to pass information.  So I need a neat way to pass structured information in plain text and put it back together for easy access in the HssfTableWriter.

Of course my initial primal instinct was to reinvent the wheel and design some neat text format and some kind of parser for it. Then I came to my senses and realized this would be a nice place to try out JSON. A quick search at StackOverflow led me to google-gson, which seemed pleasantly simple to use.

I created a simple data transfer class for the caption data:

public class ExcelExportCaptionData {

	private String header;
	private Map<String,Map<String,String>> sublists;

        // Getters and setters omitted..
}

Now I can create an ExcelExportCaptionData instance in my controller / action with the selections and headers and turn in into a JSON string for the Display tag table in the JSP page:

ExcelExportCaptionData data = new ..
data.set..
String jsonOutput = new Gson().toJson(data);

This results in a string like

{"header":"TestHeader",
  "sublists": {
               "Sublist2":{"Opt1":"Val1","Opt2":"Val2"},
               "Sublist1":{"Baz":"Bat","Foo":"Bar"}
                 }
}

I put this string to the display tag table’s caption with the <display:caption> tag in the JSP page:

<display:table>
  <display:caption media="excel">${excelCaptionData}</display:caption>
  ..
</display:table>

Finally, in my HssfTableWriter, I can access the JSON string from the TableModel and turn that back to ExcelExportCaptionData instance:

ExcelExportCaptionData data = new Gson().fromJson(tableModel.getCaption(), ExcelExportCaptionData.class);

Generate the spreadsheet

Now I have all the caption data in the easy to access object and I can generate the excel caption before the actual table using the POI-HSSF API. I’m not going into details on that, just look at the original HssfTableWriter to see how it’s done or refer to the POI-HSSF documentation.

Update displaytag.properties

Theres one final thing to do in order to enable the new export classes. In the displaytag.properties file, if you already used some other excel export class, replace the

export.excel.class=x.y.z.MyExcelExportView

with the fully qualified name of our new BinaryExportView implementation. Make sure you also have

export.excel=true

otherwise the new export option won’t show up the tables.

Result

Here’s a screenshot of the actual output with our custom export classes:

Final result

Final result of our custom export (private data obfuscated)

*Bugfix

When I started looking into this problem I noticed that some of your original excel exports where crashing with an error like

Exception: [.DefaultHssfExportView] !DefaultHssfExportView.errorexporting! Cause: The 'to' row (0) must not be less than the 'from' row (24)

Some googling led me to the display tag issue tracker and this known issue http://jira.codehaus.org/browse/DISPL-616. The reason was that the underlying POI-HSSF API’s  cell merging feature had changed to use a different class. The display tag’s HssfTableWriter had been updated to reflect this change but they had failed to notice that the order of row and column number parameters had changed in the new API class:

Old API parameter order:

firstRow, firstCol, lastRow, lastCol

New API parameter order:

firstRow, lastRow, firstCol, lastCol

This issues hasn’t been fixed in the display tag source as of writing this so anyone using the default excel export should be aware of this. If I remember correctly it only occurs if you use captions or footers in your export though.

Advertisements