Modelling spreadsheets for producing sheets programmatically

What I learned today — 3 April 2018

Building up a spreadsheet in code can be very tedious — creating cell objects and setting the data and position of each cell. This low-level way of thinking can easily make you miss the fact that groups of cells in a sheet represents some type of object that makes sense to the user. The relative position of one cell to another is also more important than their absolute coordinates.

Using this insight one can model a sheet as a collection of sheet-objects, each consisting of one or more sheet-objects, relative to one another, which can produce the cell objects for your framework, like Apache POI, of choice. Use sheet-object factories to populate sheet-objects with the correct data, or to compose multiple sheet-objects.

For example, one may have a Ledger sheet-object, which consists of a Debit Side and a Credit Side sheet-objects. These, in turn may consist of Entries.