In one of my recent articles, I wrote about my experiences in generating an Excel report with Apache POI. I want to complete my thoughts by summarizing my experiences with the framework.
As I wrote before, it is quite important to understand the business value of a report. What is it good for? What will the user do with it? This determines for example if there should be formulas or not. Often, the user wants to play with the report by changing values and see the effect of this. To make these changes possible, there have to be formulas in the sheet, not just plain numbers. However, this contradicts with the concept of a pure report. I understand reports as a simple snapshoot of the current state of the application. Hence, all possibilities to change that state should be in the software, not in the report. This points to the absence of formulas in the report. You have to ask your customer, what type of report she wants.
The moment I saw the well formated example Excel file which I had to populate with data, I knew I have to use a template. So I created an Excel sheet with all the formats needed. I loaded it with POI and populated it with data. This is the way to go in case there are fancy formats. Don’t build the whole Excel sheet with POI.
Further down the road, I wrote a pretty ugly class for creating the report. It was full of magic numbers to handle the offsets needed in a more complex sheet. Using magic numbers makes maintaining the code incredibly hard. Use constants instead: COLUMN_INDEX_LAST_NAME = 3;
One of my more resistant bugs used the absence of explicit DataFormats for hiding. I had one column of currencies and one column with plain numbers. The first was formated with a currency DataFormat, the later had no format. The result was that both columns had a Euro-symbol attached to the numbers in them. Only giving every column a DataFormat solved the problem.
In conclusion I have to say that working with POI was far less annoying then expected. I often heard complaints from colleques working with the framework. Maybe my task hasn’t been complicated enough to run in the same trouble my coworkers had.