Go to the directory containing the example code for Chapter 2:
spreadsheet.py
, the now familiar CherryPy console will open in a text window. http://localhost:8080
in 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: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.
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:
<td>
element in our example).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 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
.
18.117.98.250