In this chapter, you are going to apply what you have learned to developing another application-a general-purpose spreadsheet.
Although you have experienced creating, and have seen examples of specific-function calculators, JavaScript's capability to work with forms and its math functions are not limited to these types of applications.
Using forms and cookies, you can 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.
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 you will use Bill Dortch's functions again to achieve this. 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. You will use an expiry date one year in the future to ensure that cookies are available between sessions.
Of course, you are limited by the number of cookies you can store 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. You can do this 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; would multiply the value in field A1 by the value in field B7.
Every time the value of a form field is changed, you need to be able to reevaluate all expressions. Likewise, if the definition of an expression is changed, a new expression is created, or an expression is deleted, all expressions need to be reevaluated because the change could potentially affect any of the formulas. Listing 12.1 contains the script for the program.
Listing 12.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>
"INSERTIMAGOUTPUT"
The results of this script appear like those in Figures 12.1 and 12.2.
Figure 12.1 : Building complex spreadsbeets using mathematical expessions.
Figure 12.2 : The small form at the bottom can be used to create, update, and delete expessions.
You have used five functions to create the spreadsheet application. In addition, you have included Bill Dortch's cookie functions and the search and replace functions you built in Chapter 10, "Strings, Math, and the History List." |
Using these, the calculate(), evaluateExp(), saveExp(), deleteExp(), and buildForm() functions do everything you need.
Before you look at the functions, you need to look at the body of the HTML document 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 which calls buildForm(). You use an HTML table to create a nicely formatted spreadsheet layout, as shown in the following segment.
<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>
The second form is also 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 12.2).
In addition, when the value of the expField field changes, you check if there is a stored cookie for that field, and if there is, display the expression in the expression field. Otherwise, you store an empty string in the expression field.
<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>
</CENTER>
</FORM>
</BODY>
In addition to the functions, you have several global variables you use 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 800¥600 pixel display. Only notebook users with 640¥480 displays may need a smaller spreadsheet.
The letters string contains the letters of the alphabet which 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 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. You have 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.
// 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;
You then check if there is an expression stored in the cookie with the name of the field. You store the result of the GetCookie() call in the variable expression and compare this to null. If it is not null, you have an expression, and you evaluate the expression by calling evaluateExp(). evaluateExp() returns the evaluated expression, and you directly store that value in the appropriate field in the form.
Notice the use of the form[expField] structure to refer to the appropriate field in the form. As you learned earlier in the book, object properties can be referred to in three ways:
objectName.propertyName
objectName["propertyName"]
objectName[propertyIndexNumber]
The second form uses a string literal between the brackets, and in Listing 12.1, the value of expField is a string literal.
if ((expression = GetCookie(expField)) != null)
form[expField].value = evaluateExp(form,expression);
}
}
}
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.
// Function to evaluate an expression
//
function evaluateExp(form,expression) {
var column = "";
var index = 0;
var nextExpField;
var nextExpression = "";
var nextResult = "";
You start with a for loop which iterates through each of the letters that name the columns. Inside that loop, you check 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.
You check for an occurrence of the letter by using indexOf() and storing the results in index.
// Scan the expression for field names
for (var x = 0; x < width; x ++) {
column = letters.charAt(x);
index = 0;
index = expression.indexOf(column,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 is 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.
// 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";
}
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. Make sure that you also 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.
For instance, if you have an expression A1; * B1; and B1 has the value of C1; + D1;, then, without adding the brackets, A1 would be multiplied by C1 and the result added to D1, when what you really want is to add C1 to D1 first and have the result multiplied by A1.
// Replace the field name with the result
nextExpField = nextExpField + ";";
nextResult = "(" + nextResult + ")";
expression = replace(expression,nextExpField,nextResult,
ÂnotCaseSensitive,anySubstring);
Once you have updated the expression, 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 you haven't reached the end of the string, you check for another occurrence of the current letter with indexOf() and return to the condition at the top of the while loop.
// 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);
}
}
Once you finish the for loop, you are ready to evaluate the expression. You use with(Math) so that any methods from the Math object that occurred in the expression don't require the presence of the Math prefix.
You evaluate the expression using the eval() statement.
// Evaluate the expression
with (Math) {
var result = eval(expression);
}
// Return the result
return result;
}
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 you inform the user that she needs to delete another expression if she wants to save this one.
// 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 you have room to save the expression, then save it by getting the name of the cookie directly from the appropriate field in the form and getting the expression in the same way. You also update the number of expressions by one and update the cookie containing this value (notice the use of parseInt() to change the string returned by GetCookie() into an integer).
// 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);
Finally, you recalculate the spreadsheet by calling calculate() and then inform the user that the expression has been saved.
// Recalculate the spreadsheet
calculate(document.spreadsheet);
alert("Expession for field " + form.expField.value + " is 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.
You start by checking whether there is an expression stored in that field. If there is, you save a new cookie with the same name but use deleteExpiry as the expiry date. You also decrease the number of expressions by one and update the cookie containing the number.
// 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);
Once the cookie has been deleted, you recalculate the spreadsheet and inform the user the task is done in the same way as the saveExp() function.
// 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.");
}
}
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 by 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, you save a zero value there to initialize the spreadsheet.
// 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);
}
Next, you build the header row for the table which contains a blank field at the start, and then a field for each column with the appropriate letter centered in the field. You do this with a for loop that extracts each letter from the letters string.
// 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>");
Once the table header is output, you use two nested forT> loops 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.
// 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>");
}
}
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, you use two nested for loops to iterate through all 96 fields in the form. For each, you call GetCookie() to check whether the field has an expression, and if it does, you call evaluateExp().
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 you change a value in the form.
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, you 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 is null, there are no expressions and no evaluation is needed. Otherwise, you enter a while loop that continues 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.
You then increment index 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.
You handle updating the fieldList cookie by first checking if there is a list already. If not, you simply create the list with the current field name. If there is a list, you remove the field name 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, you simply use the replace() function 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. The structure you will use is to have the first character of the title expression be a double-quote character.
Then, you can simply update the evaluateExp() function to return the rest of the string when it encounters this 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;
}
You have added only one step to the evaluateExp() function. Before you attempt to evaluate the expression as a mathematical expression, you check the first character for a double quotation mark. If you find one, you simply return the rest of the expression string.
By way of example, you are going to perform some very basic error checking.
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 will check basic syntax-that is, that the field names use capital letters and end with a semicolon and also that you don't have 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, you use a for loop which loops through each of the letters that are column names and performs a series of checks based on that column.
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);
}
You first assign the column name to the variable column. You also assign the lowercase version of the same letter to lowerColumn because you will also 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 you continue to find instances of the letter.
// 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;
}
The first check in the while
loop is to see if the character immediately following the letter
is a number. If it is not a number-which would make it the start
of a field reference-you 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. |
You perform this check by passing the character through parseInt() and then check if the result is zero. If it is, you also check if the actual character is zero and make sure that the letter you found is a lowercase letter (since all the Math methods start with lowercase letters).
Having passed all these conditions, you make the assumption that this is a Math method and you scan forward for another occurrence of the letter and then return to the top of the loop with the continue statement.
// 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;
}
If you get by the first if
statement, you know you have a letter followe