Friday, March 22, 2013

M$FT Excel format from Common Lisp.

In light of Hans Hübner's recent spike into the "no more Java the language" railhead. I'll certainly share recipes for using the monkeys' libraries to manipulate various historical Microsoft Excel formats.  The dirty secret of computing on the modern enterprise trapped in the black iron prison of non-free document formats is that current versions of Office don't always handle previous versions very well.  Since Apache POI has been around for the better part of a decade, it provides a fairly reasonably DWIM fallbacks for reading such formats.  Somehow, once whatever you are working with is touchable with ANSI Common Lisp, all seems to be so much better…

Two examples that come to mind in swigging the Apache POI libraries over to a syntax that the Bear can paw through:
  1. Alan Ruttenberg used JSS to work with Apache POI as read-ms-docs which was released  as part of the lsw Semantic Web toolkit wrapping RDF(S) and OWL inference implementations in Java
  2. Mark Evenson extended Alan's work as part of reading to/from SQL to be able to roundtrip the data within Excel spreadsheets to Oracle databases.  The database reflected the pure data of the Excel document, using the assorted Microsoft "macros" to sanitize input.