link2perry/group-data
In the true spirit of Dudes, I wanted to share our progress on this and document some technical choices that turned out to work nicely. POI, a java library created to help developers work with Excel spreadsheets has a few different approaches. Among these, you have HSSF and XSSF. My first approach was to try to use XSSF. With XSSF, essentially what happens is that the spreadsheet gets represented in XML. The api is very straightforward but had a major drawback for our use-case in that it reads in the entire document up front, comparable to the DOM approach to XML. While this turned out to work on my local tests, the program failed on the "production" spreadsheets that contained 60K+ rows per sheet with 8+ sheets per file. Needless to say, the program was running out of memory trying to digest the whole document up front. The solution came in the form of POI's "eventusermodel". With this, the api takes an event listener approach. You register the events you're interested in (think "sheet bound", "numeric cell entered") and then proceeds to visit all the elements in the spreadsheet in a SAX-like manner, firing off these events. This approach allowed me to manage the memory footprint.