provide access to spreadsheets from within webpages
Branch: master
Clone or download
Fetching latest commit…
Cannot retrieve the latest commit at this time.
Permalink
Type Name Latest commit message Commit time
Failed to load latest commit information.
bin
lib
public
setup
silverstripe_spreadbutler
t
templates/layouts
.gitignore
AUTHORS
COPYING
README.pod

README.pod

NAME

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

SYSTEM DESCRIPTION

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.

SETUP

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.

USAGE

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.

AUTHOR

Tobi Oetiker <tobi@oetiker.ch>

HISTORY

2011-08-31 Initial Version

LICENSE

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.