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...