The code shown below implements a simple spreadsheet in JavaScript. To see it in action, copy it into an .html file and open that file in a web browser that runs JavaScript.
As of this writing, the spreadsheet only fully supports one formula type: AddRange. I had also intended to implement saving and loading, but the current version does not incorporate these features. Some XML-conversion code has been included to facilitate this. See the previous post titled “Loading, Editing, and Saving a Text File in JavaScript” for more details on how saving and loading files might be implemented.
UPDATE 2017/03/28 – I have updated this program after a delay of several years. I started from scratch, so the new code is completely unrelated to the old code. Saving and loading works, as does a single formula, namely, “sum”. I have also added an online version, at the URL https://thiscouldbebetter.neocities.org/spreadsheet.html.
<html>
<body>
<!-- ui -->
<div id="divMain">
<div>
<label>Name:</label>
<input id="inputName" onchange="inputName_Changed(this);"></input>
<button onclick="buttonNew_Clicked();">New</button>
<button onclick="buttonSave_Clicked();">Save</button>
<button onclick="buttonLoad_Clicked();">Load:</button>
<input id="inputFileToLoad" type="file"></input>
</div>
<div>
<label>Size:</label>
<input id="inputSizeX" type="number" onchange="inputSizeX_Changed(this);"></input>
<label>x</label>
<input id="inputSizeY" type="number" onchange="inputSizeY_Changed(this);"></input>
</div>
<div>
<label>Cursor:</label>
<input id="inputCursorX" type="number" disabled="true"></input>
<label>x</label>
<input id="inputCursorY" type="number" disabled="true"></input>
<label>Expression:</label>
<input id="inputCursorExpression" disabled="true"></input>
</div>
<div style="border:1px solid">
<table id="tableData"></table>
</div>
</div>
<!-- ui ends -->
<script type="text/javascript">
// main
function main()
{
Globals.Instance.initialize();
Globals.Instance.spreadsheet.domUpdate();
}
// ui events
function buttonLoad_Clicked()
{
var spreadsheet = Globals.Instance.spreadsheet;
var inputFileToLoad = document.getElementById("inputFileToLoad");
var fileToLoad = inputFileToLoad.files[0];
if (fileToLoad == null)
{
alert("No file specified!");
}
else
{
FileHelper.loadFileAsText
(
fileToLoad, buttonLoad_Clicked_FileLoaded
);
}
}
function buttonLoad_Clicked_FileLoaded(fileName, fileContents)
{
var spreadsheetAsJSON = fileContents;
var spreadsheet = Serializer.deserialize(spreadsheetAsJSON);
Globals.Instance.spreadsheet = spreadsheet;
spreadsheet.cellsAllEvaluate();
spreadsheet.domUpdate();
}
function buttonNew_Clicked()
{
var spreadsheetNew = new Spreadsheet("Untitled.json", [[""]]);
Globals.Instance.spreadsheet = spreadsheetNew;
spreadsheetNew.domUpdate();
}
function buttonSave_Clicked()
{
var spreadsheet = Globals.Instance.spreadsheet;
var spreadsheetSerialized = Serializer.serialize(spreadsheet);
FileHelper.saveTextAsFile(spreadsheetSerialized, spreadsheet.name);
}
function inputCursorX_Changed(inputSizeX)
{
var spreadsheet = Globals.Instance.spreadsheet;
spreadsheet.cursorPos.x = parseInt(Math.floor(inputCursorX.value));
spreadsheet.domUpdate();
}
function inputCursorY_Changed(inputSizeY)
{
var spreadsheet = Globals.Instance.spreadsheet;
spreadsheet.cursorPos.y = parseInt(Math.floor(inputCursorY.value));
spreadsheet.domUpdate();
}
function inputName_Changed(inputName)
{
var spreadsheet = Globals.Instance.spreadsheet;
spreadsheet.name = inputName.value;
}
function inputSizeX_Changed(inputSizeX)
{
var spreadsheet = Globals.Instance.spreadsheet;
spreadsheet.size.x = parseInt(Math.floor(inputSizeX.value));
spreadsheet.resize();
spreadsheet.domUpdate();
}
function inputSizeY_Changed(inputSizeY)
{
var spreadsheet = Globals.Instance.spreadsheet;
spreadsheet.size.y = parseInt(Math.floor(inputSizeY.value));
spreadsheet.resize();
spreadsheet.domUpdate();
}
// classes
function Coords(x, y)
{
this.x = x;
this.y = y;
}
function FileHelper()
{
// static class
}
{
FileHelper.loadFileAsText = function(fileToLoad, callback)
{
var fileReader = new FileReader();
fileReader.onload = function(fileLoadedEvent)
{
var textFromFileLoaded = fileLoadedEvent.target.result;
callback(fileToLoad.name, textFromFileLoaded);
};
fileReader.readAsText(fileToLoad);
}
FileHelper.saveTextAsFile = function(textToSave, fileNameToSaveAs)
{
var textToSaveAsBlob = new Blob([textToSave], {type:"text/plain"});
var textToSaveAsURL = window.URL.createObjectURL(textToSaveAsBlob);
var downloadLink = document.createElement("a");
downloadLink.download = fileNameToSaveAs;
downloadLink.href = textToSaveAsURL;
downloadLink.click();
}
}
function Globals()
{
// do nothing
}
{
Globals.Instance = new Globals();
Globals.prototype.initialize = function()
{
this.spreadsheet = new Spreadsheet
(
"Demo.json",
[
[ "1", "2", "3", " ", "=sum:a1:c1" ],
[ "4", "5", "6", " ", "=sum:a2:c2" ],
[ "7", "8", "9", " ", "=sum:a3:c3" ],
[ " ", " ", " ", " ", " " ],
[ "=sum:a1:a3", "=sum:b1:b3", "=sum:c1:c3", "", "=sum:a5:c5" ],
]
);
this.spreadsheet.cellsAllEvaluate();
this.spreadsheet.domUpdate();
}
}
function Serializer()
{
// static class
}
{
Serializer.deserialize = function(stringToDeserialize)
{
var spreadsheetAsObject = JSON.parse(stringToDeserialize);
var spreadsheetName = spreadsheetAsObject.name;
var cellRowsAsStringArrays = spreadsheetAsObject.cellRows;
var spreadsheet = new Spreadsheet
(
spreadsheetName,
cellRowsAsStringArrays
);
return spreadsheet;
}
Serializer.serialize = function(spreadsheetToSerialize)
{
var cellRowsAsStringArrays = [];
var cellRows = spreadsheetToSerialize.cellRows;
for (var y = 0; y < cellRows.length; y++)
{
var cellRow = cellRows[y];
var cellRowAsStrings = [];
for (var x = 0; x < cellRow.length; x++)
{
var cell = cellRow[x];
var cellAsString = cell.expression;
cellRowAsStrings.push(cellAsString);
}
cellRowsAsStringArrays.push(cellRowAsStrings);
}
var spreadsheetAsObject =
{
"name" : spreadsheetToSerialize.name,
"cellRows" : cellRowsAsStringArrays
}
var spreadsheetAsJSON = JSON.stringify(spreadsheetAsObject);
return spreadsheetAsJSON;
}
}
function Spreadsheet(name, cellRowsAsStringArrays)
{
this.name = name;
this.cellRows = [];
for (var y = 0; y < cellRowsAsStringArrays.length; y++)
{
var cellRowColumnsAsStrings = cellRowsAsStringArrays[y];
var cellRow = [];
for (var x = 0; x < cellRowColumnsAsStrings.length; x++)
{
var cellExpression = cellRowColumnsAsStrings[x];
var cellPos = new Coords(x, y);
var cell = new SpreadsheetCell(cellExpression, cellPos);
cellRow.push(cell);
}
this.cellRows.push(cellRow);
}
this.size = new Coords(this.cellRows[0].length, this.cellRows.length);
this.cursorPos = new Coords(0, 0);
}
{
Spreadsheet.prototype.cellAtPosSetExpression = function(cellPos, expressionToSet)
{
var cell = this.cellRows[cellPos.y][cellPos.x]
cell.expression = expressionToSet;
cell.evaluate();
}
Spreadsheet.prototype.cellCodeToPos = function(cellCode)
{
var charCodeForA = "a".charCodeAt(0);
var columnIndex =
cellCode.charCodeAt(0)
- charCodeForA;
var rowIndex = parseInt
(
cellCode.substr(1)
) - 1;
var returnValue = new Coords
(
columnIndex,
rowIndex
);
return returnValue;
}
Spreadsheet.prototype.cellsAllEvaluate = function()
{
for (var y = 0; y < this.size.y; y++)
{
var cellRow = this.cellRows[y];
for (var x = 0; x < this.size.x; x++)
{
var cell = cellRow[x];
cell.evaluate(this);
}
}
}
Spreadsheet.prototype.cellsInRange = function(min, max)
{
var returnCells = [];
for (var y = min.y; y <= max.y; y++)
{
for (var x = min.x; x <= max.x; x++)
{
var cell = this.cellRows[y][x];
if (cell != null)
{
returnCells.push(cell);
}
}
}
return returnCells;
}
Spreadsheet.prototype.cellsInRangeCoded = function(minCoded, maxCoded)
{
var min = this.cellCodeToPos(minCoded);
var max = this.cellCodeToPos(maxCoded);
return this.cellsInRange(min, max);
}
Spreadsheet.prototype.resize = function()
{
var cellRowsNew = [];
for (var y = 0; y < this.size.y; y++)
{
var cellRowOld = this.cellRows[y];
if (cellRowOld == null)
{
cellRowOld = [];
}
var cellRowNew = [];
for (var x = 0; x < this.size.x; x++)
{
var cellOld = cellRowOld[x];
var cellNew;
if (cellOld == null)
{
cellNew = new SpreadsheetCell
(
"", // expression
new Coords(x, y) // pos
);
}
else
{
cellNew = cellOld;
}
cellRowNew.push(cellNew);
}
cellRowsNew.push(cellRowNew);
}
this.cellRows = cellRowsNew;
}
// dom
Spreadsheet.prototype.domUpdate = function()
{
var inputName = document.getElementById("inputName");
inputName.value = this.name;
var inputSizeX = document.getElementById("inputSizeX");
var inputSizeY = document.getElementById("inputSizeY");
inputSizeX.value = this.size.x;
inputSizeY.value = this.size.y;
var inputCursorX = document.getElementById("inputCursorX");
var inputCursorY = document.getElementById("inputCursorY");
inputCursorX.value = this.cursorPos.x;
inputCursorY.value = this.cursorPos.y;
var tableData = document.getElementById("tableData");
tableData.innerHTML = "";
var header = document.createElement("thead");
var tdOrigin = document.createElement("th");
header.appendChild(tdOrigin);
var charCodeForA = "a".charCodeAt(0);
for (var x = 0; x < this.size.x; x++)
{
var columnLetter = String.fromCharCode(charCodeForA + x);
var thColumnLetter = document.createElement("th");
thColumnLetter.innerHTML = columnLetter;
header.appendChild(thColumnLetter);
}
tableData.appendChild(header);
for (var y = 0; y < this.size.y; y++)
{
var cellRow = this.cellRows[y];
var tr = document.createElement("tr");
var tdRowNumber = document.createElement("td");
tdRowNumber.innerHTML = (y + 1);
tr.appendChild(tdRowNumber);
for (var x = 0; x < this.size.x; x++)
{
var cell = cellRow[x];
var td = document.createElement("td");
var inputForCell = document.createElement("input");
inputForCell.cell = cell;
inputForCell.value = cell.value;
inputForCell.onfocus = this.cellSelected;
inputForCell.onchange = this.cellValueChanged;
td.appendChild(inputForCell);
tr.appendChild(td);
}
tableData.appendChild(tr);
}
}
Spreadsheet.prototype.cellSelected = function(event)
{
var spreadsheet = Globals.Instance.spreadsheet;
var inputForCell = event.target;
var cell = inputForCell.cell;
var inputCursorX = document.getElementById("inputCursorX");
var inputCursorY = document.getElementById("inputCursorY");
var inputCursorExpression = document.getElementById("inputCursorExpression");
inputCursorX.value = cell.pos.x;
inputCursorY.value = cell.pos.y;
inputCursorExpression.value = cell.expression;
}
Spreadsheet.prototype.cellValueChanged = function(event)
{
var spreadsheet = Globals.Instance.spreadsheet;
var inputForCell = event.target;
var cell = inputForCell.cell;
var expressionNew = inputForCell.value;
cell.expression = expressionNew;
spreadsheet.cellsAllEvaluate();
inputForCell.value = cell.value;
spreadsheet.cellSelected(event);
}
}
function SpreadsheetCell(expression, pos)
{
this.expression = expression;
this.pos = pos;
this.value = null;
}
{
SpreadsheetCell.prototype.evaluate = function(spreadsheet)
{
var valueToSet;
if (this.expression.startsWith("=") == true)
{
var formulaParts = this.expression.substr(1).split(":");
var formulaType = formulaParts[0];
if (formulaType == "sum")
{
valueToSet = this.evaluate_Sum(spreadsheet, formulaParts);
}
else
{
valueToSet = "Error: Unrecognized formula: " + formulaType;
}
}
else
{
valueToSet = this.expression;
}
this.value = valueToSet;
}
SpreadsheetCell.prototype.evaluate_Sum = function(spreadsheet, formulaParts)
{
var valueToSet;
if (formulaParts.length != 3)
{
valueToSet = "Error: Sum: Wrong number of operands!";
}
else
{
var cellRangeStartCoded = formulaParts[1];
var cellRangeEndCoded = formulaParts[2];
var cellsInRange = spreadsheet.cellsInRangeCoded
(
cellRangeStartCoded, cellRangeEndCoded
);
var sumOfCellValuesSoFar = 0;
for (var i = 0; i < cellsInRange.length; i++)
{
var cell = cellsInRange[i];
var cellValue = cell.value;
var cellValueAsNumber = parseFloat(cellValue);
if (cellValueAsNumber != null)
{
sumOfCellValuesSoFar += cellValueAsNumber;
}
}
valueToSet = sumOfCellValuesSoFar;
}
return valueToSet;
}
}
// run
main();
</script>
</body>
</html>
Advertisements
Programming for the Beginner (on Amazon)
github.com/thiscouldbebetter
