Running Spreadsheets in the Browser
Spreadsheets are used everywhere in business and just about everyone knows how to use them at least at the surface level. If you want to build functionality into your website or blog post without building out a complete system you could opt to embed a spreadsheet instead.
For the spreadsheet parser we will be building, we will use a table tag with a spreadsheet attribute and a data attribute that will contain the CSV data in the form of a two dimensional array. The size of the spreadsheet will be determined by a set of rows and cells attributes and the complete tag will look like this:
1<table
2 spreadsheet
3 cells="100"
4 rows="20"
5 data='[["Item","Cost"], ["Rent","1500"], ["Utilities","200"],["Groceries","360"], ["Transportation","450"], ["Entertainment","1203"], ["Total","=SUM(B2:B6)"]]'
6></table>
This will be parsed into a HTML table that looks like this:
If you click on B6, the cell will switch from containing the output value of the function assigned to it, to the function. All data can be edited and the function will reflect the updated values. The functionality of the spreadsheet be done using a library called formulajs. This removes a ton of work that would be needed if you were to build this from scratch.
Building the table
Below is the first part of what we need to create this, I added comments going step by step over the main functionality it. We will need to add more later, however, this code is the part where we generate the HTML for the table and lay the groundwork for the interactivity.
Creating the head
1// Collect the table elements with the spreadsheet attribute
2let sheets = document.querySelectorAll("table[spreadsheet]");
3
4for (let a = 0; a < sheets.length; a++) {
5 const sheet = sheets[a];
6 // Get the CSV data in the form of JSON
7 let contents = JSON.parse(sheet.getAttribute("data"));
8
9 let cells = parseInt(sheet.getAttribute("cells"));
10 let rows = parseInt(sheet.getAttribute("rows"));
11
12 // Create the head of the table
13 let head = document.createElement("thead");
14 head.appendChild(document.createElement("td"));
15 // Make the head fill the entire table
16 for (let b = 0; b < cells; b++) {
17 let label = document.createElement("td");
18 // This takes the position of the cell and turns it into an alphabetic selector
19 // For example 0 == "A" and 28 == "AB"
20 label.innerHTML = (
21 " abcdefghijklmnopqrstuvwxyz"[Math.floor(b / 26)] +
22 "abcdefghijklmnopqrstuvwxyz"[Math.floor(b % 26)]
23 )
24 .trim()
25 .toUpperCase();
26 head.appendChild(label);
27 }
28 sheet.appendChild(head);
29 /* ... */
First, we select all of the table elements with the spreadsheet attribute and parse the JSON data from the data attribute. Along with getting the JSON data, we will also need the dimensions of the table of the cells/rows attributes. After we have all the attributes we need to create the head of the table. The head will be the first row with alphabetic selectors To do this we can create a for loop that goes over each cell and create a td element inside of the thead. Generating the selector from the index of the cell is pretty simple, create two strings with all the letters. The first string needs a space in it so we can remove it using the .trim() method for the first 26 selectors. Then we clamp the index to 26 using the modulus operator to index the correct letter in the string.
Creating the body
The creation of the body is the same process as the creation of the head, except that we will have to go through the two-dimensional array that contains the data. After we create the tbody element, we need to loop through the rows and create a tr element for each one. The tr or table row element will contain all the cells in the table. The first cell in the row should be a number to make reading, which row you are on more easily.
Next we need to loop through the cells of the table and fill them with the data if the JSON object contains a value in its position. To do this we first have to create the cell, which is made up of a td or table data element and an input field. We give the input some extra data- attributes like data-x and data-y to help with building the reactivity of the spreadsheet. The data x/y attributes will help us locate the cell and map it to the appropriate selector when a function is called. The data-value attribute is the last attribute we will add, it's propose is to store the original value given in the sheet. This is only used when a cell contains a function, function cell will show the calculated value of the function until a user clicks on it. Once the cell is clicked, the value of the cell will be switched to the value of the data-value attribute. If you look at the code now, you might notice that we are setting the value to an empty string. This is because we still need to generate the cells that do not contain a value, we will give them a value after we add the event listeners.
1 /* ... */
2 // Create the body of the table
3 let body = document.createElement("tbody");
4 for (let b = 0; b < rows; b++) {
5 let row = document.createElement("tr");
6 // Create the first column of number for the table
7 let td = document.createElement("td");
8 td.innerHTML = b;
9 row.appendChild(td);
10 // Fill the cells that contain values
11 for (let c = 0; c < cells; c++) {
12 let td = document.createElement("td");
13 // Each cell is made up of a input element
14 let input = document.createElement("input");
15 input.type = "text";
16 // The data-x and data-y attributes will be used to process data later
17 input.dataset.x = c;
18 input.dataset.y = b;
19
20 // Initiate the input with nothing inside
21 input.dataset.value = "";
22 input.value = "";
23 /* ... */
Event listeners
1/* ... */
2input.addEventListener("focusin", (e) => {
3 e.target.value = e.target.dataset.value;
4});
5input.addEventListener("focusout", (e) => {
6 exe(e.target.parentNode.parentNode.parentNode.parentNode);
7});
8input.addEventListener("keyup", (e) => {
9 e.target.dataset.value = e.target.value;
10 e.target.parentNode.parentNode.parentNode.parentNode.setAttribute(
11 "data",
12 JSON.stringify(
13 parseSheet(e.target.parentNode.parentNode.parentNode.parentNode)
14 )
15 );
16});
17/* ... */
When building the reactivity of the spreadsheet, we will need to create events for three different cases: click-in, click-out, and editing. We will be using the focusin event to observe when the user clicks into the input. Once the user has clicked in, we need to change the value of it to the raw value. If it is a function it will be the function instead of the output of the function and if it is just a value then there will be no change. We do this by setting the target value to the value stored in the data-value attribute. When the user clicks out of the input we will detect it using focusout and then the exe function below will handle the rest.
1function exe(sheet) {
2 let functions = parseSheet(sheet, true)
3 .flat()
4 .filter((e) => e.dataset.value);
5 for (let i = 0; i < functions.length; i++) {
6 if (functions[i].dataset.value.trim()[0] == "=") {
7 let res = new Function(
8 `return ${functions[i].dataset.value
9 .replace(/[A-Z0-9]+\:[A-Z0-9]+/g, (e) => {
10 return JSON.stringify(query(sheet, e));
11 })
12 .replace(/[A-Z]+[0-9]+/g, (e) => {
13 return JSON.stringify(query(sheet, e));
14 })
15 .replace(/[A-Z]+\((.*?)\)/g, (e) => `formulajs.${e}`)
16 .slice(1)}`
17 )();
18 functions[i].value = res;
19 }
20 }
21}
The exe function takes the current table element (sheet) as a input, we get this value by getting the fourth removed parent of the input element. This will work every time because the event listener is on the input and the structure of the table looks like below:
1<table>
2 <tbody>
3 <tr>
4 <td>
5 <input />
6 </td>
7 </tr>
8 </tbody>
9</table>
1function parseSheet(sheet, elements = false) {
2 let inputs = [...sheet.querySelectorAll("input")].filter(
3 (e) => e.value.length > 0
4 );
5 let values = [];
6 inputs.forEach((e) => {
7 if (!values[parseInt(e.dataset.y)]) {
8 values[parseInt(e.dataset.y)] = [];
9 }
10 if (elements) {
11 values[parseInt(e.dataset.y)][parseInt(e.dataset.x)] = e;
12 } else {
13 values[parseInt(e.dataset.y)][parseInt(e.dataset.x)] = e.value;
14 }
15 });
16 return values;
17}
The exe function will need to get the contents of the table using the parseSheet function below. The parseSheet function has two arguments, the table and a boolean that if true will return the elements of the table and if false will return the values. For the exe function we want the elements so we can change the values of them. To keep this document from getting too long the parseSheet works by getting all of the input elements from the sheet variable, removing all empty elements and remapping them to a 2d array.
Once we have the cells that contain values flattened, we can loop through them and check to see if the value of the cell is a function by checking if the first character is an equals sign. If it is we will use the Function constructor to evaluate the statement we create next. The regex inside of the template literal replaces selectors i.e. B6 or B2:B6 within the function arguments with a JSON object containing the data in the sheet. Then it searches for function calls and i.e. SUM() with formula.SUM(), this allows us to use excel functions in javascript.
1 let res = new Function(
2 `return ${functions[i].dataset.value
3 .replace(/[A-Z0-9]+\:[A-Z0-9]+/g, (e) => {
4 return JSON.stringify(query(sheet, e));
5 })
6 .replace(/[A-Z]+[0-9]+/g, (e) => {
7 return JSON.stringify(query(sheet, e));
8 })
9 .replace(/[A-Z]+\((.*?)\)/g, (e) => `formulajs.${e}`)
10 .slice(1)}`
Within the replace statement, we use a function called query, this function will take the selector from the replace function and grabs the values off the table. It does this using the data-x/y attributes and mapping the selector to corresponding x/y coordinates. Once it has the values, we need to turn them into the correct type variable. This is done using the type function, if you want to look at it and the mapping function they will be below.
1function query(sheet, selector) {
2 let sSplit = selector.toLowerCase().split(":");
3 if (sSplit.length > 1) {
4 let start = mapSelector(sSplit[0]);
5 let end = mapSelector(sSplit[1]);
6 let selected = [];
7 for (let y = start[1]; y < end[1] + 1; y++) {
8 let row = [];
9 for (let x = start[0]; x < end[0] + 1; x++) {
10 row.push(
11 type(sheet.querySelector(`input[data-x="${x}"][data-y="${y}"]`).value)
12 );
13 }
14 selected.push(row);
15 }
16 return selected;
17 } else {
18 let xy = mapSelector(sSplit[0]);
19 return type(
20 sheet.querySelector(`input[data-x="${xy[0]}"][data-y="${xy[1]}"]`).value
21 );
22 }
23}
1function mapSelector(selector) {
2 let selectors = "abcdefghijklmnopqrstuvwxyz";
3 return [
4 selector
5 .replace(/[^a-z]/g, "")
6 .split("")
7 .map((e) => selectors.indexOf(e))
8 .reduce((e, a) => e + a, 0),
9 parseInt(selector.replace(/[a-z]/g, "")) - 1,
10 ];
11}
12
13function type(val) {
14 if (val == "false" || val == "true") {
15 return val == "true";
16 } else {
17 if (isNaN(val)) {
18 return `"${val}"`;
19 } else {
20 return parseFloat(val);
21 }
22 }
23}
Finishing off the original for loop, we assign the input element it's value if the contents array has a value in it and append everything to the table element.
1 /* ... */
2 // Check if the cell has a set value and populate it
3 if (contents[b]) {
4 if (contents[b][c]) {
5 input.dataset.value = contents[b][c];
6 input.value = contents[b][c];
7 }
8 }
9
10 td.appendChild(input);
11 row.appendChild(td);
12 }
13 body.appendChild(row);
14 }
15 sheet.appendChild(body);
16}
Don't forget to load FormulaJS!
1<script src="https://cdn.jsdelivr.net/npm/@formulajs/formulajs/lib/browser/formula.min.js"></script>
If you would like to have a self injecting version of this code, see this gist. This is the complete code put together from this tutorial, plus it will create a script tag and inject FormulaJS into the document.