A Simple Spreadsheet in JavaScript

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
This entry was posted in Uncategorized and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s