/**
 * SeekQuarry/Yioop --
 * Open Source Pure PHP Search Engine, Crawler, and Indexer
 *
 * Copyright (C) 2009 - 2026  Chris Pollett chris@pollett.org
 *
 * LICENSE:
 *
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program.  If not, see <https://www.gnu.org/licenses/>.
 *
 * END LICENSE
 *
 * @author Chris Pollett
 * @license https://www.gnu.org/licenses/ GPL3
 * @link https://www.seekquarry.com/
 * @copyright 2017 - 2018
 * @filesource
 */
/**
 * Defines a class for drawing and editing spreadsheets within a tag with
 *
 * Example uses:
 * spreadsheet = new Spreadsheet(some_html_element_id,
 *     [["Tom",5],["Sally", 6]]); //read-only
 * spreadsheet.draw();
 *
 * spreadsheet2 = new Spreadsheet(some_html_element_id2,
 *     [["Tom",5],["Sally", 6]], {"mode":"write"}); //editable
 * spreadsheet2.draw();
 *
 * @param String spreadsheet_id the id of the tag in which to draw the
 *      spreadsheet
 * @param Array supplied_data two dimensional array of the rows and columns
 *      of data for the spreadsheet
 */
function Spreadsheet(spreadsheet_id, supplied_data)
{
    var self = this;
    var p = Spreadsheet.prototype;
    var properties = (typeof arguments[2] !== 'undefined') ?
        arguments[2] : {};
    var container = document.getElementById(spreadsheet_id);
    var draw_histograms = false;
    if (!container) {
        return false;
    }
    p.max_name_length = 32;
    supplied_data = eval(supplied_data);
    if (!Array.isArray(supplied_data)) {
        supplied_data = [];
    }
    var width = 0;
    for (index in supplied_data) {
        if (!Array.isArray(supplied_data[index])) {
            supplied_data[index] = [];
        }
        if ([index].length > width) {
            width = supplied_data[index].length;
        }
    }
    var length = supplied_data.length;
    var data = [];
    var eval_data = [];
    for (var i = 0; i < length; i++) {
        data[i] = [];
        for (var j = 0; j < width; j++) {
            data[i][j] = (typeof supplied_data[i][j] == 'undefined') ? "" :
                supplied_data[i][j];
        }
    }
    var property_defaults = {
        'mode' : 'read', // currently, only supports csv
        'data_id' : spreadsheet_id + "-data",
        'data_name' : 'page',
        'headings' : true, // whether headings drawn for table
        'offset' : [0, 0], /* 0 in first coordinate means top row is A, a
                1 would mean B,etc.
                offset of 0 in second coordinate, means first column 1
                offset of 1 in second coordinate, means first column 2
                */
        'table_style' : 'overflow:auto;',
        'user_name' : 'anonymous',
    };
    for (var property_key in property_defaults) {
        if (typeof properties[property_key] !== 'undefined') {
            this[property_key] = properties[property_key];
        } else {
            this[property_key] = property_defaults[property_key];
        }
    }
    /**
     * Main function used to draw the spreadsheet with the container tag
     */
    p.draw = function()
    {
        //used to draw a csv based on spreadsheet data
        var table = "<div style='" + self.table_style + "'>";
        var length = data.length;
        var width = (typeof data[0] == 'undefined') ? 0 : data[0].length;
        var add_button = "";
        var delete_button = "";
        var pre_delete_button = "";
        var x_offset = self.offset[0];
        var y_offset = self.offset[1];
        if (self.mode == 'write') {
            table += "<input id='" + self.data_id + "' type='hidden' " +
                "name='" + self.data_name + "' value='" + JSON.stringify(
                data)+ "' >";
            add_button = "<button>+</button>";
            pre_delete_button = "<button>-</button>";
        } else {
            add_button = "";
            pre_delete_button = "";
        }
        table += "<table class='wikitable' >";
        if (self.headings || self.mode == 'write') {
            table += "<tr><th></th>";
            for (var i = 0; i < width; i++) {
                table += "<th style='padding:0;min-width:1in;text-align:right;'>" +
                    "<div class='resizable' "+
                    "style='margin:0;padding:1px 15px 3px 1px;'>" +
                    delete_button + self.letterRepresentation(
                    i + y_offset) + add_button +
                    "</div></th>";
                delete_button = pre_delete_button;
            }
            table += "</tr>";
        }
        delete_button = "";
        eval_data = [];
        for (i = 0; i < length; i++) {
            eval_data[i] = [];
            table += "<tr>";
            if (self.headings || self.mode == 'write') {
                table += "<th style='min-width:1.1in;text-align:right;'>" +
                    delete_button + (i + x_offset + 1) + add_button + "</th>";
                delete_button = pre_delete_button;
            }
            for (var j = 0; j < width; j++) {
                eval_data[i][j] = data[i][j];
                var item = "";
                if (typeof eval_data[i][j] == 'string') {
                    item = eval_data[i][j];
                    if (item.charAt(0) == '=') {
                        item = self.evaluateCell(item.substring(1), 0)[1];
                        eval_data[i][j] = item;
                    }
                } else {
                    item = eval_data[i][j];
                }
                if (item == "" && self.mode == 'read') {
                    item = "<div style='min-height:1em;'></div>";
                }
                table += "<td style='min-width:1.1in;text-align:right;'>" +
                    item + "</td>";
            }
            table += "</tr>";
        }
        table += "</table></div>";
        container.innerHTML = table;
    }
    /**
     *  Draws to the Spreadsheet's container histograms for each column
     *  of the number of occurrrence of each value that occurs in that column.
     *  For example, if the second column only has two values in it red and
     *  green where red occurs five times and green twice, then
     *  in the histogram for that column there will be two bars one witch
     *  label red and one with label green. The bar for red will be 5/7
     *  of a full bar and the one for green will be 2/7 of a full bar.
     *
     *  @param Object config can have the following fields:
     *      has_column_headers: which if true indicates the first row of
     *      spreadsheet has names for each of the columns and those names
     *      should be used in place of 0, 1, 2, ... It also tells
     *      the drawer not to include this row in the value tallies.
     *      has_row_headers:which if true indicates the first column of
     *      spreadsheet has names for each of the rows. This column will be
     *      not be used in making the histograms.
     *      ignore_columns: an array of column numbers or column header names
     *      to be excluded from the histogram
     *      ignore_rows: an array of row numbers or row header names
     *      to be excluded from the histogram
     *      ignore_values: an array of values to be ignored in
     *      histogram counts
     *      stop_condition: [col, val] if the column col has value val, then
     *      stop processing the remaining rows when making the histogram
     */
    p.drawHistograms = function (config) {
        var length = data.length;
        var width = (typeof data[0] == 'undefined') ? 0 : data[0].length;
        var has_column_headers = ((typeof config['has_column_headers'] !==
            'undefined') && config['has_column_headers']) ? true : false;
        var has_row_headers = ((typeof config['has_row_headers'] !==
            'undefined') && config['has_row_headers']) ? true : false;
        var ignore_columns = (typeof config['ignore_columns'] !==
            'undefined') ? config['ignore_columns'] : [];
        var ignore_rows = (typeof config['ignore_rows'] !==
            'undefined') ? config['ignore_rows'] : [];
        var ignore_values = (typeof config['ignore_values'] !==
            'undefined') ? config['ignore_values'] : [];
        var stop_condition = (typeof config['stop_condition'] !==
            'undefined' && typeof config['stop_condition'][0] !==
            'undefined' && typeof config['stop_condition'][1] !==
            'undefined') ? [config['stop_condition'][0],
            config['stop_condition'][1]] : [];
        var column_headers = [];
        var row_headers = [];
        if (has_column_headers) {
            column_headers = data[0];
            ignore_rows.push(0);
        }
        if (has_row_headers) {
            for (var i = 0; i < length; i++) {
                row_headers.push(data[i][0]);
            }
            ignore_columns.push(0);
        }
        histogram = {};
        for (let row = 0; row < length; row++) {
            if (stop_condition.length != 0 &&
                (data[row][column_headers[stop_condition[0]]] ==
                    stop_condition[1]) ) {
                break;
            }
            for (let col = 0; col < width; col++) {
                if (ignore_rows.includes(row) ||
                    ((typeof row_headers[row] !== 'undefined') &&
                    ignore_rows.includes(row_headers[row]))) {
                    continue;
                }
                if (ignore_columns.includes(col) ||
                    ((typeof column_headers[col] !== 'undefined') &&
                    ignore_columns.includes(column_headers[col]))) {
                    continue;
                }
                if (ignore_values.includes(data[row][col])) {
                    continue;
                }
                let column_header =
                    (typeof column_headers[col] !== 'undefined') ?
                    column_headers[col] : col;
                if (typeof histogram[column_header] === 'undefined') {
                    histogram[column_header] = {};
                }
                if (typeof histogram[column_header][data[row][col]]
                    === 'undefined') {
                    histogram[column_header][data[row][col]] = 0;
                }
                histogram[column_header][data[row][col]]++;
            }
        }
        let out = "";
        for (let field in histogram) {
            out += `<table class="histotable"><caption>${field}</caption>`;
            let num = 0;
            for (let value in histogram[field]) {
                num += histogram[field][value];
            }
            for (let value in histogram[field]) {
                let fraction = Math.floor(100 * histogram[field][value] /
                    Math.max(num,1));
                out += `<tr><th>${value}</th><td>${histogram[field][value]}` +
                    `</td><td><div style="width:${fraction}%;">&nbsp;` +
                    `</div></td></tr>`;
            }
            out += `</table>`;
        }
        container.innerHTML = out;
    }
    /**
     *  If the Spreadsheet currently has the sheet table drawn on it this
     *  will toggle to a histogram view displaying for each column of
     *  data the frequency counts of each of the values that occurred in that
     *  column. If Spreadsheet currently has the histogram view dispay
     *  then it will return to display the normal spreadsheet view.
     *
     *  @param Object config can have the following fields:
     *      has_column_headers: which if true indicates the first row of
     *      spreadsheet has names for each of the columns and those names
     *      should be used in place of 0, 1, 2, ... It also tells
     *      the drawer not to include this row in the value tallies.
     *      has_row_headers:which if true indicates the first column of
     *      spreadsheet has names for each of the rows. This column will be
     *      not be used in making the histograms.
     *      ignore_columns: an array of column numbers or column header names
     *      to be excluded from the histogram
     *      ignore_rows: an array of row numbers or row header names
     *      to be excluded from the histogram
     */
    p.toggleHistograms = function (config)
    {
        if (draw_histograms) {
            draw_histograms = false;
            p.draw();
        } else {
            draw_histograms = true;
            p.drawHistograms(config);
        }
    }
    /**
     * Sets the current HTML Elment to which the Spreadsheet will draw.
     *
     * @param String id of an HTML elment of the web page.
     */
    p.setContainer = function (id)
    {
        container = document.getElementById(id);
    }
    /**
     * Calculates the value of a cell expression in a spreadsheet. This code
     * runs on the client. In Yioop's GroupModel there is almost identical
     * PHP code that runs on the server.
     *
     * @param String cell_expression a string representing a formula to
     * calculate from a spreadsheet file
     * @param Number location character position in cell_expression to start
     *      evaluating from
     * @param Number operator_pos a position in the array of binary operators
     *      ['*', '/', '+', '-', '%'](used to compute operators with preference)
     * @return Array [new_loc, the value of the cell or the String 'NaN' if
     *      the expression was not evaluatable]
     */
    p.evaluateCell = function(cell_expression, location, operator_pos)
    {
        var out = [location, false];
        var operators = ['%', '-','+', '/', '*'];
        if (operator_pos === undefined) {
            operator_pos = 0;
        }
        if (location >= cell_expression.length) {
            return out;
        }
        if (operator_pos >= operators.length) {
            out = self.evaluateFactor(cell_expression, location);
            return out;
        }
        var operator = operators[operator_pos];
        var left_out = self.evaluateCell(cell_expression, location,
            operator_pos + 1);
        if (left_out[0] >= cell_expression.length) {
            return left_out;
        }
        left_out[0] = self.skipWhitespace(cell_expression, left_out[0]);
        if (cell_expression.charAt(left_out[0]) != operator) {
            return left_out;
        }
        var right_out = self.evaluateCell(cell_expression, left_out[0] + 1,
            operator_pos);
        out[0] = self.skipWhitespace(cell_expression, right_out[0]);
        out[1] = eval("" + Number(left_out[1]) + operator +
            Number(right_out[1]));
        return out;
    }
    /**
     * Used to evaluate the left hand factor of a binary operator
     * appearing in a CSV spreadsheet cell
     *
     * @param String cell_expression cell formula to evaluate
     * @param Number location start offset in cell expression
     *      of factor protion that this method needs to evaluate
     * @return Array [new_loc, the value of sub-expression]
     */
    p.evaluateFactor = function(cell_expression, location)
    {
        var out = [location, false];
        if (location >= cell_expression.length) {
            return out;
        }
        location = self.skipWhitespace(cell_expression, location);
        out = self.evalFunctionInvocation(cell_expression, location);
        if (out[1] !== false) {
            return out;
        }
        out = self.evalParenthesizedExpression(cell_expression, location);
        if (out[1] !== false) {
            return out;
        }
        out = self.evalRangeExpression(cell_expression, location);
        if (out[1] !== false) {
            return out;
        }
        out = self.evalNegatedExpression(cell_expression, location);
        if (out[1] !== false) {
            return out;
        }
        out = self.evalNumericExpression(cell_expression, location);
        if (out[1] !== false) {
            return out;
        }
        out = self.evalCellNameExpression(cell_expression, location);
        if (out[1] !== false) {
            return out;
        }
        out = self.evalStringExpression(cell_expression, location);
        if (out[1] !== false) {
            return out;
        }
        return out;
    }
    /**
     * Used to evaluate a function call
     * appearing in a CSV spreadsheet cell formula
     *
     * @param String cell_expression cell formula to evaluate
     * @param Number location start offset in cell expression
     *      of function call that this method needs to evaluate
     * @return Array [new_loc, the value of sub-expression]
     */
    p.evalFunctionInvocation = function(cell_expression, location)
    {
        var out = [location, false];
        var rest = cell_expression.substring(location, location +
            self.max_name_length);
        var pattern =
            new RegExp("^(avg|ceil|cell|col|exp|floor|log|min|max|pow|"+
            "row|sqrt|sum|username)\\(", 'i');
        var call_parts = rest.match(pattern);
        if (call_parts === null) {
            return out;
        }
        location += call_parts[0].length;
        var arg_values = self.evaluateArgListExpression(cell_expression,
            location);
        if (arg_values[1] == "NaN") {
            return arg_values;
        }
        if ((["username"].includes(
            call_parts[1]) && arg_values[1] !== false) ||
            (["ceil", "floor", "exp", "log", "sqrt"].includes(
            call_parts[1]) && arg_values[1].length != 1) ||
            (["cell", "pow"].includes(call_parts[1]) &&
            arg_values[1].length != 2) ||(["min", "max"].includes(call_parts[1])
            && arg_values[1].length == 0) ||
            (["col", "row"].includes(call_parts[1]) &&
            arg_values[1].length != 4) ||
            cell_expression.charAt(arg_values[0]) != ')') {
            out[0] = arg_values[0];
            out[1] = "NaN";
            return out;
        }
        out[0] = arg_values[0] + 1;
        switch (call_parts[1]) {
            case "ceil":
                var arg = arg_values[1][0];
                out[1] = Math.ceil(Number(arg));
                break;
            case "cell":
                var args = arg_values[1];
                var row_col = self.cellNameAsRowColumn("" + args[1] + args[0]);
                if (row_col !== null) {
                    out[1] = eval_data[row_col[0] - 1][row_col[1]];
                } else {
                    out[1] = "NaN";
                }
                break;
            case "col":
                var args = arg_values[1];
                var tmp = self.cellNameAsRowColumn("" + args[2] + "0");
                var start = Math.max(tmp[1] - 1, 0);
                tmp = self.cellNameAsRowColumn("" + args[3] + "0");
                var end = Math.min(tmp[1] - 1, eval_data[0].length - 1);
                out[1] = -1;
                for (var j = start; j <= end; j++) {
                    if (eval_data[Math.max(args[1] - 1,0)][j] == args[0]) {
                        out[1] = self.letterRepresentation(j);
                        break;
                    }
                }
                break;
            case "exp":
                var arg = arg_values[1][0];
                out[1] = Math.exp(Number(arg));
                break;
            case "floor":
                var arg = arg_values[1][0];
                out[1] = Math.floor(Number(arg));
            case "log":
                var arg = arg_values[1][0];
                out[1] = Math.log(Number(arg));
                break;
            case "min":
                var minnands = arg_values[1];
                var min = minnands[0];
                for (var i = 0; i < minnands.length; i++) {
                    if (minnands[i] < min) {
                        min = minnands[i];
                    }
                }
                out[1] = min;
                break;
            case "max":
                var maxands = arg_values[1];
                var max = maxands[0];
                for (var i = 0; i < maxands.length; i++) {
                    if (maxands[i] > max) {
                        max = maxands[i];
                    }
                }
                out[1] = max;
                break;
            case "pow":
                out[1] = Math.pow(Number(arg_values[1][0]),
                    Number(arg_values[1][1]));
                break;
            case "row":
                var args = arg_values[1];
                var tmp = self.cellNameAsRowColumn("" + args[1] + "0");
                var col = tmp[1];
                var start = Math.max(args[2] - 1, 0);
                var end = Math.min(args[3] - 1, eval_data.length - 1);
                out[1] = -1;
                for (var i = start; i <= end; i++) {
                    if (eval_data[i][col] == args[0]) {
                        out[1] = i + 1;
                        break;
                    }
                }
                break;
            case "sqrt":
                var arg = arg_values[1][0];
                out[1] = Math.sqrt(Number(arg));
                break;
            case "sum":
            case "avg":
                var sum = 0;
                var summands = arg_values[1];
                for (var i = 0; i < summands.length; i++) {
                    var summand = Number(summands[i]);
                    sum += summand;
                }
                out[1] = (call_parts[1] == 'sum') ? sum :
                     sum/summands.length;
                break;
            case "username":
                out[1] = self.user_name;
                break;
        }
        return out;
    }
    /**
     * Used to evaluate a spreadsheet expression surrounded by
     * parentheses appearing in a CSV spreadsheet cell formula
     *
     * @param String cell_expression cell formula to evaluate
     * @param Number location start offset in cell expression
     *      of parentheses expression that this method needs to
     *      evaluate
     * @return Array [new_loc, the value of sub-expression]
     */
    p.evalParenthesizedExpression = function(cell_expression, location)
    {
        var out = [location, false];
        if (location >= cell_expression.length) {
            return out;
        }
        location = self.skipWhitespace(cell_expression, location);
        if (cell_expression.charAt(location) == "(") {
            out = self.evaluateCell(cell_expression, location + 1);
            if (cell_expression.charAt(out[0]) != ')') {
                out[1] = "NaN";
                return out;
            }
            out[0] = self.skipWhitespace(cell_expression, out[0] + 1);
            return out;
        }
        return out;
    }
    /**
     * Used to evaluate the expressions in a list of arguments to
     * a function call appearing in a CSV spreadsheet
     * cell formula
     *
     * @param string $cell_expression cell formula to evaluate
     * @param Number $location start offset in cell expression
     *      of argument list that this method needs to
     *      evaluate
     * @return Array [new_loc, array of arg-list]
     */
    p.evaluateArgListExpression = function(cell_expression, location)
    {
        var out = [location, false];
        if (location >= cell_expression.length) {
            return out;
        }
        location = self.skipWhitespace(cell_expression, location);
        var more_args = true;
        out = [location, []];
        while (more_args) {
            more_args = false;
            var sub_out = self.evaluateCell(cell_expression, location);
            if (sub_out[1] == 'NaN' || sub_out[1] === false) {
                return sub_out;
            }
            if (typeof sub_out[1] == 'object') {
                for(var i = 0 ; i < sub_out[1].length; i++) {
                    out[1].push(sub_out[1][i]);
                }
            } else {
                out[1].push(sub_out[1]);
            }
            location = self.skipWhitespace(cell_expression, sub_out[0]);
            out[0] = location;
            if (location < cell_expression.length &&
                cell_expression.charAt(location) == ',') {
                more_args = true;
                location++;
            }
        }
        return out;
    }
    /**
     * Used to convert range expressions, cell_name1:cell_name2 into a
     * sequence of cells, cell_name1, ..., cell_name2 so that it may be
     * used as part of an argument list to a function call
     * appearing in a CSV spreadsheet cell formula
     *
     * @param String $cell_expression cell formula to evaluate
     * @param Number $location start offset in cell expression
     *      of range expression that this method needs to
     *      evaluate
     * @return Array [new_loc, array of range cells]
     */
    p.evalRangeExpression = function(cell_expression, location)
    {
        var out = [location, false];
        if (location >= cell_expression.length) {
            return out;
        }
        location = self.skipWhitespace(cell_expression, location);
        var rest = cell_expression.substring(location, location +
            self.max_name_length);
        value = rest.match(/^([A-Z]+)(\d+)\s*\:\s*([A-Z]+)(\d+)/);
        var col_flag = false;
        if (value !== null && ((col_flag = (value[1] == value[3])) ||value[2] ==
            value[4])) {
            out[0] = self.skipWhitespace(cell_expression, location +
                value[0].length);
            out[1] = [];
            if (col_flag) {
                for (var i = Number(value[2]); i <= Number(value[4]); i++) {
                    var row_col = self.cellNameAsRowColumn("" + value[1] + i);
                    if (typeof eval_data[row_col[0] - 1] == 'undefined' ||
                        typeof eval_data[row_col[0] - 1][row_col[1]] ==
                        'undefined' ||
                        eval_data[row_col[0] - 1][row_col[1]] == 'NaN') {
                        out[1] = "NaN";
                        return out;
                    }
                    out[1].push(eval_data[row_col[0] - 1][row_col[1]]);
                }
            } else {
                var row_col1 = self.cellNameAsRowColumn("" + value[1]+value[2]);
                var row_col2 = self.cellNameAsRowColumn("" + value[3]+value[4]);
                var i = row_col1[0];
                for (var j = row_col1[1]; j <= row_col2[1]; j++) {
                    if (typeof eval_data[i - 1] == 'undefined' ||
                        typeof eval_data[i - 1][j] == 'undefined'
                        || eval_data[i - 1][j] == 'NaN'){
                        out[1] = "NaN";
                        return out;
                    }
                    out[1].push(eval_data[i - 1][j]);
                }
            }
        }
        return out;
    }
    /**
     * Used to parse expression of the form: -expr
     * appearing in a CSV spreadsheet cell formula
     *
     * @param String cell_expression cell formula to evaluate
     * @param Number location start offset in cell expression
     *      of negated expression that this method needs to
     *      evaluate
     * @return Array [new_loc, the value of sub-expression]
     */
    p.evalNegatedExpression = function(cell_expression, location)
    {
        var out = [location, false];
        if (location >= cell_expression.length) {
            return out;
        }
        location = self.skipWhitespace(cell_expression, location);
        if (cell_expression.charAt(location) == "-") {
            sub_out = self.evaluateCell(cell_expression, location + 1);
            if (sub_out[1] == 'NaN' || sub_out[1] == false) {
                return sub_out;
            }
            out[0] = self.skipWhitespace(cell_expression, sub_out[0]);
            out[1] = - sub_out[1];
            return out;
        }
        return out;
    }
    /**
     * Used to parse a integer or float expression
     * appearing in a CSV spreadsheet cell formula
     *
     * @param String cell_expression cell formula to evaluate
     * @param Number location start offset in cell expression
     *      of integer or float expression that this method needs to
     *      evaluate
     * @return Array [new_loc, the value of sub-expression]
     */
    p.evalNumericExpression = function(cell_expression, location)
    {
        var out = [location, false];
        if (location >= cell_expression.length) {
            return out;
        }
        location = self.skipWhitespace(cell_expression, location);
        var rest = cell_expression.substring(location, location +
            self.max_name_length);
        var value = rest.match(/^\-?\d+(\.\d*)?|^\-?\.\d+/);
        if (value !== null) {
            out[0] = self.skipWhitespace(cell_expression,location +
                value[0].length);
            out[1] = (value[0].match(/\./) == '.') ? parseFloat(value[0]) :
                parseInt(value[0]);
            return out;
        }
        return out;
    }
    /**
     * Used to parse an expression of the form letter sequence followed by
     * number sequence corresponding to the name of a spreadsheet cell
     * appearing in a CSV spreadsheet cell formula
     *
     * @param String cell_expression cell formula to evaluate
     * @param Number location start offset in cell expression
     *      of cell name expression that this method needs to
     *      evaluate
     * @return Array [new_loc, the value of sub-expression]
     */
    p.evalCellNameExpression = function(cell_expression, location)
    {
        var out = [location, false];
        if (location >= cell_expression.length) {
            return out;
        }
        location = self.skipWhitespace(cell_expression, location);
        var rest = cell_expression.substring(location, location +
            self.max_name_length);
        value = rest.match(/^[A-Z]+\d+/);
        if (value !== null) {
            out[0] = self.skipWhitespace(cell_expression,location +
                value[0].length);
            var row_col = self.cellNameAsRowColumn(value.toString().trim());
            out[1] = eval_data[row_col[0] - 1][row_col[1]];
        }
        return out;
    }
    /**
     * Used to parse a string expression, "some string" or 'some string',
     * appearing in a CSV spreadsheet cell formula
     *
     * @param String cell_expression cell formula to evaluate
     * @param Number location start offset in cell expression
     *      of string expression that this method needs to
     *      evaluate
     * @return Array [new_loc, the value of sub-expression]
     */
    p.evalStringExpression = function(cell_expression, location)
    {
        var out = [location, false];
        if (location >= cell_expression.length) {
            return out;
        }
        location = self.skipWhitespace(cell_expression, location);
        var rest = cell_expression.substring(location, location +
            self.max_name_length);
        var value = rest.match(/^\"([^\"]*)\"/);
        if (value === null) {
            value = rest.match(/^\'([^\']*)\'/);
        }
        if (value !== null) {
            out[0] = self.skipWhitespace(cell_expression,location +
                value[0].length);
            out[1] = value[1];
            return out;
        }
        return out;
    }
    /**
     * Returns the position of the first non-whitespace character after
     * location in the string (returns location if location is non-WS or
     * if no location found).
     *
     * @param String haystack string to search in
     * @param Number location where to start search from
     * @return Number position of non-WS character
     */
    p.skipWhitespace = function(haystack, location)
    {
        var next_loc = haystack.substring(location).search(/\S/);
        if (next_loc > 0) {
            location += next_loc;
        }
        return location;
    }
    /**
     * Converts a decimal number to a base 26 number string using A-Z for 0-25.
     * Used where drawing column headers for spreadsheet
     * @param Number number the value to convert to base 26
     * @return String result of conversion
     */
    p.letterRepresentation = function(number)
    {
        var pre_letter;
        var out = "";
        do {
            pre_letter = number % 26;
            number = Math.floor(number/26);
            out += String.fromCharCode(65 + pre_letter);
        } while (number > 25);
        return out;
    }
    /**
     * Given a cell name string, such as B4, converts it to an ordered pair
     * suitable for lookup in the spreadsheets data array. On B4,
     * [3, 1] would be returned.
     *
     * @param String cell_name name to convert
     * @return Array ordered pair corresponding to name
     */
    p.cellNameAsRowColumn = function(cell_name)
    {
        var cell_parts = cell_name.match(/^([A-Z]+)(\d+)$/);
        if (cell_parts == null) {
            return null;
        }
        var column_string = cell_parts[1];
        var len = column_string.length;
        var column = 0;
        var old_column = 0;
        var shift = 26;
        for (var i = 0; i < len; i++) {
            column += old_column + (column_string.charCodeAt(i) - 65);
            old_column = shift * column;
        }
        return [parseInt(cell_parts[2]), column];
    }
    /**
     * Callback for click events on spreadsheet. Determines if the event
     * occurred on a spreadsheet cell. If so, it opens a prompt for a
     * new value for the cell and updates the cell and the associated form
     * hidden input value.
     * @param Object event click event object
     */
    p.updateCell = function (event) {
        var type = (event.target.innerHTML == "+") ? 'add' :
            (event.target.innerHTML == "-") ? 'delete' :'cell';
        var target = (type == 'cell') ? event.target :
            event.target.parentElement;
        var row = target.parentElement.rowIndex - 1;
        var column = target.cellIndex - 1;
        var length = data.length;
        var width = data[0].length;
        if (row >= 0 && column >= 0) {
            self.makeUpdatableCell(event.target, row, column,
                data[row][column]);
        } else if (type == 'add' && row == -1 && column >= 0) {
            for (var i = 0; i < length; i++) {
                for (var j = width; j > column + 1; j--) {
                    data[i][j] = data[i][j-1];
                }
                data[i][column + 1] = "";
            }
            data_elt = document.getElementById(self.data_id);
            data_elt.value = JSON.stringify(data);
            self.draw();
        } else if (type == 'add' && row >= 0 && column == -1) {
            data[length] = [];
            for (var i = length; i > row + 1; i--) {
                for (var j = 0; j < width; j++) {
                    data[i][j] = data[i - 1][j];
                }
            }
            for (var j = 0; j < width; j++) {
                data[row + 1][j] = "";
            }
            data_elt = document.getElementById(self.data_id);
            data_elt.value = JSON.stringify(data);
            self.draw();
        } else if (type == 'delete' && row == -1 && column >= 0) {
            for (var i = 0; i < length; i++) {
                for (var j = column ; j < width - 1; j++) {
                    data[i][j] = data[i][j + 1];
                }
                data[i].pop();
            }
            data_elt = document.getElementById(self.data_id);
            data_elt.value = JSON.stringify(data);
            self.draw();
        } else if (type == 'delete' && row >= 0 && column == -1) {
            for (var i = row; i < length - 1; i++) {
                    data[i] = data[i + 1];
            }
            data.pop();
            data_elt = document.getElementById(self.data_id);
            data_elt.value = JSON.stringify(data);
            self.draw();
        }
        event.stopPropagation();
        event.preventDefault();
    }
    /**
     * Replaces table cell in spreadsheet with an input tag which can be
     * edited, sets up events for handling when cell update is over.
     *
     * @param Object cell the td tag object for the cell
     * @param Number row the row in the spreadsheet the cell corresponds to
     * @param Number col the column in the spreadsheet the cell corresponds to
     * @param String cell_value the non HTMl contents of the cell
     */
    p.makeUpdatableCell = function (cell, row, column, cell_value) {
        cell.innerHTML = "<input id='update-cell' value=" +
            JSON.stringify(cell_value.replace(/\"/g, '&quot;')) +
            " data-row='" + row + "' data-column='" + column + "' " +
            "style='background:lightgray; width:1in; text-align:inherit'/>";
        document.getElementById('update-cell').focus();
        elt('update-cell').addEventListener("blur", self.finishCellUpdate,
            false);
    }
    /**
     * Called after a spreadsheet cell that was being edited becomes blurred.
     * It is used to update the value of the cell in the data array, and to
     * redraw and re-evaluate the spreadsheet.
     *
     * @param event the Javascript blur event that signaled cell editing was
     *      was complete
     */
    p.finishCellUpdate = function (event) {
        var cell = document.getElementById('update-cell');
        if (cell == null) {
            return;
        }
        cell.removeEventListener("blur", self.finishCellUpdate,
            false);
        var row = cell.getAttribute('data-row');
        var column = cell.getAttribute('data-column');
        var cell_value = cell.value;
        if (cell_value != null) {
            data[row][column] = cell_value;
            data_elt = document.getElementById(self.data_id);
            data_elt.value = JSON.stringify(data);
            self.draw();
            data[row][column] = cell_value.replace(/\'/g, '&#39;')
            data_elt.value = JSON.stringify(data);
        }
    }
    if (this.mode == 'write') {
        container.addEventListener("click", self.updateCell, true);
    }
}
