Previous Page TOC Index Next Page Home


12

Creating a Spreadsheet in JavaScript

In this chapter, you are going to apply what you have learned to developing another application—general purpose spreadsheet.

While you have experience creating, and have seen examples of specific-function calculators, JavaScript's ability to work with forms and its math functions are not limited to these types of applications.

Using forms and cookies, it is possible to create a general-purpose spreadsheet that retains its formulas between sessions.

The Specifications

The spreadsheet has several basic requirements:

What You Need to Do

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 semi-colon. 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 re-evaluate 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 re-evaluated because the change could potentially affect any of the formulas. Listing 12.1 contains the script for the program.

Input 

<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 <[email protected]>

//  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>

Output

The results of this script appear like those in Figures 12.1 and 12.2.


Figure 12.1. You can build complex spreadsheets using mathematical expressions.


Figure 12.2. The small form at the bottom can be used to create, update, and delete expressions.

End of Output

Analysis

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>

Setting Up the Global Variables

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 800x600 pixel display. Only notebook users with 640x480 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

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);

    }

  }

}

The evaluateExp() Function

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 if there is an occurrence of the letter in the expression. If there is, it means that there is 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 only executes 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 semi-colon (;), which marks the end of the field name. Given this value, you check if 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 semi-colon 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 if 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 which 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

This 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 object for the expression form 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 you 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.");

  }

}

The deleteExp() Function

Just as saveExp() saved an expression, deleteExp() deletes the expression indicated by a field name in the form. It takes the form object again as an expression and is invoked when the user clicks on the Delete button.

You start by checking if 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 delete 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

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 if 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 for 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>");

  }

}

Beyond the Basic Script

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:

Improving Efficiency

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 if 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 which contain expressions. For instance, a semi-colon delimited list like 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 which continues until the index reaches the end of the fieldList string.

Inside the while loop, you scan for the next semi-colon using indexOf() and extract the substring from index to the character before the semi-colon. 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 semi-colon.

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.

Adding Title Fields

In order to save title fields, treat them as expressions so that 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 only added 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.

Checking for Errors

By way of example, you are going to perform some very basic error checking.

There 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 semi-colon and make sure you don't have a circular expression.

To make the script easier to read, you will 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 if 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 upper case

      if (expression.charAt(index) == lowerColumn) {

        alert("Field names must use upper case letters.");

        return false;

      }

      // The letter was upper case, so check that we have only numbers followed by a semi-colon

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 if 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 outlined above. 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 lower case version of the same letter to lowerColumn because you will also need to deal with lower case versions of the same letter.

You then check for an occurrence of either the upper case or lower case letter using indexOf() and assign the index to the variable index. You then enter a while loop which 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.

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.

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 lower case letter (since all the Math methods start with lower case 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 upper case

      if (expression.charAt(index) == lowerColumn) {

        alert("Field names must use upper case letters.");

        return false;

      }

If you get by the first if statement, then 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 upper case letter; if not, you alert the user and return a false value. 

      // The letter was upper case, so check that we have only numbers followed by a semi-colon

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;

        }

      }

Next, you move forward through the expression checking each character. If you find a non-numeric character before you reach a semi-colon, 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 semi-colon, you also know you have an incorrect form, and you do the same thing. 

      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;

        }

      }

The last check you perform is to look for a circular expression. You extract the field name that you are currently looking at and use it to get any existing expression for that field. If the field has an expression, you search it using search() to see if the expression refers back to the field you are trying to add an expression to. If it does, you have a circular expression and you inform the user and return a false value 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. 

if (next + 1 == expression.length) { break; }

      index = expression.indexOf(column,next+1);

      if (index < 0) {

        index = expression.indexOf(lowerColumn,next+1);

      }

    }

Finally, you check if you have reached the end of the expression and if not, search for another occurrence of the letter, store the index in index, and return to the top of the while loop.

The other place you need to perform error checking is in the evaluateExp() function. Here, you need to make sure that the value of fields being used in expressions is numeric. You do this 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(), you check that the result is not "error". If it is "error", you simply return error back up the chain of function calls.

If you are getting a value directly from a form field and the field is not empty, you check if the value is a number by applying parseInt() to the value and checking the result. If you don't have a numeric expression, you return "error".

Summary

In this chapter we have put together a complete, workable spreadsheet application 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 you have just built, I am including the complete source code of the program, including all the changes you just made. In the exercises later in this chapter we will extend the features of this application even further. 

<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 <[email protected]>

//  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 upper case

      if (expression.charAt(index) == lowerColumn) {

        alert("Field names must use upper case letters.");

        return false;

      }

      // The letter was upper case, so check that we have only numbers followed by a semi-colon

      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>

Exercises

  1. Earlier in the chapter we discussed adding two additional features: the clear button and the range capability. Extend the script to add the clear button.

  2. Extend the script you just wrote in Exercise 1 to include the following range capability: When the user specifies the range, simply add up the values in all the fields in that range. You will need to define a syntax for ranges and then adjust the script to accommodate those changes. Try to define the syntax in such a way that it does not cause problems in the existing checkExp() function.

Answers

  1. To add the clear function, add a single button to the second HTML form:

    <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>
    <TD><DIV ALIGN=CENTER><INPUT TYPE=button VALUE="Clear" onClick="clearSpreadSheet();"></DIV></TD>
    </TR>

    </TABLE>
    </FORM>

    You then need to add a clearSpreadSheet() function:


    function clearSpreadSheet() {

    var form = document.spreadsheet;

    var index = 0;
    var next = 0;
    var expField = "";
    var field = "";
    var fieldList = GetCookie("fieldList");

    // Clear Expression Cookies
    if (fieldList != null) {
    while (index != fieldList.length) {
    next = fieldList.indexOf(";",index);
    expField = fieldList.substring(index,next);
    SetCookie(expField,"",deleteExpiry);
    index = next + 1;
    }
    }

    SetCookie("fieldList","",deleteExpiry);
    SetCookie("numExpressions",0,expiryDate);

    // Clear form fields

    for (var x = 0; x < width; x++) {
    for (var y = 1; y <= height; y++) {
    field = letters.charAt(x) + y;
    form[field].value = "";
    }
    }

    }

    There are two main steps in this function. First, you extract the field list from its cookie and loop through each of the expressions in the list the same way you did in the revised calculate() function. For each expression, you delete its cookie. Then you delete the field list cookie and set the number of expressions to zero.

    Next, you use a set of nested for loops to place an empty string in each form text entry field in the spreadsheet.

  2. In this solution, use a simple syntax for defining ranges: <fieldNameOne;fieldNametwo;>. If you want to define the sum of all fields from A1 to A6, you could use <A1;A6;>. Similarly, all fields from A1 to C1 would be <A1;C1;>. All ranges must be on the same row or column and must be indicated from lowest field to highest (that is, <C1;A1;> is invalid).

    You add support for this range feature by adding a section to the evaluateExp() function:

    function evaluateExp(form,expression) {

    var column = "";
    var index = 0;
    var nextExpField;
    var nextExpression = "";
    var nextResult = "";
    var next = 0;
    var firstField = "";
    var lastField = "";
    var rangeExp = ""

    if (expression.charAt(0) == '"') {
    return(expression.substring(1,expression.length));
    }

    // Check for ranges
    index = expression.indexOf("<",index);
    while (index >= 0) {
    next = expression.indexOf(">",index+1);
    nextExpField = expression.substring(index,next+1);
    firstField = expression.substring(index+1,expression.indexOf(";",index+1));
    lastField = expression.substring
    [ci:ccc](expression.indexOf(";",index+1) + 1,next - 1);

    if (firstField.charAt(0) == lastField.charAt(0)) {
    var start = parseInt(firstField.substring(1,firstField.length));
    var end = parseInt(lastField.substring(1,lastField.length));
    nextResult = firstField.charAt(0) + start + ";";
    for (var i = start + 1; i <= end; i++)
    nextResult += " + " + firstField.charAt(0) + i + ";";
    } else {
    var tempChar = firstField.charAt(0);
    var start = letters.indexOf(tempChar,0);
    tempChar = lastField.charAt(0);
    var end = letters.indexOf(tempChar,0);
    nextResult = letters.charAt(start) + firstField.substring(1,firstField.length) + ";";
    for (var i = start + 1; i <= end; i++)
    nextResult += " + " + letters.charAt(i) +firstField.substring(1,firstField.length) + ";";
    }

    rangeExp = "<" + firstField + ";" + lastField + ";>";
    nextResult = "(" + nextResult + ")";
    expression = replace(expression,rangeExp,nextResult,notCaseSensitive,anySubstring);
    index += nextResult.length;
    if (index >= expression.length - 1) { break; }
    index = expression.indexOf("<",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);

    // 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);
    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";
    }
    }

    }

    // 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;

    }

    What you have done is add a section that replaces ranges with a mathematical expression. For instance, <A1;A4;> is replaced by (A1; + A2; + A3;). Once this is done, you can evaluate the expression in the same way you did before.

    You check for ranges by scanning the string for the < character using indexOf(). All the processing takes place inside a while loop:

    index = expression.indexOf("<",index);
    while (index >= 0) {
    next = expression.indexOf(">",index+1);
    firstField = expression.substring(index+1,expression.indexOf(";",index+1));
    lastField = expression.substring(expression.indexOf(";",index+1) + 1,next - 1);

    You start by finding the end of the range by looking for >. Then you are able to extract the first field name and the last field name.

    if (firstField.charAt(0) == lastField.charAt(0)) {
    var start = parseInt(firstField.substring(1,firstField.length));
    var end = parseInt(lastField.substring(1,lastField.length));
    nextResult = firstField.charAt(0) + start + ";";
    for (var i = start + 1; i <= end; i++)
    nextResult += " + " + firstField.charAt(0) + i + ";";
    } else {

    If you have a range on the same row (the first character of both lastField and firstField are the same), then you loop through each field in the range and build a mathematical expression that adds the fields.

    var tempChar = firstField.charAt(0);
    var start = letters.indexOf(tempChar,0);
    tempChar = lastField.charAt(0);
    var end = letters.indexOf(tempChar,0);
    nextResult = letters.charAt(start) + firstField.substring(1,firstField.length) + ";";
    for (var i = start + 1; i <= end; i++)
    nextResult += " + " + letters.charAt(i) + firstField.substring(1,firstField.length) + ";";
    }

    If you don't have a range on the same row, then it must run down a single column. If it does, you build an expression appropriately.

    rangeExp = "<" + firstField + ";" + lastField + ";>";
    nextResult = "(" + nextResult + ")";
    expression = replace(expression,rangeExp,nextResult,notCaseSensitive,anySubstring);
    index += nextResult.length;
    if (index >= expression.length - 1) { break; }
    index = expression.indexOf("<",index);

    }

    Finally, you use replace() to replace the range syntax with its mathematical equivalent. Then you see if you are at the end of the expression; if not, you scan for another range.

    The range syntax you are using still enables checkExp() to accurately perform the checks it is making. However, you are not checking that the format of the range syntax is correct. For instance, you don't know that each open symbol < is matched with its partner >, and you don't know if extra characters have been introduced into the middle of the range structure.

    You can add support for this in the checkExp() function. After you complete all of the checks, before you would return true, you can add a while loop that checks the range syntax:

    index = expression.indexOf("<",0);
    while (index >= 0) {
    next = index + 1;

    for (i = 1; i <= 2; i++) {
    thisLetter = expression.charAt(next);
    if (letters.indexOf(thisLetter,0) < 0) {
    alert("Incorrect Range format.");
    return false;
    }

    while(expression.charAt(++next) != ";") {
    checkNum = parseInt(expression.charAt(next));
    if ((checkNum == 0) && (expression.charAt(next) != "0")) {
    alert("Incorrect Range format.");
    return false;
    }
    }

    next ++;

    }

    if (expression.charAt(next) != ">") {
    alert("Incorrect Range format.");
    return false;
    }

    if (next + 1 == expression.length) { break; }
    index = expression.indexOf("<",next);

    }

    The process of checking for errors is fairly simple. When you find occurrences of <, you first check the next letter to make sure it is a legitimate column name. If it is, you check that you have only numbers until a semi-colon. Then you check if you have a valid letter again, and then check numbers again until another semicolon. Finally, you check for the closing >.

Previous Page TOC Index Next Page Home