Dark Views

When developing software, one pain is always the same: Getting valuable input from the customer. One solution is to use a tool that your customer probably understands: Excel.

“Oh, great, CSV files,” you think. No. I mean Excel work sheets, with colors and formatting and formulas. Let me explain two approaches to save you a lot of time and effort, pain and tears.

Image via Wikipedia

Apache POI

Apache POI is the Swiss Army Knife for Java developers who have to deal with Excel. It basically allows you to access the content of an Excel document in a humane way: By book, sheet and then indexed by row and column. It doesn’t matter if the file is in the old binary format (*.xls) or the new XML one (*.xlsx).

Especially the XML format is suited perfectly to allow your customers to send you master data. Just write a small tool that reads the Excel file directly and dumps it into your database. No CSV conversion! You can use colors to mark rows/columns to ignore. You can define styles to ignore (like “Section” or “Heading”). That allows your customer to keep the table readable.

Formula Compiler

But the data in the spreadsheets is only half of the value. The guys at Abacus Research AG created a new project: Abacus Formula Compiler (AFC). This gem gives you access to the formulas in the cells. POI does that, too, but you will just get a string. AFC will convert the formulas into Java Code. That means your customer can not only send you master data but also how to process it.

They can send you a spreadsheet with an example what they are talking about. They can use the Excel sheets they have been using to drive their business for the last five years. If something changes, they can send you an updated version, you run AFC on that and get a new set of classes which replace the old ones and which do exactly what your customer wants.