In this chapter, you are going to apply what you have learned to developing another application—a general-purpose spreadsheet.
Using forms and cookies, you will create a general-purpose spreadsheet that retains its formulas between sessions.
The spreadsheet has several basic requirements:
In order to implement a spreadsheet with these requirements, you need to do several things before you start writing the script.
First, you need to decide the structure of expressions, how to store expressions, and how to handle changes to information in the spreadsheet.
The obvious choice for saving expressions is using cookies, and Bill Dortch's functions are used to achieve this (these were mentioned when cookies were discussed in Chapter 9). Each function should be stored in a cookie named by the field it is attached to in the spreadsheet.
For instance, if an expression is created for field A6, then a cookie named A6 should be created with the expression stored as a string for the value of the cookie. An expiry date one year in the future is used to ensure that cookies are available between sessions.
Of course, you're limited by the number of cookies that can be stored for a given page and need to keep track of them so you don't accidentally delete important expressions by enabling the user to add too many expressions. This can be accomplished by using one cookie as a counter to keep track of how many expressions have been created so far on the page.
The syntax for expressions is simple: the value of another field can be referenced simply by using the field's name followed by a semicolon. So, the expression A1; * B7; multiplies the value in field A1 by the value in field B7.
Every time the value of a form field is changed, all expressions are reevaluated. Likewise, if the definition of an expression is changed, a new expression is created, or an expression is deleted, all expressions need to be re-evaluated because the change could potentially affect any of the formulas. Listing 12.1 contains the script for the program.
Listing 20.1. A general-purpose spreadsheet.
<HTML>
<HEAD>
<TITLE>Chapter 12</TITLE>
<SCRIPT LANGUAGE="JavaScript">
<!-- HIDE FROM OTHER BROWSERS
//
// cookie Functions - Second Helping (21-Jan-96)
// Written by: Bill Dortch, hIdaho Design <bdortch@netw.com>
// The following functions are released to the public domain.
//
// "Internal" function to return the decoded value of a cookie
//
function getCookieVal (offset) {
var endstr = document.cookie.indexOf (";", offset);
if (endstr == -1)
endstr = document.cookie.length;
return unescape(document.cookie.substring(offset, endstr));
}
//
// Function to return the value of the cookie specified by "name".
//
function GetCookie (name) {
var arg = name + "=";
var alen = arg.length;
var clen = document.cookie.length;
var i = 0;
while (i < clen) {
var j = i + alen;
if (document.cookie.substring(i, j) == arg)
return getCookieVal (j);
i = document.cookie.indexOf(" ", i) + 1;
if (i == 0) break;
}
return null;
}
//
// Function to create or update a cookie.
//
function SetCookie (name, value) {
var argv = SetCookie.arguments;
var argc = SetCookie.arguments.length;
var expires = (argc > 2) ? argv[2] : null;
var path = (argc > 3) ? argv[3] : null;
var domain = (argc > 4) ? argv[4] : null;
var secure = (argc > 5) ? argv[5] : false;
document.cookie = name + "=" + escape (value) +
((expires == null) ? "" : ("; expires=" + expires.toGMTString())) +
((path == null) ? "" : ("; path=" + path)) +
((domain == null) ? "" : ("; domain=" + domain)) +
((secure == true) ? "; secure" : "");
}
// Function to delete a cookie. (Sets expiration date to current date/time)
// name - String object containing the cookie name
//
function DeleteCookie (name) {
var exp = new Date();
exp.setTime (exp.getTime() - 1); // This cookie is history
var cval = GetCookie (name);
document.cookie = name + "=" + cval + "; expires=" + exp.toGMTString();
}
// END OF COOKIE FUNCTIONS
// SEARCH AND REPLACE FUNCTIONS
//
// SET UP ARGUMENTS FOR FUNCTION CALLS
//
var caseSensitive = true;
var notCaseSensitive = false;
var wholeWords = true;
var anySubstring = false;
// SEARCH FOR A TERM IN A TARGET STRING
//
// search(targetString,searchTerm,caseSensitive,wordOrSubstring)
//
// where caseSenstive is a boolean value and wordOrSubstring is a boolean
// value and true means whole words, false means substrings
//
function search(target,term,caseSens,wordOnly) {
var ind = 0;
var next = 0;
if (!caseSens) {
term = term.toLowerCase();
target = target.toLowerCase();
}
while ((ind = target.indexOf(term,next)) >= 0) {
if (wordOnly) {
var before = ind - 1;
var after = ind + term.length;
if (!(space(target.charAt(before)) && space(target.charAt(after)))) {
next = ind + term.length;
continue;
}
}
return true;
}
return false;
}
// SEARCH FOR A TERM IN A TARGET STRING AND REPLACE IT
//
// replace(targetString,oldTerm,newTerm,caseSensitive,wordOrSubstring)
//
// where caseSenstive is a boolean value and wordOrSubstring is a boolean
// value and true means whole words, false means substrings
//
function replace(target,oldTerm,newTerm,caseSens,wordOnly) {
var work = target;
var ind = 0;
var next = 0;
if (!caseSens) {
oldTerm = oldTerm.toLowerCase();
work = target.toLowerCase();
}
while ((ind = work.indexOf(oldTerm,next)) >= 0) {
if (wordOnly) {
var before = ind - 1;
var after = ind + oldTerm.length;
if (!(space(work.charAt(before)) && space(work.charAt(after)))) {
next = ind + oldTerm.length;
continue;
}
}
target = target.substring(0,ind) + newTerm + target.substring(ind+oldTerm.length,target.length);
work = work.substring(0,ind) + newTerm + work.substring(ind+oldTerm.length,work.length);
next = ind + newTerm.length;
if (next >= work.length) { break; }
}
return target;
}
// CHECK IF A CHARACTER IS A WORD BREAK AND RETURN A BOOLEAN VALUE
//
function space(check) {
var space = " .,/<>?!`';:@#$%^&*()=-|[]{}" + '"' + "\\\n\t";
for (var i = 0; i < space.length; i++)
if (check == space.charAt(i)) { return true; }
if (check == "") { return true; }
if (check == null) { return true; }
return false;
}
// END OF SEARCH AND REPLACE FUNCTIONS
// MAIN BODY OF SCRIPT
//
// Set up global variables
//
var width = 8;
var height = 12;
var letters = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
// Set up Expiry Date for cookies
//
var expiryDate = new Date();
expiryDate.setTime(expiryDate.getTime() + 365*24*60*60*1000);
var deleteExpiry = new Date();
deleteExpiry.setTime(deleteExpiry.getTime() - 1);
// Function to calculate the spreadsheet
//
function calculate(form) {
var expField = "";
var expression = "";
// Check each field for an expression and if there is one, evaluate it
for (var x = 0; x < width; x ++) {
for (var y = 1; y <= height; y ++) {
expField = letters.charAt(x) + y;
if ((expression = GetCookie(expField)) != null)
form[expField.value = evaluateExp(form,expression);
}
}
}
// Function to evaluate an expression
//
function evaluateExp(form,expression) {
var column = "";
var index = 0;
var nextExpField;
var nextExpression = "";
var nextResult = "";
// Scan the expression for field names
for (var x = 0; x < width; x ++) {
column = letters.charAt(x);
index = 0;
index = expression.indexOf(column,index);
// If we find a field name, evaluate it
while(index >= 0) {
// Check if the field has an expression associated with it
nextExpField = expression.substring(index,expression.indexOf(";",index));
// If there is an expression, evaluate--otherwise grab the value of the field
if ((nextExpression = GetCookie(nextExpField)) != null) {
nextResult = evaluateExp(form,nextExpression);
} else {
nextResult = form[nextExpField.value;
if ((nextResult == "") || (nextResult == null))
nextResult = "0";
}
// Replace the field name with the result
nextExpField = nextExpField + ";";
nextResult = "(" + nextResult + ")";
expression = replace(expression,nextExpField,nextResult,notCaseSensitive,anySubstring);
// Check if we have reached the end of the expression
index = index + nextResult.length;
if (index >= expression.length - 1) { break; }
// If not, search for another field name
index = expression.indexOf(column,index);
}
}
// Evaluate the expression
with (Math) {
var result = eval(expression);
}
// Return the result
return result;
}
// Function to save an expression
//
function saveExp(form) {
var numExp = GetCookie("numExpressions");
// Check the number of saved expressions
if (numExp == "19") {
alert("Too many expressions. Delete One first");
} else {
// If there is room, save the expression and update the number of expressions
SetCookie(form.expField.value,form.expression.value,expiryDate);
numExp = parseInt(numExp) + 1;
SetCookie("numExpressions",numExp,expiryDate);
// Recalculate the spreadsheet
calculate(document.spreadsheet);
alert("Expession for field " + form.expField.value + " is saved.");
}
}
// Function to delete an expression
//
function deleteExp(form) {
var numExp = GetCookie("numExpressions");
var expression = GetCookie(form.expField.value);
// Check if there is an expression to delete for the field
if (expression != null) {
// There is, so set the expiry date
SetCookie(form.expField.value,"",deleteExpiry);
numExp = parseInt(numExp) - 1;
SetCookie("numExpressions",numExp,expiryDate);
// Update the field and recalculate the spreadsheet
document.spreadsheet[form.expField.value].value = "";
calculate(document.spreadsheet);
alert("Expession for field " + form.expField.value + " is removed.");
}
}
// Function to build form
//
function buildForm() {
var numExp = 0;
// Check if this is a new spreadsheet. If it is, set the number of expressions to zero
if ((numExp = GetCookie("numExpressions")) == null) {
SetCookie("numExpressions",0,expiryDate);
}
// Build row header
document.write("<TR><TD></TD>");
for (var x = 0; x < width; x++) {
document.write("<TD><DIV ALIGN=CENTER>" + letters.charAt(x) + "</DIV></TD>");
}
document.write("</TR>");
// Build each field -- each is the same, with a different name
for (var y = 1; y <= height; y++) {
document.write("<TR><TD>" + y + "</TD>");
for (var x = 0; x < width; x++) {
document.write('<TD><INPUT TYPE=text SIZE=10 NAME="' + letters.charAt(x) + y + '" onChange="calculate(this.form);"></TD>');
//SetCookie(letters.charAt(x) + y,"",deleteExpiry);
}
document.write("</TR>");
}
}
// STOP HIDING -->
</SCRIPT>
</HEAD>
<BODY BGCOLOR="iceblue">
<CENTER>
<FORM METHOD=POST NAME="spreadsheet">
<TABLE BORDER=0>
<SCRIPT LANGUAGE="JavaScript">
<!-- HIDE FROM OTHER BROWSERS
buildForm();
// STOP HIDING -->
</SCRIPT>
</TABLE>
</FORM>
<HR>
<FORM METHOD=POST>
<TABLE BORDER=1>
<TR>
<TD><DIV ALIGN=CENTER>Field Name</DIV></TD>
<TD><DIV ALIGN=CENTER>Expression</DIV></TD>
</TR>
<TR>
<TD><DIV ALIGN=CENTER><INPUT TYPE=text SIZE=10 NAME="expField"
onChange="var exp = GetCookie(this.value); this.form.expression.value = (exp == null) ? '' : exp;"></DIV></TD>
<TD><DIV ALIGN=CENTER><INPUT TYPE=text SIZE=50 NAME="expression"></DIV></TD>
<TD><DIV ALIGN=CENTER><INPUT TYPE=button VALUE="Apply" onClick="saveExp(this.form);"></DIV></TD>
<TD><DIV ALIGN=CENTER><INPUT TYPE=button VALUE="Delete" onClick="deleteExp(this.form);"></DIV></TD>
</TR>
</TABLE>
</FORM>
</CENTER>
</BODY>
</HTML>
The results of this script look like those shown in Figures 20.1 and 20.2.
Figure 20.1. Building complex spreadsheets using mathematical expressions.
Figure 20.2. The small form at the bottom can be used to create, update, and delete expressions.
The script uses functions to create the spreadsheet application. In addition, Bill Dortch's cookie functions and the search and replace functions provided for reference purposes in Chapter 17 have been included.
Using these, the calculate(), evaluateExp(), saveExp(), deleteExp(), and buildForm() functions do everything needed.
Before looking at the functions, the body of the HTML document needs to be looked at to understand the different interface components accessible to the user.
The document consists of two forms: the spreadsheet and the expression update form. The spreadsheet form is built dynamically by a small script that calls buildForm(). An HTML table is used to create a nicely formatted spreadsheet layout.
The second form also is in a table and is used to create, update, or delete expressions. It contains two text entry fields—one for the field name and one for the expression—and two buttons, Apply and Delete, which invoke the saveExp() and deleteExp() functions respectively (shown in Figure 20.2).
In addition, when the value of the expField field changes, the script checks if there is a stored cookie for that field, and if there is, displays the expression in the expression field. Otherwise, you store an empty string in the expression field.
In addition to the functions, several global variables are used to keep track of information throughout the script:
var width = 8; var height = 12; var letters = " ABCDEFGHIJKLMNOPQRSTUVWXYZ "; // Set up Expiry Date for cookies // var expiryDate = new Date(); expiryDate.setTime(expiryDate.getTime() + 365*24*60*60*1000); var deleteExpiry = new Date(); deleteExpiry.setTime(deleteExpiry.getTime() - 1);
The width and height variables define the size of the spreadsheet. Eight columns and 12 rows fit well on an 800x600 pixel display. Only notebook users with 640x480 displays may need a smaller spreadsheet.
The letters string contains the letters of the alphabet that are used to name the columns of the form. Each letter is extracted by its index (the column number minus one) when it is needed. You should include the whole alphabet because this gives you the flexibility to increase the number of columns in the form simply by increasing the value of width.
expiryDate and deleteExpiry are the Date objects used for setting and deleting the cookies. expiryDate is set to one year from the current date, and deleteExpiry is set to one millisecond before the current time.
The calculate() function is probably the main function of the script. This function is called every time you want to reevaluate the form when a value changes or an expression is added, updated, or deleted. The function takes one argument: the form object for the spreadsheet form.
The structure of the function is quite simple. It uses two nested for loops: one for each column using variable x and one for each row using variable y. For each combination of row and column you build the field name with letters.charAt(x) + y. Notice that the first for statement loops from zero to one less than the number of columns, which means x is the index of the appropriate letter in the letters string.
You then check if there is an expression stored in the cookie with the name of the field. The result of the GetCookie() call is stored in the variable expression and compared null. If it is not null, you have an expression, and it is evaluated by calling evaluateExp(). evaluateExp() returns the evaluated expression, and this can be directly stored in the appropriate field in the form.
This is, perhaps, the most heavily used function in the script (with the exception of the cookie functions).
Given two arguments—the form object for the spreadsheet and the expression to be evaluated—the evaluateExp() function returns the value of the expression based on the current content of the spreadsheet.
You start with a for loop which iterates through each of the letters that name the columns. Inside that loop, a check is made to see whether there is an occurrence of the letter in the expression. If there is, it means that there is a reference to a field in that column that you need to handle.
Next, a check for an occurrence of the letter is made by using indexOf() and storing the results in index.
The while loop executes only when a field for the current column has been found—that is, index must be greater than zero.
Inside the loop, you get the field name by using substring() from index to the first occurrence of a semicolon (;), which marks the end of the field name. Given this value, you check whether there is an expression for that field and store the expression in nextExpression. If there is an expression, you call evaluateExp() recursively to get the value for that expression and store the result in nextResult.
If there's no expression for the field, you get the value of nextResult directly from the form. If this value is a null value or an empty string, you change nextResult to zero.
Once you have a value for nextResult, you can replace the occurrence of the field in the expression with the value of nextResult using the replace() function. You also should replace the semicolon after the field name and add parentheses to nextResult so that when the expression is evaluated, the value of nextResult is correctly evaluated and not affected by the rules of operator precedence.
Once the expression is evaluated, you check whether you have reached the end of the expression by updating index to the character after the newly replaced value and compare this to the index of the last character in the expression.
If the end of the string hasn't been reached, a check for another occurrence of the current letter is made with indexOf() and you are return to the condition at the top of the while loop.
Once the for loop is complete, you're ready to evaluate the expression. with(Math) is used so that any methods from the Math object that occurred in the expression don't require the presence of the Math prefix.
Finally, the expression is evaluated using the eval() statement.
The saveExp() function saves an expression in a cookie when the user clicks the Apply button in the lower form, which is used to create and manipulate expressions. The function takes the form object for the expression as an argument.
The function starts by checking the number of expressions that have already been saved. If the number is already 19, the limit, then the user is informed that she needs to delete another expression if she wants to save this one.
If there is room to save the expression, then it is saved, getting the name of the cookie directly from the appropriate field in the form and getting the expression in the same way. The number of expressions is updated by one and the cookie containing this value is updated (notice the use of parseInt() to change the string returned by GetCookie() into an integer).
Finally, the spreadsheet is recalculated by calling calculate(), and then informs the user that the expression has been saved.
Just as saveExp() saved an expression, deleteExp() deletes the expression indicated by a field name in the form. Again, it takes the form object as an expression and is invoked when the user clicks on the Delete button.
It start by checking whether there is an expression stored in that field. If there is, it saves a new cookie with the same name but uses deleteExpiry as the expiry date. It also decreases the number of expressions by one and updates the cookie containing the number.
Once the cookie has been deleted, the spreadsheet is recalculated and the user is informed that the task is done in the same way as the saveExp() function.
The buildForm() function is the last function in Listing 12.1. It is called from inside the body of the HTML file and builds the HTML of the spreadsheet form, which is displayed in a table.
Using JavaScript to dynamically build the table is the best approach, because each field is repetitive and because you want to be able to build the spreadsheet table to match the width and height variables if they get changed.
You start determining whether this is a new spreadsheet by checking if there is any value stored in the cookie holding the number of expressions. If there isn't a value, save a zero value there to initialize the spreadsheet.
Next, you build the header row for the table that contains a blank field at the start, and then a field for each column with the appropriate letter centered in the field. This is done with a for loop that extracts each letter from the letters string.
Once the table header is output, two nested for loops are used to build each row of the table with the number in the first field and then blank text input fields in the rest of the table cells in the row.
The names of the text entry fields are created using letters.charAt(x) + y.
The basic script works, but it has several limitations, including the following:
In addition to these limitations, there are several features you could add to the spreadsheet to make it more useful:
The main efficiency bottleneck is in the calculate() function. In this function, two nested for loops are used to iterate through all 96 fields in the form. For each, GetCookie() is called to check whether the field has an expression, and if it does, evaluateExp() is called.
This is inefficient, however. You end up calling GetCookie() for each empty field in the form, which in the example, means at least 77 unneeded calls to GetCookie() each time a value in the form is changed.
If you have a way to know which fields have expressions without checking each field in the spreadsheet, you could avoid all these unnecessary calls to GetCookie().
To do this, you can take one more of the cookies and use it to store a list of fields that contain expressions. For instance, a semicolon delimited list such as A1;B11;C10; could be used.
In order to do this, you need to make changes to calculate(), saveExp(), and deleteExp().
In the calculate() function, make a fundamental change to the logic of the function:
function calculate(form) {
var index = 0;
var next = 0;
var expField = "";
var expression = "";
var fieldList = GetCookie("fieldList");
if (fieldList != null) {
while (index != fieldList.length) {
next = fieldList.indexOf(";",index);
expField = fieldList.substring(index,next);
expression = GetCookie(expField);
form[expField].value = evaluateExp(form,expression);
index = next + 1;
}
}
}
You get the field list from the fieldList cookie. If it's null, there are no expressions and no evaluation is needed. Otherwise, a while loop is entered, continuing until the index reaches the end of the fieldList string.
Inside the while loop, you scan for the next semicolon using indexOf() and extract the substring from index to the character before the semicolon. This value is the field name of an expression which you then get from the cookie, evaluate, and store in form[expField].value.
index is then incremented to the character after the semicolon.
The saveExp() and deleteExp() functions both have similar changes. In the saveExp() function, you need to add a few lines to handle the extra cookie containing the field list, as well as change the maximum number of cookies to 18 to make room for the fieldList cookie.
Updating the fieldList cookie is handled by first checking if there is a list already. If not, the list is simply created with the current field name. If there is a list, the field name is removed from the list by replacing it with an empty string and then add it back in. In this way, you don't get double occurrences of any field name in the list.
function saveExp(form) {
var expField = form.expField.value;
var fieldList = GetCookie("fieldList");
var numExp = GetCookie("numExpressions");
// Check the number of saved expressions
if (numExp == "18") {
alert("Too many expressions. Delete One first");
} else {
// If there is room, save the expression and update the number of expressions
SetCookie(form.expField.value,form.expression.value,expiryDate);
numExp = parseInt(numExp) + 1;
SetCookie("numExpressions",numExp,expiryDate);
expField += ";"
if (fieldList == null) {
fieldList = expField;
} else {
fieldList = replace(fieldList,expField,"",notCaseSensitive,anySubstring);
fieldList += expField;
}
SetCookie("fieldList",fieldList,expiryDate);
// Recalculate the spreadsheet
calculate(document.spreadsheet);
alert("Expession for field " + form.expField.value + " is saved.");
}
}
The deleteExp() function works in a similar manner:
function deleteExp(form) {
var fieldList = GetCookie("fieldList");
var expField = form.expField.value;
var numExp = GetCookie("numExpressions");
var expression = GetCookie(form.expField.value);
// Check if there is an expression to delete for the field
if (expression != null) {
// There is, so set the expiry date
SetCookie(form.expField.value,"",deleteExpiry);
numExp = parseInt(numExp) - 1;
SetCookie("numExpressions",numExp,expiryDate);
expField += ";";
fieldList = replace(fieldList,expField,"",notCaseSensitive,anySubstring);
SetCookie("fieldList",fieldList,expiryDate);
// Update the field and recalculate the spreadsheet
document.spreadsheet[form.expField.value].value = "";
calculate(document.spreadsheet);
alert("Expession for field " + form.expField.value + " is removed.");
}
}
To delete the entry from the field list and update the cookie, the replace() function is used to delete the name and replace it with an empty string before updating the fieldList cookie.
In order to save title fields, treat them as expressions so they get saved as cookies. Have the first character of the title expression be a double-quote character.
This enables you to simply update the evaluateExp() function to return the rest of the string when it encounters the following syntax:
function evaluateExp(form,expression) {
var column = "";
var index = 0;
var nextExpField;
var nextExpression = "";
var nextResult = "";
if (expression.charAt(0) == '"') {
return(expression.substring(1,expression.length));
}
// Scan the expression for field names
for (var x = 0; x < width; x ++) {
column = letters.charAt(x);
index = 0;
index = expression.indexOf(column,index);
// If we find a field name, evaluate it
while(index >= 0) {
// Check if the field has an expression associated with it
nextExpField = expression.substring(index,expression.indexOf(";",index));
// If there is an expression, evaluate.Otherwise grab the value of the field
if ((nextExpression = GetCookie(nextExpField)) != null) {
nextResult = evaluateExp(form,nextExpression);
} else {
nextResult = form[nextExpField].value;
if ((nextResult == "") || (nextResult == null))
nextResult = "0";
}
// Replace the field name with the result
nextExpField = nextExpField + ";";
nextResult = "(" + nextResult + ")";
expression = replace(expression,nextExpField,nextResult,notCaseSensitive,anySubstring);
// Check if we have reached the end of the expression
index = index + nextResult.length;
if (index >= expression.length - 1) { break; }
// If not, search for another field name
index = expression.indexOf(column,index);
}
}
// Evaluate the expression
with (Math) {
var result = eval(expression);
}
// Return the result
return result;
}
Only one step has been added to the evaluateExp() function. Before evaluating the expression as a mathematical expression, the first character is checked for a double quotation mark. If one is found, the rest of the expression string is returned.
By way of example, some very basic error checking is performed.
There are two places you need to check for errors. First, you need to make sure that the user has entered a legitimate expression in the expression field.
Here, if the user has entered a mathematical expression, you check basic syntax—that is, that the field names use capital letters and end with a semicolon and also that there isn't a circular expression.
To make the script easier to read, do this in a separate function and call the function from the main if statement in saveExp():
if (numExp == "18") {
alert("Too many expressions. Delete One first");
} else {
if (!checkExp(form.expression.value,expField + ";")) { return }
// If there is room, save the expression and update the number of expressions
SetCookie(form.expField.value,form.expression.value,expiryDate);
numExp = parseInt(numExp) + 1;
SetCookie("numExpressions",numExp,expiryDate);
expField += ";"
if (fieldList == null) {
fieldList = expField;
} else {
fieldList = replace(fieldList,expField,"",notCaseSensitive,anySubstring);
fieldList += expField;
}
SetCookie("fieldList",fieldList,expiryDate);
// Recalculate the spreadsheet
calculate(document.spreadsheet);
alert("Expession for field " + form.expField.value + " is saved.");
}
The line
if (!checkExp(form.expression.value,expField + ";")) { return }
calls checkExp(), which checks the expression in question and, if it finds an error, alerts the user and returns false. Otherwise, it returns true. By checking whether you get a false value from checkExp(), you are able to exit out of the function before saving the new expression.
The main work of error checking takes place in the function checkExp():
function checkExp(expression,expField) {
var index =0;
var next = 0;
var checkNum = 0;
var otherExpField = ""
var otherExp = "";
var lowerColumn = ""
if (expression.charAt(0) == '"') { return true; }
for (var x = 0; x < width; x++) {
index =0;
column = letters.charAt(x);
lowerColumn = column.toLowerCase();
// Check for field in this column
index = expression.indexOf(column,0);
if (index < 0) {
index = expression.indexOf(lowerColumn,0);
}
// If we have a reference to this column, check the syntax
while (index >= 0) {
next = index + 1;
// Check if letter is followed by a number, if not assume it is a Math method
checkNum = parseInt(expression.charAt(next));
if ((checkNum == 0) && (expression.charAt(next) != "0") && (expression.charAt(index) == lowerColumn)) {
if (next + 1 == expression.length) { break; }
index = expression.indexOf(column,next+1);
if (index < 0) {
index = expression.indexOf(lowerColumn,next+1);
}
continue;
}
// It is not a Math method so check that the letter was uppercase
if (expression.charAt(index) == lowerColumn) {
alert("Field names must use uppercase letters.");
return false;
}
// The letter was uppercase, so check that we have only numbers followed by a semicolon
while(expression.charAt(++next) != ";") {
checkNum = parseInt(expression.charAt(next));
if ((checkNum == 0) && (expression.charAt(next) != "0")) {
alert("Field name format is incorrect (should be like A12; or B9;).");
return false;
}
if (next == expression.length - 1) {
alert("Field name format is incorrect (should be like A12; or B9;).");
return false;
}
}
otherExpField = expression.substring(index,next);
// Check for a circular expression
otherExp = GetCookie(otherExpField);
if (otherExp != null) {
if (search(otherExp,expField,caseSensitive,anySubstring)) {
alert("You have created a circular expression with field " + otherExpField + ".");
return false;
}
}
if (next + 1 == expression.length) { break; }
index = expression.indexOf(column,next+1);
if (index < 0) {
index = expression.indexOf(lowerColumn,next+1);
}
}
}
return true;
}
This function is divided into several steps. It starts by checking whether you have a string expression (which starts with a double quotation mark). If you do, it returns true.
If you don't have a string expression, then you need to check the mathematical expression according to the criteria previously outlined. To do this, a for loop is used, which loops through each of the letters that are column names and performs a series of checks based on that column.
You first assign the column name to the variable column. The lowercase version of the same letter also is assigned to lowerColumn because you also will need to deal with lowercase versions of the same letter.
You then check for an occurrence of either the uppercase or lowercase letter, using indexOf(), and assign the index to the variable index. You then enter a while loop that performs the main checking. The condition of the while loop means it will repeat as long as it continues to find instances of the letter.
The first check in the while loop is to see if the character immediately following the letter is a number. If it isn't a number—which would make it the start of a field reference—assume it refers to a method or property from the Math object.
Note:
This is not a perfect assumption. To correctly check, you would need to assure that whatever character string you find is actually part of the Math object. This could be done using the typeof operator.
This check is performed by passing the character through parseInt() and then checking if the result is zero. If it is, a check is made to see if the actual character is zero and make sure that the letter is lowercase (because all the Math methods start with lowercase letters).
Having passed all these conditions, an assumption is made that this is a Math method, and you scan forward for another occurrence of the letter then return to the top of the loop with the continue statement.
If you get by the first if statement, you know you have a letter followed by a number, which means the user is trying to reference a field name. The first thing you do is check if the user is using an uppercase letter; if not, the user is alerted and a false value is returned.
Next, you move forward through the expression, checking each character. If you find a non-numeric character before you reach a semicolon, then you know that you have an invalid reference, so you alert the user and return a false value. Likewise, if you reach the end of the expression without hitting a semicolon, you also know you have an incorrect form, and do the same thing.
The last check performed is to look for a circular expression. The field name that you are currently looking at is extracted and used to get any existing expression for that field. If the field has an expression, it is searched using search() to see if the expression refers back to the field to which you are trying to add an expression. If it does, you have a circular expression, and the user is informed and a false value is returned again.
For instance, if the user is trying to define the expression A1-B1 in field A1, this would create a circular expression; so the user needs to be informed, and the expression should not be saved.
Finally, a check is made to see if the end of the expression has been reached. If not, a search is made for another occurrence of the letter, the index is stored in index, and you're returned to the top of the while loop.
The other place that error checking needs to be performed is in the evaluateExp() function. Here, you need to make sure that the values of fields being used in expressions are numeric. This is done in the main if statement in the while loop:
if ((nextExpression = GetCookie(nextExpField)) != null) {
nextResult = evaluateExp(form,nextExpression);
if ("" + nextResult == "error") {
return "error";
}
} else {
nextResult = form[nextExpField].value;
if ((nextResult == "") || (nextResult == null)) {
nextResult = "0";
} else {
// Check if this is a numeric expression
var checkNum = parseInt(nextResult);
if ((checkNum == 0) && (nextResult.charAt(0) != "0")) {
return "error";
}
}
}
When you get back a value of calling evaluateExp(), a check is made to see that the result is not "error". If it is "error", "error" is simply returned back up the chain of function calls.
If you're getting a value directly from a form field and the field isn't empty, a check is performed to see whether the value is a number by applying parseInt() to the value and checking the result. If you don't have a numeric expression, "error" is returned.
In this chapter you have seen a complete, workable spreadsheet application put together using only the commands and JavaScript objects learned in this book. This demonstrates the power of JavaScript as an easy-to-use and flexible scripting language.
To help you put together the program, the complete source code of the programs included in the Listing 20.2, including all the changes you just made.
Listing 20.2. The final spreadsheet script
<HTML>
<HEAD>
<TITLE>Chapter 12</TITLE>
<SCRIPT LANGUAGE="JavaScript">
<!-- HIDE FROM OTHER BROWSERS
//
// cookie Functions - Second Helping (21-Jan-96)
// Written by: Bill Dortch, hIdaho Design <bdortch@netw.com>
// The following functions are released to the public domain.
//
// "Internal" function to return the decoded value of a cookie
//
function getCookieVal (offset) {
var endstr = document.cookie.indexOf (";", offset);
if (endstr == -1)
endstr = document.cookie.length;
return unescape(document.cookie.substring(offset, endstr));
}
//
// Function to return the value of the cookie specified by "name".
//
function GetCookie (name) {
var arg = name + "=";
var alen = arg.length;
var clen = document.cookie.length;
var i = 0;
while (i < clen) {
var j = i + alen;
if (document.cookie.substring(i, j) == arg)
return getCookieVal (j);
i = document.cookie.indexOf(" ", i) + 1;
if (i == 0) break;
}
return null;
}
//
// Function to create or update a cookie.
//
function SetCookie (name, value) {
var argv = SetCookie.arguments;
var argc = SetCookie.arguments.length;
var expires = (argc > 2) ? argv[2] : null;
var path = (argc > 3) ? argv[3] : null;
var domain = (argc > 4) ? argv[4] : null;
var secure = (argc > 5) ? argv[5] : false;
document.cookie = name + "=" + escape (value) +
((expires == null) ? "" : ("; expires=" + expires.toGMTString())) +
((path == null) ? "" : ("; path=" + path)) +
((domain == null) ? "" : ("; domain=" + domain)) +
((secure == true) ? "; secure" : "");
}
// Function to delete a cookie. (Sets expiration date to current date/time)
// name - String object containing the cookie name
//
function DeleteCookie (name) {
var exp = new Date();
exp.setTime (exp.getTime() - 1); // This cookie is history
var cval = GetCookie (name);
document.cookie = name + "=" + cval + "; expires=" + exp.toGMTString();
}
// END OF COOKIE FUNCTIONS
// SEARCH AND REPLACE FUNCTIONS
//
// SET UP ARGUMENTS FOR FUNCTION CALLS
//
var caseSensitive = true;
var notCaseSensitive = false;
var wholeWords = true;
var anySubstring = false;
// SEARCH FOR A TERM IN A TARGET STRING
//
// search(targetString,searchTerm,caseSensitive,wordOrSubstring)
//
// where caseSenstive is a boolean value and wordOrSubstring is a boolean
// value and true means whole words, false means substrings
//
function search(target,term,caseSens,wordOnly) {
var ind = 0;
var next = 0;
if (!caseSens) {
term = term.toLowerCase();
target = target.toLowerCase();
}
while ((ind = target.indexOf(term,next)) >= 0) {
if (wordOnly) {
var before = ind - 1;
var after = ind + term.length;
if (!(space(target.charAt(before)) && space(target.charAt(after)))) {
next = ind + term.length;
continue;
}
}
return true;
}
return false;
}
// SEARCH FOR A TERM IN A TARGET STRING AND REPLACE IT
//
// replace(targetString,oldTerm,newTerm,caseSensitive,wordOrSubstring)
//
// where caseSenstive is a boolean value and wordOrSubstring is a boolean
// value and true means whole words, false means substrings
//
function replace(target,oldTerm,newTerm,caseSens,wordOnly) {
var work = target;
var ind = 0;
var next = 0;
if (!caseSens) {
oldTerm = oldTerm.toLowerCase();
work = target.toLowerCase();
}
while ((ind = work.indexOf(oldTerm,next)) >= 0) {
if (wordOnly) {
var before = ind - 1;
var after = ind + oldTerm.length;
if (!(space(work.charAt(before)) && space(work.charAt(after)))) {
next = ind + oldTerm.length;
continue;
}
}
target = target.substring(0,ind) + newTerm + target.substring(ind+oldTerm.length,target.length);
work = work.substring(0,ind) + newTerm + work.substring(ind+oldTerm.length,work.length);
next = ind + newTerm.length;
if (next >= work.length) { break; }
}
return target;
}
// CHECK IF A CHARACTER IS A WORD BREAK AND RETURN A BOOLEAN VALUE
//
function space(check) {
var space = " .,/<>?!`';:@#$%^&*()=-|[]{}" + '"' + "\\\n\t";
for (var i = 0; i < space.length; i++)
if (check == space.charAt(i)) { return true; }
if (check == "") { return true; }
if (check == null) { return true; }
return false;
}
// END OF SEARCH AND REPLACE FUNCTIONS
// MAIN BODY OF SCRIPT
//
// Set up global variables
//
var width = 8;
var height = 12;
var letters = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
// Set up Expiry Date for cookies
//
var expiryDate = new Date();
expiryDate.setTime(expiryDate.getTime() + 365*24*60*60*1000);
var deleteExpiry = new Date();
deleteExpiry.setTime(deleteExpiry.getTime() - 1);
// Function to calculate the spreadsheet
//
function calculate(form) {
var index = 0;
var next = 0;
var expField = "";
var expression = "";
var fieldList = GetCookie("fieldList");
if (fieldList != null) {
while (index != fieldList.length) {
next = fieldList.indexOf(";",index);
expField = fieldList.substring(index,next);
expression = GetCookie(expField);
form[expField].value = evaluateExp(form,expression);
index = next + 1;
}
}
}
// Function to evaluate an expression
//
function evaluateExp(form,expression) {
var column = "";
var index = 0;
var nextExpField;
var nextExpression = "";
var nextResult = "";
if (expression.charAt(0) == '"') {
return(expression.substring(1,expression.length));
}
// Scan the expression for field names
for (var x = 0; x < width; x ++) {
column = letters.charAt(x);
index = 0;
index = expression.indexOf(column,index);
// If we find a field name, evaluate it
while(index >= 0) {
// Check if the field has an expression associated with it
nextExpField = expression.substring(index,expression.indexOf(";",index));
// If there is an expression, evaluate--otherwise grab the value of the field
if ((nextExpression = GetCookie(nextExpField)) != null) {
nextResult = evaluateExp(form,nextExpression);
} else {
nextResult = form[nextExpField].value;
if ((nextResult == "") || (nextResult == null))
nextResult = "0";
}
// Replace the field name with the result
nextExpField = nextExpField + ";";
nextResult = "(" + nextResult + ")";
expression = replace(expression,nextExpField,nextResult,notCaseSensitive,anySubstring);
// Check if we have reached the end of the expression
index = index + nextResult.length;
if (index >= expression.length - 1) { break; }
// If not, search for another field name
index = expression.indexOf(column,index);
}
}
// Evaluate the expression
with (Math) {
var result = eval(expression);
}
// Return the result
return result;
}
// Function to save an expression
//
function saveExp(form) {
var expField = form.expField.value;
var fieldList = GetCookie("fieldList");
var numExp = GetCookie("numExpressions");
// Check the number of saved expressions
if (numExp == "18") {
alert("Too many expressions. Delete One first");
} else {
if (!checkExp(form.expression.value,expField + ";")) { return }
// If there is room, save the expression and update the number of expressions
SetCookie(form.expField.value,form.expression.value,expiryDate);
numExp = parseInt(numExp) + 1;
SetCookie("numExpressions",numExp,expiryDate);
expField += ";"
if (fieldList == null) {
fieldList = expField;
} else {
fieldList = replace(fieldList,expField,"",notCaseSensitive,anySubstring);
fieldList += expField;
}
SetCookie("fieldList",fieldList,expiryDate);
// Recalculate the spreadsheet
calculate(document.spreadsheet);
alert("Expession for field " + form.expField.value + " is saved.");
}
}
// Function to delete an expression
//
function deleteExp(form) {
var fieldList = GetCookie("fieldList");
var expField = form.expField.value;
var numExp = GetCookie("numExpressions");
var expression = GetCookie(form.expField.value);
// Check if there is an expression to delete for the field
if (expression != null) {
// There is, so set the expiry date
SetCookie(form.expField.value,"",deleteExpiry);
numExp = parseInt(numExp) - 1;
SetCookie("numExpressions",numExp,expiryDate);
expField += ";";
fieldList = replace(fieldList,expField,"",notCaseSensitive,anySubstring);
SetCookie("fieldList",fieldList,expiryDate);
// Update the field and recalculate the spreadsheet
document.spreadsheet[form.expField.value].value = "";
calculate(document.spreadsheet);
alert("Expession for field " + form.expField.value + " is removed.");
}
}
// Function to build form
//
function buildForm() {
var numExp = 0;
// Check if this is a new spreadsheet. If it is, set the number of expressions to zero
if ((numExp = GetCookie("numExpressions")) == null) {
SetCookie("numExpressions",0,expiryDate);
}
// Build row header
document.write("<TR><TD></TD>");
for (var x = 0; x < width; x++) {
document.write("<TD><DIV ALIGN=CENTER>" + letters.charAt(x) + "</DIV></TD>");
}
document.write("</TR>");
// Build each field -- each is the same, with a different name
for (var y = 1; y <= height; y++) {
document.write("<TR><TD>" + y + "</TD>");
for (var x = 0; x < width; x++) {
document.write('<TD><INPUT TYPE=text SIZE=10 NAME="' + letters.charAt(x) + y + '" onChange="calculate(this.form);"></TD>');
}
document.write("</TR>");
}
}
// Function check expressions
//
function checkExp(expression,expField) {
var index =0;
var next = 0;
var checkNum = 0;
var otherExpField = ""
var otherExp = "";
var lowerColumn = ""
if (expression.charAt(0) == '"') { return true; }
for (var x = 0; x < width; x++) {
index =0;
column = letters.charAt(x);
lowerColumn = column.toLowerCase();
// Check for field in this column
index = expression.indexOf(column,0);
if (index < 0) {
index = expression.indexOf(lowerColumn,0);
}
// If we have a reference to this column, check the syntax
while (index >= 0) {
next = index + 1;
// Check if letter is followed by a number, if not assume it is a Math method
checkNum = parseInt(expression.charAt(next));
if ((checkNum == 0) && (expression.charAt(next) != "0") && (expression.charAt(index) == lowerColumn)) {
if (next + 1 == expression.length) { break; }
index = expression.indexOf(column,next+1);
if (index < 0) {
index = expression.indexOf(lowerColumn,next+1);
}
continue;
}
// It is not a Math method so check that the letter was uppercase
if (expression.charAt(index) == lowerColumn) {
alert("Field names must use uppercase letters.");
return false;
}
// The letter was uppercase, so check that we have only numbers followed by a semicolon
while(expression.charAt(++next) != ";") {
checkNum = parseInt(expression.charAt(next));
if ((checkNum == 0) && (expression.charAt(next) != "0")) {
alert("Field name format is incorrect (should be like A12; or B9;).");
return false;
}
if (next == expression.length - 1) {
alert("Field name format is incorrect (should be like A12; or B9;).");
return false;
}
}
otherExpField = expression.substring(index,next);
// Check for a circular expression
otherExp = GetCookie(otherExpField);
if (otherExp != null) {
if (search(otherExp,expField,caseSensitive,anySubstring)) {
alert("You have created a circular expression with field " + otherExpField + ".");
return false;
}
}
if (next + 1 == expression.length) { break; }
index = expression.indexOf(column,next+1);
if (index < 0) {
index = expression.indexOf(lowerColumn,next+1);
}
}
}
return true;
}
// STOP HIDING -->
</SCRIPT>
</HEAD>
<BODY BGCOLOR="iceblue">
<CENTER>
<FORM METHOD=POST NAME="spreadsheet">
<TABLE BORDER=0>
<SCRIPT LANGUAGE="JavaScript">
<!-- HIDE FROM OTHER BROWSERS
buildForm();
// STOP HIDING -->
</SCRIPT>
</TABLE>
</FORM>
<HR>
<FORM METHOD=POST>
<TABLE BORDER=1>
<TR>
<TD><DIV ALIGN=CENTER>Field Name</DIV></TD>
<TD><DIV ALIGN=CENTER>Expression</DIV></TD>
</TR>
<TR>
<TD><DIV ALIGN=CENTER><INPUT TYPE=text SIZE=10 NAME="expField"
onChange="var exp = GetCookie(this.value); this.form.expression.value = (exp == null) ? '' : exp;"></DIV></TD>
<TD><DIV ALIGN=CENTER><INPUT TYPE=text SIZE=50 NAME="expression"></DIV></TD>
<TD><DIV ALIGN=CENTER><INPUT TYPE=button VALUE="Apply" onClick="saveExp(this.form);"></DIV></TD>
<TD><DIV ALIGN=CENTER><INPUT TYPE=button VALUE="Delete" onClick="deleteExp(this.form);"></DIV></TD>
</TR>
</TABLE>
</FORM>
</CENTER>
</BODY>
</HTML>
