SpreadButler - A System providing access to a spreadsheet from within a webpage

SpreadButler is a system for live integration of spreadsheet data into webpages. It uses ajax technology and is therefore independent of the technology used to create the webpages themselfes.

• A Mojolicious server side application, able read an excel spread sheet and offering a rest interface to access its content.

• A jQuery javascript plugin that requests data from from the server and inserts the data into a suitably prepared html table object.

SpreadButler uses perl with the Mojolicious and Spreadsheet::Read packages.

• Install perl (5.8 or better 5.12)

• Unpack in a directory of your choice aka $INSTALL_DIR

• Make sure the prerequisite packages Mojolicious and Spreadsheet::Read packages are installed. The easiest way todo this, is to run

  $ cd setup
  $ ./build-perl-modules.sh

  This will create a thirdparty directory and copy all the required bits there.

• Setup a directory where you store the spreadsheets you want to make available via SpreadButler ($SPREAD_BUTLER_DATA)

• Integrate the SpreadButler fastcgi script into your webpage using a script like this:

  DIR/sb.fcgi:
  
  #!/bin/sh
  export SPREAD_BUTLER_DATA=/some/DATA_DIR
  exec /INSTALL_DIR/spread_butler.pl fastcgi

  and make sure your webserver actually executes this as a fastcgi script.

The client side integration of speadButler relies on javascript. The necessary files are served directly by SpreadButler. To use them in your webpage, just load them in the header using:

<script type="text/javascript" src="DIR/sb.fcgi/js/jquery.js"></script>
<script type="text/javascript" src="DIR/sb.fcgi/js/jquery.SpreadButler.js"></script>

To activate spreadButler, call the spreadButlerFillTable method on a table node:

<script type="text/javascript">
$(document).ready(function(){
  $('#mySpread').spreadButlerFillTable({
    server : 'DIR/sb.fcgi',
    file   : 'sample.xlsx',
    stopColumns: ['B','C'],
    startRow: 4,
    minColumn: 'B',
    maxColumn: 'F',
    minRow: '1',
    maxRow: '50',
    recalcClick: $('#recalcButton'),
    finalizeCallback: function(action){
         $('#addField').change(function(){    
             // check input
             action(); 
         });
     }
    sortCol: 1
  });
});
</script>

This will fill data into the table with the mySpread id. Provding interactive recalculation by connecting with the click event on the recalcButton.

Rows of class sbReplace will get their td,th cells replaces with the evaluated result of the embedded javascript expression. The map d holds all the fields requested from the server.

Rows of class sbRepeat will get repeated for every row in the spreasheet, starting from startRow to the rwo where all columns mentioned in stopColumns are empty. The current row is available in the variable r.

<div>
  <input id="addField" type="text" value="1"></input>
  <button id="recalcButton">Calc!</button>
</div>
<table id="mySpread">
  <tr class="sbReplace">
    <th>d.B2</th>
    <th>d.C2</th>
    <th>d.D2</th>
  </tr>
  <tr class="sbRepeat">
    <td>d['B'+r]</td><td>sprintf('%.2f',d['C'+r])</td>
    <td>sprintf('%.2f',d['D'+r])</td>
    <td>sprintf('%.2f',parseFloat($('#addField').val())+parseFloat(d['D'+r]))</td>
  </tr>
</table>    

You can enter multi statement javascript into table cells, but then there must be a return statement in your script. For single statement script, the return gets added implicitly.

Tobi Oetiker <[email protected]>

2011-08-31 Initial Version

This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation; either version 3 of the License, or (at your option) any later version.

This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.

You should have received a copy of the GNU General Public License along with this program; if not, write to the Free Software Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.