Apache POI is a java library which allows to build Office-Documents. POI stands for “Poor obsfuscation interface” and is a good description. Nonetheless it’s the only lib I know which allows you to produce what most customers still don’t want to miss: MS Excel sheets and Docs. In the following post, I want to list some hints I found in the last weeks when working on excel sheets:
– Cellstyles don’t work like normal objects: if you use a cellstyle for a cell and AFTERWARDS changes the style to use it for some other cell, you will find only the latter style in the resulting document
– Don’t mix up font definition and borders: Use the HSSFRegionUtil.setBorderXXX() for borders. This methods puts a border around the given region preserving the style (font) defined before. Keep in mind, that you have to use the method AFTER you defined the cell with a certain style.
– if you use Springs AbstractExcelView, do not just implement the excel-building code in a subclass of it. Build an abstraction layer between the Spring view and the building code to avoid dependencies to HttpRequest and HttpResponse. Otherwise testing will be painful. I defined an AbstractDocumentBuildView extending the AbstractExcelView that defines an abstract method which passes only the Workbook and the data to the implementing subclasses.
– Make a big list of constants defining the column/row-structure and labels!
– It is not possible to make “autofilters” so far! Just don’t try it.
– To put a dataFormat of your choice in a cell, use cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat(xxx));
– HSSF is short for “Horrible SpreadSheet Format” (not kiddin’!). So just try to take it easy …