
	Profit and Value from Information Technology
	Ecommerce & Extranets : Client Systems : Enterprise Applications : Application Development

Chapter 12 Creating a Spreadsheet in JavaScript

The Specifications
What You Need to Do
Beyond the Basic Script
Summary
Exercises
Answers

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 Specifications

The spreadsheet has several basic requirements:

It should have a reasonable number of fields-not so many that users with small displays will have trouble, but not so few as to be less than useful. A good number appears to be roughly 100.
The columns and rows should be numbered-one with numerals and one with letters.
Users should be able to create formulas, or expressions, for any of the fields that use values from other fields to calculate their own values.
Formulas should be able to include mathematical operators, as well as any of the methods of the Math object. Basically, any legal JavaScript mathematical expression should be acceptable.
Users should be able to change or delete any expression.
Expressions should be saved between sessions so that users can come back and continue using their spreadsheets.

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 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">  </SCRIPT> </HEAD> <BODY BGCOLOR="iceblue"> <CENTER> <FORM METHOD=POST NAME="spreadsheet"> <TABLE BORDER=0> <SCRIPT LANGUAGE="JavaScript">  </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">  </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 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

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

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

The `deleteExp()` Function

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

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

Beyond the Basic Script

The basic script works but it has several limitations, including the following:

Efficiency-Most users, especially those on Windows platforms, will notice that your script is a little slow and that actions create a noticeable lag to update the spreadsheet.
Error checking-This script doesn't check that the syntax of the expressions is valid. It doesn't check that fields contain numeric values when it evaluates expressions and doesn't check for circular expressions (expressions that depend on each other to evaluate and will cause infinite recursion).
Title-If you tried to create a spreadsheet including titles, you will notice that when you come back to the spreadsheet, the values of these title fields are lost.

In addition to these limitations, there are several features you could add to the spreadsheet to make it more useful:

Ranges-Most spreadsheets enable formulas to include ranges in their expressions. (For instance, A1; ... A5; might be the total of the values in all fields from A1 to A5.)
Clear-This application provides no easy way for the user to clear all the field values and all the expressions and start from scratch.

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

Adding Title Fields

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.

Checking for Errors

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 followed by a number, which means the user is trying to reference a field name. The first thing you do is check if the user is using an uppercase letter; if not, you alert the user and return a false value.

// The letter was upper case, 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; } }

Next, you move forward through the expression, checking each character. If you find a non-numeric character before you reach a semicolon, then you know that you have an invalid reference, so you alert the user and return a false value. Likewise, if you reach the end of the expression without hitting a semicolon, you also know you have an incorrect form, and 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 whether 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 values of fields being used in expressions are 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 whether 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 you will extend the features of this application even further.

Listing 12.2. The final spreadsheet script.

<HTML> <HEAD> <TITLE>Chapter 12</TITLE> <SCRIPT LANGUAGE="JavaScript">  </SCRIPT> </HEAD> <BODY BGCOLOR="iceblue"> <CENTER> <FORM METHOD=POST NAME="spreadsheet"> <TABLE BORDER=0> <SCRIPT LANGUAGE="JavaScript">  </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

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

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.
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 Â(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 semicolon. Then you check if you have a valid letter again, and then check numbers again until another semicolon. Finally, you check for the closing >.

Use of this site is subject certain Terms & Conditions.
Copyright (c) 1996-1999 EarthWeb, Inc.. All rights reserved. Reproduction in whole or in part in any form or medium without express written permission of EarthWeb is prohibited. Please read our privacy policy for details.

Chapter 12

Creating a Spreadsheet in JavaScript

CONTENTS