Configuring Ubuntu for Drupal Spreadsheet Node Imports

****Update. Sheetnode 1.5 no longer requires a standalone Java Server so this post is N/A. Please see http://drupal.org/project/sheetnode for info***

The Sheetnode module for Drupal 6 is a great way to incorporate shreadsheets as node types useful for calculators, sales tools, or a variety of other uses.  Installing the Sheetnode module is fairly straitforward according the instructions on the project page at http://drupal.org/project/sheetnode.  The only requirement is downloading the SocialCalc libarary and applying the module author's included patch.  Out of the box, spreadsheets can then be created as new nodes.  Many organizations already have complicated spreadsheets in xls format that need to be imported rather than manually created.  Importing xls spreadsheets works great, but does require some specific server configuration to work properly.  Since a lot of this information is scattered in bits accross the web, I've consilidated the process for those running Ubuntu servers.

Install the Sheetnode Module in accordance with the instructions at http://drupal.org/project/sheetnode.

*Disclaimer* Please backup your server image before proceeding.  I use this setup on a development platform and then transfer imported xls sheetnodes to the production site.  The Sheetnode XLS Import module requires the standalone version of the PHP-Java Bridge servlet which is not recommended in a production environment.  Once you're ready to proceed:

Check and see if you already have a JRE installed:

If it is you will see something like:

If it's not installed, we'll update our apt-get sources and install it. We need to add the multiverse repositories to our sources so that we can install third party packages such as the Sun Java 6 JDK and JRE. Using the multiverse repository, it's important to realize that third party packages may have some risks.

Add the following code to the end of the file if the multiverse repositories aren't already enabed, exit, and save: (specific to Ubuntu 9.04, consult Ubuntu.com for the proper repositories per distro)

Now we need to let the apt manager know we added it:

Try it again:

Once we have the JRE set up, we need to install the PHP-Java Bridge. Currently the Sheetnode module requires the 5.x release even though 6.x is available. The PHP-Java Bridge and the Apache POI library need to be accessible by the web server. Change to a the accessible directory you want to use and download the bridge:

Next we need to extract the .jar file from the .war file:

Add the following line to your php.ini file:

Next we'll download the Apache POI needed to convert xls files. Once again make sure you're in a web accessible directory:

Bounce the webserver for our new additions to be included:

In order to use the import function we need to manually start the servlet. I do not run this servlet on a production site:

Now we're ready to configure Drupal for the imports. Enter the web accessible POI path at admin/settings/sheetnode/xls and the web accessible bridge settings at admin/settings/pjb.  Next try to import a spreadsheet under node/add/xls.  Errors generally will result from improper servlet setup but can occur if .xlsx spreadsheets are imported.  Newer spreadsheets need to be saved in 2007 .xls format.  I'm currently running Ubuntu 9.04. I hope it helps, it's a great module.