Hazard's stuff

25 May, 2014

Sample jQuery.sheet online spreadsheet backend with load/save functionality

— Posted by hazard @ 2014-05-25 12:30
I've been integrating an online spreadsheet functionality into DokuWiki and jQuery.sheet looked like the most suitable candidate. It comes with a number of examples, but there is no server-side backend that will save/load the sheets. As a proof of concept I created a simple jQuery.sheet database backend example in Perl.

The backend saves the sheets as XML data into MySQL database, the browser communicates with the backend using simple HTTP POST/GET requests (cmd=get/save,sheet_name,sheet_value parameters). Backend is very basic and can be easily rewritten in PHP or whatever else.

Requirements: web-server (e.g. Apache), Perl with DBI::MySQL module, MySQL, jQuery.sheet v3.1 (tested with 3.1-RC5)


1) Put jQuery.sheet v3.1 into the same directory as html/spreadsheet.html
2) Web-server should be configured to execute cgi-bin/jquery-sheet-handler.pl as a Perl CGI script
3) Adjust html/spreadsheet.html if CGI script base URL is not /cgi-bin/
4) Create MySQL table as in sqlschema.txt. All sheets will be stored in this table in XML format
5) Adjust cgi-bin/jquery-sheet-handler.pl to use correct MySQL DSN, username, pass
6) Create your page, pass sheet name as "sheet_name" URL parameter to spreadsheet.html

Example of inclusion of a spreadsheet from within a page:
<iframe scrolling="no" style="hidden" height="500px" width="100%" src="spreadsheet.html?&sheet_name=test"></iframe>

Download the backend HTML and Perl source from here.