Wizard of X

Part 1 was just laying some foundation. Now it's time to tackle some of the more troublesome bits of managing XLSX through POI. The first challenge I wanted to tackle is how to define Fonts and CellStyles. Every time you create one of these elements, it gets added to the .xlsx file, even if it is a duplicate of something you've already created. Also, there is a maximum number of each, so if you create your cells dynamically, you can run out of room to create new cells. You can get around this by first creating your spreadsheet, and then applying styling to groups of cells. Buy my goal was dynamic reports. I want to write a report without knowing what kind of data is going to be in each row or cell.

I've been working on this project for about two years off and on now. Some parts of it shaped up early on, while others have undergone significant refactoring. Most of my reports are in Excel, but some few are done in Word. So I started with a class that accepts any POI XML document and a file name, and sends that file to the browser.

Cracked a nut today. I currently have some reports in XPages that export to Excel, but it is completely separate code for what is seen in the browser vs. what is in the spreadsheet. This is terrible for a number of reasons, but the main two being discrepancies and the length of time it takes to generate a spreadsheet after you've already waited for the report. Ideally, you could use the same data source for the XPage and the spreadsheet. But that turns out to be more difficult than it seems at first glance. The view wants to be fed a list of row data, but POI needs a lot of help knowing what to do with that data. So today I finished a POI wrapper that accepts List<List<Object>>, applies custom formatting without generating a ton of duplicate Fonts and CellStyles, and automatically sums numeric data. It aligns headers with data, and allows me to specify custom formatting that is universal for the application. More to come...

One difference I've noticed when I create my bean in the XPage rather than having the server manage it, is that a managed bean seems to be created only once, while loading it up in a DataContext seems to execute the constructor multiple times. If you have a somewhat slow bean, like say it is compiling report data, this can be a significant performance hit. In the example I'm working on today, generating report data took 246 seconds as a DataContext and 117 seconds as a managed bean.

For a long time, every time I wanted to create a controller or model bean, I wrote it and then added it to my managed beans in faces-config. But this can create a very bloated faces config, particularly when these beans are only used by a single XPage. Then I learned about dataContext.

To future me (and anyone else who comes across it): WebContent/ is the root folder for resources. Not WebContent/WEB-INF/. WebContent/WEB-INF/ is actually protected and hidden from the browser, even if you explicitly include it in your URL. So put your /js/ folder and /css/ folder under WebContent/ and not WebContent/WEB-INF/. Future you will thank you for remembering or for checking here before spending several hours trying to figure out why this isn't working. Again. You're welcome. - Past you.