Go to the directory containing the example code for Chapter 2:
- Double-click the file
spreadsheet.py, the now familiar CherryPy console will open in a text window. - Enter
http://localhost:8080in the address bar of your browser (or click refresh if it is still open on that address). You will now see a simple spreadsheet application:
- You can click on any cell to edit its formula. You should not start a formula with an equal sign: 42, D2+19 and "text" (including the double quote marks) are examples of valid formulas. In fact, any JavaScript expression is valid.
The spreadsheet application served to the end user consists of two major parts, HTML to structure the spreadsheet and some JavaScript to provide interaction. We look at each of these in turn.
The HTML we need for our spreadsheet is nearly identical to the one for the unit converter. The highlighted lines in the following code show the differences. spreadsheet.js contains the definition of the plugin and the final<script> element inserts an 8x10 spreadsheet into the #example div. Converting a<div> element to a fully functional spreadsheet widget is just as simple as converting to the standard button widget!
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/ TR/html4/strict.dtd"> <html> <head> <link rel="stylesheet" href="static/css/redmond/jquery-ui-1.8.1.custom.css" type="text/css" media="screen, projection" /> <script type="text/javascript" src="static/jquery-1.4.2.js" ></script> <script type="text/javascript" src="static/jquery-ui-1.8.1.custom.min.js" ></script> <script type="text/javascript" src="static/jeditable.js" ></script> <script type="text/javascript" src="spreadsheet.js" ></script> </head> <body id="spreadsheet_example"> <div id="example"></div> <script type="text/javascript"> $("#example").sheet({cols:8,rows:10}); </script> </body> </html>
The file spreadsheet.js contains all JavaScript code needed to implement a reusable spreadsheet widget. The spreadsheet is very similar to our unit converter from a jQuery perspective although the actual JavaScript to implement the user interaction is somewhat more involved. Again our plugin is a function that is associated with jQuery's fn attribute as it can be seen in the very first line in the following code, where we define our widget with the name sheet.
Next we merge the default options for the sheet plugin (defined at the end of the file) with the options passed to the function:
jQuery.fn.sheet = function(options){
var opts = $.extend({}, $.fn.sheet.defaults, options);
The next step is to create a table that will represent our spreadsheet. We create this<table> element with a number of associated classes: its very own sheet class to make it easily recognizable as a sheet plugin once created, a ui-helper-reset class that will cause suitable CSS to be applied by jQuery to reset any unwanted default styling added by the browser and finally a ui-widget class that will cause the selected theme to be applied. Then we create the table contents step-by-step by adding the needed HTML to a variable t in incremental steps:
/* create a cols x rows grid */ var t='<table class="sheet ui-helper-reset ui-widget" cellspacing="0">';
The table contains a<thead> element that will be styled as a ui-widget-header. It contains a single row of<th> elements. These<th> elements contain the column label, a capital letter that we construct from the column index with the fromCharCode() method (highlighted):
t=t+'<thead class="ui-widget-header">
<tr class="ui-helper-reset"><th></th>';
for(i=0;i<opts.cols;i=i+1){
t=t+'<th class="ui-helper-reset">' +
String.fromCharCode(65+i)+"</th>";
}
The body of the table consists of a<tbody> element containing a number of rows with<td> elements. The first<td> element of each row contains the row label (a number) and will be styled as a ui-widget-header just like the column labels. The regular cells, that is the ones that will contain our formulas and values, will belong to the class ui-widget-content to style them in an appropriate manner. These cells will also belong to a class cell to make them easy to distinguish when we add additional functionality to them (highlighted).
There is initially no content in such a cell except for a<span> element that will contain the formula and that will be styled as ui-helper-hidden, rendering the formula invisible. The value of the evaluated formula will be stored both as text content in the<td> element (side-by-side with the<span> element) and as a global variable with a name equal to the name of the cell. A global variable in this context is a named attribute of the top-level window object defined by the browser that may be accessed as window[name].
Storing the value of a cell in a global variable as well allows us to use any JavaScript expression as the formula in a cell because we can now refer to the value of any other cell by name. A1+B3*9, for example will be a perfectly valid expression because A1 and B3 will be defined as global variables:
t=t+'</tr></thead><tbody class="ui-widget-content" >';
for(i=0;i<opts.rows;i=i+1){
t=t+'<tr class="ui-helper-reset">
<td class="rowindex ui-helper-reset ui-widget-header">'
+ (i+1)+"</td>";
for(j=0;j<opts.cols;j=j+1){
id=String.fromCharCode(65+j)+(i+1)
t=t+'<td class="cell ui-helper-reset ui-widget-content"
id="'+id+'">
<span class="formula ui-helper-hidden">
</span></td>';
/* create a global variable */
window[id]=0
}
t=t+"</tr>";
}
t=t+"</tbody></table>";
this.append(t);
The HTML for the table we created in the t variable is then inserted into the jQuery selection that we applied the sheet() method with the .append() method of the this object. The this object is available to any function defining a plugin and holds the current jQuery selection.
To edit a cell, we will employ the jEditable plugin. This plugin will take care of the user interaction when the user clicks a cell to edit its content. To do this it needs functions to get and set the contents of a cell.
Note
The jEditable plugin we use here is included in the example code distributed with this chapter. The latest version can be obtained from Mika Tuupola's website: http://www.appelsiini.net/projects/jeditable. It comes with a pretty comprehensive set of documentation. Turning a<td> element into something that changes into an editable textbox when the user clicks on it with a mouse, is as simple as selecting the element and invoking the editable() method. For example, $(".editable").editable("http://www.example.com/save") will render any element with the editable class into an editable textbox once clicked and will send the edited contents to the URL passed as the first parameter to the editable() method. The jEditable plugin comes with a host of options and we will encounter a few of them when we employ the jEditable plugin to do the editing of the spreadsheet cells.
We need to define a function that will be invoked by jEditable for extracting the content of the element. This function will require two arguments:
- The element we are editing (a
<td>element in our example). - The original settings passed to the jEditable plugin. Those settings we ignore for now.
The<td> elements are structured in such a way that the formula itself is stored in a (hidden) span element. The getvalue() function then must get access to this<span> element first before it can obtain the formula.
Therefore, we convert the<td> element first to a jQuery object (highlighted) and then filter the elements it contains to just elements with a class of formula. This amounts to just the<span> element whose text is the formula we are after:
function getvalue(org, settings){
d=$(org)
return d.filter(".formula").text()
}
The corresponding setvalue() function is used by jEditable to store the edited formula again in the<td> element. When called this function is passed two arguments:
- The edited content of the element.
- The original settings passed to the jEditable plugin and its code is quite complicated because storing the formula is not the only thing it has to do. It must also calculate the result of the formula and update any cells that depend on the updated cell.
The cell we are editing (that is the<td> element) is available as the this variable. We stored the cell index as its id attribute so we retrieve that one first (highlighted). The value argument that was passed to the setvalue() function is the edited formula.
As we use JavaScript syntax for these formulas, we can simply call JavaScript's eval() function to calculate the value of the formula. We have to store the result in global variables with the name of the cell as well to make it reusable by other cells. Note that these global variables are just attributes of the window object in the context of the browser so assigning a value to such an attribute is just what we do inside the if … else … clause. If the result of evaluating the formula was undefined in some way (for example, because of an error) we set the result to the string'#undef' to signal that situation to the user:
function setvalue(value, settings) {
/* determine cell index and update global var */
currentcell=$(this).attr( 'id'),
currentresult=eval(value);
if (typeof(currentresult) == 'undefined'){
currentresult='#undef';
window[currentcell]=0;
}else{
window[currentcell]=currentresult;
}
After we have evaluated the formula of the current cell and stored its result we must now recalculate all other cells because they may depend on the contents of the cell we just changed.
We do affect this by selecting all cells in the sheet and applying a function to each of them (highlighted). If we are looking at a different cell than the one just changed (something we determine by comparing their id attributes), we recalculate the formula contained in its<span> element. If the result is different from the previous value stored for a cell we set the change variable to true. We repeat the whole procedure until nothing is changed or we repeated ourselves more often than there are cells in the sheet, at which point we must have a circular reference somewhere, something we indicate to the user by setting the value of the cell to a suitable text. This is certainly not the most efficient method to recalculate a spreadsheet, nor is it a failsafe method to detect all circular references but it works well enough:
/* update all other cells */
var changed;
var depth = 0;
do{
depth++;
changed = false;
$('.sheet').find('.cell').
each(function (index,element){
cell=$(element).attr('id'),
if(currentcell != cell){
span=$(element).
children('span').first();
orig = window[cell];
window[cell]=0;
formula=span.text();
if(formula.length > 0){
result=eval(formula);
if (result != orig) {
changed = true;
}
if(typeof(result)=='undefined'){
result='#undef';
}else{
window[cell]=result;
}
}else{
result = ' ';
}
$(element).empty().
append('<span class="formula ui-helper-hidden replaced">' +
formula+'</span>'+result);
}
});
}while(changed && (depth <opts.cols*opts.rows));
if ( depth >= opts.cols*opts.rows){
currentresult = '#Circular!';
}
return('<span
class="formula ui-helper-hidden">'
+value+'</span>'+currentresult);
}
The purpose of defining functions to set and get a value from a<td> element was to be able to apply the jEditable plugin to every cell. This we do in the final lines of our sheet plugin. We find all children with a cell class (highlighted) and invoke an anonymous function on each of them.
This function first applies the jEditable plugin on the element by invoking the editable() method with a reference to our setvalue() function as the first argument and an options object as the second argument. The type attribute marks this editable element as a text element (and not, for example, a multiline text area element), whereas setting onblur to cancel indicates that on clicking outside the cell when editing will revert the content to its original. The data attribute points to our getvalue() function to indicate to the plugin how to get the value that we want to edit.
The second thing the function does is applies CSS style attributes to each cell. In this case a fixed width and the border-collapse attribute will make sure that the border between cells is just as wide as the border on outlying cells:
/* make every cell editable with the jEditable plugin */
this.find(".cell").each(function (index,element) {
$(this).
editable(setvalue,{type:'text',onblur:'cancel',data:getvalue})
});
$(".cell").css({'width':opts.width,'border-collapse':'collapse'});
return this;
}
spreadsheet.js is completed with the definition of a default options object:
jQuery.fn.sheet.defaults = {
rows : 4,
cols : 4,
width: '100px',
logging: false
}
In the spreadsheet we designed, the user may use any JavaScript expression as the cell formula. That's fine if we want to use operators like addition (+) or multiplication (*), but what if we would like to use, for example, trigonometric functions like sin() or cos()?
This is possible by referring to the methods of the built-in JavaScript object Math (an example would be Math.sin(A1)+Math.cos(B1)) but prefixing every function with Math is awkward. Devise a way to make these functions available without the Math prefix. (Hint: we already saw how to create names in the global namespace by assigning to window[<name>]).
A solution can be found in spreadsheet2.js. Its effects can be tested by running spreadsheet2.py.