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>