I'm currently working on a project for a small manufacturing operation. They wanted to automate the way they schedule & produce work orders or jobs. Their biggest concern was what I like to call "forward compatibility". Forward compatibility means that if they need to make changes to their system in the future, then they won't need me to redesign their system, there should be a simple method to add, edit, update procedures, etc... When I took a look at their existing processes, I found a lot of Excel spreadsheets driving their operations. For instance, there is a spreadsheet that drives their "smart" part numbering system. There is a spreadsheet that drives their product testing operations. Plus, these spreadsheets are all Revision Controlled (that's Version Control in the IT world).

My solution was to use their existing spreadsheets as inputs to my Custom Job Control System. I designed the system to always use the most current version of the relevant spreadsheets (which are always stored on the network in a static location) as data. That way, they can simply control those documents as they always have and they won't have to reconfigure my system when they have changes.

The main thing that makes this system possible is the POI project from the Apache Software Foundation. The POI project is a pure Java API for reading & writing Microsoft Office Documents. It allows simple Java Object Oriented access to MS Excel, Word, PowerPoint, Visio, Outlook & Publisher. However, some parts are still in their infancy. In particular, MS Word access is simple and not very well developed. MS Excel access, however, is about as powerful as you could possibly need. Accessing spreadsheets is simple and powerful, IF you know what parts of the spreadsheet you need to access. The POI project makes it simple to access Excel documents, but Microsoft didn't make Excel documents simple to use (at least from the perspective of the underlying implementation). So, without further ado, here are the 2 most important lessons that I've learned about reading Excel spreadsheets with Apache POI.