As a follow-up to my previous post about manipulating spreadsheets natively in Ruby I figured I'd spread the word on what I learned.
The ruby spreadsheet gem is nice but it lacks one key feature: it doesn't support recalculating formulas in a spreadsheet after a dependent value has changed. This is a show-stopper for my app as I need to allow users to change values that drive numerous formulas and immediately see the results of the recalculations. I have been in touch with Zeno Davatz, who seems to manage the gem, and it appears that this capability is slated for a release down the road but not anytime soon. I tried using the google_spreadsheet gem. This actually sort of worked, with one key issue. I could interactively modify a Google spreadsheet in my Google Docs account and see the values update immediately. The problem is the "local" ruby copy of the spreadsheet doesn't reflect updates to formula values until you re-synchronize the in- memory copy of the spreadsheet with the one on the server, and that seems to take anywhere from 5-10 seconds, a non-starter for UX reasons. Also, google_spreadsheets has a limit of 256 columns (and some limitations to the functions you can use) but I could live with that. The formula refresh delay -- not so much. I also looked into Windows-based solutions, thinking I might be able to write a web service my Rails app could hit but that seemed over the top, not to mention prohibitively expensive when you add in Windows and SharePoint costs, hosting, etc. There is a SpreadScript excel calculation engine product out there, but it only supports Python, Perl and Tcl, so again I'd have to write a web service or wrapper to use it. Ouch. And it costs $995 to deploy, so double Ouch. Someone suggested JRuby wrapping a Java API. That sounded much more like what I was after. I found the Apache POI project, which enables interaction with MS Office documents like Excel spreadsheets. I built a little Java test app today and voila, formulas that update instantly! Of course, now I have to figure out how to make use of this capability, which brings me to a question: can anyone suggest a good way to use JRuby in a Ruby-based Rails app to make use of the Apache POI Java project? Am I back to trying to write a web service? Any suggestions would be greatly appreciated! Cheers, Chris -- SD Ruby mailing list [email protected] http://groups.google.com/group/sdruby
