DocsFree Sheetster JavaScript API

Owned by Robert Avery
Last updated: Sept 22, 2012Version comment
4 min read

Introduction

Sheetster Web Application (Sheetster Web Spreadsheet) is an Open Source JSP/JavaScript/AJAX web application designed for easy customization and enhancement.

The web app uses a number of Open Source Javascript client libraries to provide various functions. Familiarity with these underlying toolkits will greatly enhance your understanding of the source code and success in working on the application.

Creating Spreadsheet Web Apps with the Sheetster JavaScript API

Sheetster was built from the ground-up as a development platform for spreadsheet application developers and power users.

Sheetster web scripting uses standard JavaScript, AJAX libraries prototype.js, and the server and client side Sheetster APIs:

  • Sheetster REST API -- allows web scripting of server-based objects and data
  • Sheetster JavaScript API -- spreadsheet user interface JavaScript API

Furthermore, there are windowing and dialogs provided by Prototype Windowing Toolkit (windowing, dialogs, and MDI).

The primary 3rd party tools used by the Sheetster web app are:

  • Prototype.js Javascript Library (basic Javascript and AJAX convenience code, OOP extensions)
  • Dojo Toolkit (Charting, Auto-complete, JS compression)
  • Prototype Windowing Toolkit (windowing, dialogs, and MDI)
  • script.aculo.us (DHTML visual effects)
  • DynamicDrive widgets (tab menu, tree menu)


Object Oriented JavaScript

Prototype api provides a useful OOP paradigm which extends basic JS objects with accessibility and convenience methods.

Sheetster spreadsheet functionality is based upon a core set of JavaScript Objects in the '/grid/grid/js/' folder (aka: sheetHandle.js, rowHandle.js, chartHandle.js) loosely correspond to the server-side REST P.A.D.S. Plugins (PluginSheet, PluginRow, PluginChart). Likewise the REST Plugin methods correspond to functionality available in the underlying ExtenXLS Java Spreadsheet API objects and methods (WorkSheetHandle.java, RowHandle.java, ChartHandle.java).

By separating the JavaScript objects into corresponding .js files allows for much easier comprehension and maintenance. Where possible, we have strived to encapsulate functionality in a logical Object Oriented way that encourages extension and reuse.

Prototype Windows and Getting the Active Sheet

The MDI windowing for the web application is designed to allow users to work in a web 'workspace' or 'webtop' which replicates some functionality of an operating system, such as file management, multiple-document interface (MDI), desktop preferences, and window layout.

Use of the MDI/Windowing in your applications is optional as the entire spreadsheet grid, and most of the document management is designed to be opened independently of the MDI/Webtop.

To open a spreadsheet outside of the MDI, you can open the grid.jsp file directly.

One complication of using the MDI architecture is that, because windows are IFRAME objects in the DOM, traversing the frames with JavaScript in order to access spreadsheet and other objects can be tricky.

The web app contains a helper function 'getActiveSheet()' in '/common/js/toolkit.js' -- this method will attempt to return the sheetHandle for the currently active spreadsheet.

If you are having problems with accessing the spreadsheet objects between iframes, please try using getActiveSheet().

Using AJAX to Make REST API Calls

The combination of Object-Oriented JavaScript and AJAX communication with the server-side model (REST P.A.D.S.) is a powerful and extensible combination.

Ajax calls from within a JavaScript object allow us to 'hide the implementation' and separate the business of communication with the server model from the spreadsheet object domain.

An example of this encapsulation can be found in the bookHandle.js file.

/grid/js/bookHandle.js.../**
Inserts the sheettabs into the grid, and or update the ones in place after sheet based operations
**/
updateSheetTabs: function(selectSheetName){
var _this = this;
var url = '/workbook/id/' + this.memeId + '/json/workbook/getsheetnames/';
new Ajax.Request(url , {
method: 'get',
onSuccess: function(transport){
_this.sheetNames = transport.responseText.evalJSON();
_this._updateSheetTabs(selectSheetName);
},
onFailure: function(transport){ 
var response = transport.responseText || "failure"; parent.showError("Unable to to get Sheet names: Check license key." + response); 
}}); 
},


Notice the creation of a temporary '_this' variable -- due to the scoping issues of the AJAX callback 'onSuccess' and 'onFailure' methods, we must initialize a function-scoped pointer for use by the called code.

Accessing Spreadsheet JavaScript Objects

We can see many places throughout the application where spreadsheet objects are accessed. An extensive example is found within the menu files.

In '/grid/contextmenu_top.jsp' we see many buttons which call methods on the currently active spreadsheet.

In this more advanced color coded example, we combine the following aspects of Sheetster development:

  • JavaScript Windowing
  • JavaScript access of the active sheet, workbook, and the current selected cell range
  • REST api call
  • Accessing the current site theme variable in Java (only works in a JSP page!)
  • Accessing Internationalized Strings for Supported Languages (only works in a JSP page!) into a single hyperlink:
javascript:parent.createWindow(getActiveSheet().book.selectedCellRange + ' <%=locale_strings.get("link_1")%>',
'/workbook/id/'+getActiveSheet().book.memeId+'/r3s/namedrange/getcellvalues/'+ getActiveSheet().book.selectedCellRange,'<%=theme%><%=theme%>');


Extending the Built-In Spreadsheet Objects

Example of overriding Sheetster core JavaScript sheetHandle.js Object behavior:

// save original cellHandle.js getSheetNames function
Function.prototype.originalGetSheetNames= Function.prototype.getSheetNames;// override
Function.prototype.getSheetNames = function(){
if(somecondition) // use default behavior
return this.originalGetSheetNames();
else
return "SheetX";
};


Web Scripting

Sheetster has an inline JavaScript evaluation capability which allows for development of web apps completely within the web browser.

Example of Web Scripting code:

 

/*
Demo Linking Grid Events to Google Maps
*/

// get latitude and longitude from cells
var clat = book.getSelectedSheet().getCell("C7");

var clong = book.getSelectedSheet().getCell("D7");

alert(clat.getVal()+ ':' + clong.getVal());

// create google maps url
var mapurl = "http://maps.google.com/maps/api/staticmap?center="+clat.getVal()+","+clong.getVal()+"&zoom=14&size=512x512&maptype=roadmap&sensor=false";

var dialog = parent.createDialogWindow("mapped",mapurl,"extentech");

To use Sheetster Web Scripting, click on the "Create App" button in the Sheet data group. This brings up the Sheetster code editor allowing you to add code to any document in the system, much like embedding Visual Basic for Applications in an Excel file for example.