PDA

View Full Version : Spreadsheet Issue



cenibyte
02-21-2011, 06:14 PM
Hello Everyone,

I am creating a simple Spreadsheet program and I am having issues calculating on a diagonal or if I skip cells. Here is my code below and I have also added an attachment of the entire project:

tablepage.js

// JavaScript Document

var tblRows = 20;
var tblColumns = 10;
var tblArray = new Array(tblRows);

var p = window.parent; // shortened alias for window.parent

// *************************************************
// event handler function called when the page loads
function initPage()
{
p.refAssignCellFromTextbox = assignCellFromTextbox;

for (var i = 0; i < tblArray.length; i++)
{
tblArray[i] = new Array(tblColumns);
for (var j = 0; j < tblArray[i].length; j++)
tblArray[i][j] = "0";
}

// for development purposes
recalculate();
}

// ******************************************
// traverse the 2d array looking for formulas
// and then recalculate cell values
function recalculate()
{
for (var i = 0; i < tblRows; i++)
{
for (var j = 0; j < tblColumns; j++)
{
// check to see if table element is a formula
if (tblArray[i][j].indexOf("=SUM") != -1)
{
// apply the formula for cell at row/column i/j
// you will need to write the code for calculateCell()
calculateCell(i, j);
}
}
}
}

// *********************************************************
// event handler function called when the user clicks a cell
function clickCell(cellRef)
{
thisRef = cellRef;

// check to see if this is a formula
var cellID = cellRef.id;

var row = parseFloat(cellID.substr(0, 3)) - 1;
var column = parseFloat(cellID.substr(3, 2)) - 1;

var cellValue = tblArray[row][column];
var tokenArray = getFormula(cellValue);

// if null... this isn't a formula
// otherwise it is
if (tokenArray != null)
p.assignTextboxFromArray(cellValue);
else
p.assignTextboxFromCell(cellRef);
}

// *******************************************************
// exported function called from the parent page to update
// a cell in the table from the text box
function assignCellFromTextbox(tblValue)
{
if (thisRef != undefined)
{
if (tblValue == "")
{
tblValue = "0";
}
var tokenArray = getFormula(tblValue);

// if null... this isn't a formula
// otherwise it is
if (tokenArray != null)
{
// start by updating the array
assignArray(thisRef.id, tblValue.toUpperCase());
}
else
{
// Not formula. Check for Numbers
// Check to see if the value is a number and not mixed with letters, especially if not a formula
if (!isFloat(tblValue))
{
parseValue = tblValue.replace(/[^0-9]/g, '');
tblValue = parseValue;
}
assignArray(thisRef.id, tblValue);
if (tblValue == "0")
thisRef.innerText = "";
else
thisRef.innerText = tblValue;
}

// always recalculate the spreadsheet
recalculate();
}
}

// ********************************************************
// determines if user entered a formula such as =SUM(A1:B2)
// returns an array with cell coordinates
function getFormula(tblValue)
{
var pattern = /[:|\(|\)]/;
var ar = tblValue.split(pattern);
var sum = ar[0].toUpperCase();

if (ar.length < 3)
return null;
else if (sum != "=SUM")
{
return null;
}
else
{
// too simplistic
return ar;
}
}

function assignArray(cellID, tblValue)
{
var row = parseFloat(cellID.substr(0, 3));
var column = parseFloat(cellID.substr(3, 2));

tblArray[row - 1][column - 1] = tblValue;
}

function calculateCell(row, column)
{
var tokenArray = getFormula(tblArray[row][column]);

if (tokenArray != null)
{
var fromColumn = tokenArray[1].substr(0, 1);
var fromRow = tokenArray[1].substr(1, tokenArray[1].length - 1);

var toColumn = tokenArray[2].substr(0, 1);
var toRow = tokenArray[2].substr(1, tokenArray[2].length - 1);

var fromRowIndex = parseFloat(fromRow) - 1;
var fromColIndex = fromColumn.charCodeAt(0) - 65;

var toRowIndex = parseFloat(toRow) - 1;
var toColIndex = toColumn.charCodeAt(0) - 65;

var sumTotal = 0;

for (var i = fromRowIndex; i <= toRowIndex; i++)
{
for (var j = fromColIndex; j <= toColIndex; j++)
{
if (isFloat(tblArray[i][j]))
sumTotal += parseFloat(tblArray[i][j]);
}
}

var cellID = fillField((row + 1).toString(), 3) + fillField((column + 1).toString(), 2);
document.getElementById(cellID).innerText = sumTotal;
}
}

function isFloat(s)
{
var ch = "";
var justFloat = "0123456789.";

for (var i = 0; i < s.length; i++)
{
ch = s.substr(i, 1);
if (justFloat.indexOf(ch) == -1)
return false;
}
return true;
}

function fillField(s,n)
{
var zeros = "0000000000";
return zeros.substring(0, n - s.length) + s;
}

spreadsheet.js


// JavaScript Document

var refAssignCellFromTextbox;
var nCharsAllowed = "ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890.=(): ";


// Event Handler
function initPage()
{
document.getElementById("TableFrame").style.top = "50px";
document.getElementById("TableFrame").src = "tablepage.html";
}

// Updates text field when a user clicks on a particular field from with the spreadsheet
function assignTextboxFromCell(cValue)
{
document.getElementById("fieldEntry").value = cValue.innerText;
document.getElementById("fieldEntry").focus();
}

// Assists tablepage.js with updating a value from a 2D array
function assignTextboxFromArray(aValue)
{
document.getElementById("fieldEntry").value = aValue;
document.getElementById("fieldEntry").focus();
}

// Used when a user presses a key to update text field
function filterText()
{
if (window.event.keyCode != 13)
{
if (!nCharOK(window.event.keyCode))
window.event.keyCode = null;
}
else
{
window.event.keyCode = null;
refAssignCellFromTextbox(document.getElementById("fieldEntry").value);
}
}

// Checks to see if character entered falls within our preset
function nCharOK(c)
{
var ch = (String.fromCharCode(c));
ch = ch.toUpperCase();

if (nCharsAllowed.indexOf(ch) != -1)
return true;
else
return false;
}

cenibyte
02-21-2011, 08:36 PM
Also if I try and total a bunch of totals I am presented with a 0

Any ideas?