Adding text cells to an Excel worksheet using xlsx4j

I’ve just started using xlsx4j (a large collection of classes in what is more widely known as the docx4j project) to implement Excel export as part of a large, ongoing StackThread project. As an extremely brief introduction, docx4j uses the Java Architecture for XML Binding (JAXB) to create an in-memory representation of the Microsoft Open XML format that underlies all Microsoft Office documents in the post-2007 era.

Since xlsx4j is effectively a wrapper around the Open XML format, it becomes immediately apparent in using the library that you need to have some familiarity with the file format in order to get anything working. The first snag I hit was the manner in which Excel handles string storage, namely through the use of a shared string table. The shared string table contains every unique string in the workbook only once. Every cell that contains a string actually just holds a reference to the appropriate string in the shared string table. While I did eventually get this working, it took quite a lot of code to do so:

This is, of course, greatly simplified. Notably, as you’ll see on line 46, it’s not keeping track of unique strings. While this wouldn’t be too difficult to implement (a HashMap<String, Integer> could be used to keep count of unique strings and instances), the above code feels very heavyweight for one of the most basic spreadsheet manipulation tasks. Thankfully, there is an easier way.

The easier way

After a little bit of digging around, I discovered that cells also support inline strings. With the addition of a convenience newCellWithInlineString method to generate INLINE_STR cells, the shared string table code can be removed entirely, leaving this:

While the first example could definitely have been distilled down and refactored to keep the workings of the shared string table hidden away, the lack of string-tracking overhead in the second example makes for much easier-to-read code in my opinion. It’s also much faster to get to the point of functional Excel export without having the hassle of setting up the shared string table. I haven’t yet investigated the performance implications of using INLINE_STR when opening the workbook in Excel, but from a quick look at the XML after opening and saving an Excel workbook created using the INLINE_STR method, it looks as though Excel generates its own shared string table when the workbook’s first saved.

Leave a Reply

Your email address will not be published. Required fields are marked *