WebDew

Developing a JavaScript Spreadsheet in Ten Minutes – Part 1

JavaScript Spreadsheet

In this tutorial we are going to create a simple JavaScript Spreadsheet app for any purpose. The code used in this tutorial can be a start point for your own project; however, we will develop an application that will look like an Office Excel program.

In the Part 1 we will create the base of our JavaScript Spreadsheet web application, and in the next parts we will add more functionality, such as formulas, importing/exporting .csv files, etc.

JavaScript Spreadsheet: the Start Point

For this project I’m going to use the Open Sans font, and you can get it for free from Google Fonts.

Firstly, we have to create three files: index.html, spreadsheet.js and main.css.

Secondly, make sure that you’ve linked the index.html file with the main.css and the javascript.js files:

<script src="spreadsheet.js"></script>
<link rel="stylesheet" href="main.css">
<link href="https://fonts.googleapis.com/css?family=Open+Sans" rel="stylesheet">

Finally, add some controls and the main part which will be populated with our JavaScript Spreadsheet:

<!-- Header contains control buttons -->
<header>
    <button onclick="saveData(); return false;">Save</button>
    <button onclick="loadData(); return false;">Load</button>
    <button onclick="clearData(); return false;">Clear</button>
</header>

<!-- Main contains the spreadsheet -->
<main id="spreadsheet"></main>

Good! The index file is ready! Let’s move to the spreadsheet.js file.

JavaScript Spreadsheet: the Base

In our example we will have the fixed amount of columns and rows, so create in the .js file two constants:

const ROWS = 20; // Number of rows
const COLUMNS = 10; // Number of columns

I suppose that we want our application to build the spreadsheet as soon as possible, am I right? If yes, we need to add the event listener:

// Create the spreadsheet when the DOM is loaded
window.addEventListener('DOMContentLoaded', buildTable);

And now let’s have a fun with the function that builds the table for us. Hopefully, the comments in the code will help you to understand what this function does. But anyway, always feel free to ask any questions in the comments at the end of this article!

/*
 * This function creates the Excel Spreadsheet inside
 * the element with the "spreadsheet" ID
 */
function buildTable() {
    // Get the spreadsheet element
    let spreadsheet = document.getElementById('spreadsheet');

    // Create the table
    let table = document.createElement('table');

    // Build the column headers
    let columnHeader = table.createTHead();
    let columnHeaderRow = columnHeader.insertRow(0);

    // Insert the "corner" cell
    let corner = document.createElement('th');
    corner.setAttribute('id', 'corner');
    columnHeaderRow.appendChild(corner);

    // Create the column headers for each row
    for(let i = 1; i <= COLUMNS; i++) {
        let header = document.createElement('th');

        // We use 64 because the variable i starts from 1
        let text = document.createTextNode(String.fromCharCode(i + 64));

        // Insert the text node into the <th>
        header.appendChild(text);

        // Insert the <th> into the row
        columnHeaderRow.appendChild(header);
    }

    // Create <tbody> element
    let tableBody = document.createElement('tbody');

    // Create the rows
    for(let i = 1; i <= ROWS; i++) {
        // Create the <tr> element.
        let row = tableBody.insertRow(-1);

        // Insert the row number
        row.insertCell(-1).innerText = i;

        // Create the columns for each row
        for(let j = 1; j <= COLUMNS; j++) {
            let cell = row.insertCell(-1);

            // Create the input in each cell
            let input = document.createElement('input');

            // Assign the id for each input. "ROW_COLUMN" format is used
            input.setAttribute('id', i + '_' + j);

            // Insert the input in each cell
            cell.appendChild(input);
        }
    }

    // Insert the <tbody> into the table
    table.appendChild(tableBody);

    // Insert the table into the element with the spreadsheet ID
    spreadsheet.appendChild(table);
}

When you refresh the page, you’ll probably see the poorly designed table with lots of inputs. Don’t worry because we will add some styling at the end of this article, but now we need to add the data clear function. This function will clear all input values:

// Clear all data from the spreadsheet
function clearData() {
    // For each row...
    for(let i = 1; i <= ROWS; i++) {
        // Clean each cell in the selected row
        for (let j = 1; j <= COLUMNS; j++) {
            document.getElementById(i + "_" + j).value = '';
        }
    }
}

JavaScript Spreadsheet: Load and Save

For saving function we need to have a browser supporting the HTML5 Local Storage. To simplify the task of saving, we need to write a helper function that will save all input values into the JSON format.

// Save all cell values into the JSON format, and return the JSON array back
function spreadsheetToJSON() {
    let json = [];

    // For each row
    for(let i = 1; i <= ROWS; i++) {
        json[i] = [];

        // Save each cell in the selected row
        for (let j = 1; j <= COLUMNS; j++) {
            json[i][j] = document.getElementById(i + "_" + j).value;
        }
    }

    return JSON.stringify(json);
}

And now we can move to the saveData() function:

// Save all cell values in JSON format to the HTML5 local storage
function saveData() {
    // Check that HTML5 Local Storage is supported by browser
    if(typeof Storage === 'undefined') {
        alert('HTML5 Local Storage is not supported by your browser!');
    } else {
        // save the JSON data to the local storage
        localStorage.setItem('spreadsheetData', spreadsheetToJSON());

        alert('Saved!');
    }
}

And the last thing is a loadData() function, but as we want our application to load the previously saved data, we need to add the event listener.

// Load previous data from Local Storage
window.addEventListener('DOMContentLoaded', loadData);

// Load the previous spreadsheet data from the local storage
function loadData() {
    // Check that HTML5 Local Storage is supported by browser
    if(typeof Storage === 'undefined') {
        alert('HTML5 Local Storage is not supported by your browser!');

        return false;
    }

    // Check that data exists
    if(localStorage.getItem('spreadsheetData') !== null) {
        // Decode the JSON data
        let values = JSON.parse(localStorage.getItem('spreadsheetData'));

        // Once data is decoded into the arrays, it's time to fill inputs with values
        for(let i = 1; i <= ROWS; i++) {
            // Fill each cell
            for (let j = 1; j <= COLUMNS; j++) {
                document.getElementById(i + "_" + j).value = values[i][j]; // Values
            }
        }
    }
}

JavaScript Spreadsheet: the Styling

I’m not going to write much about the styling because everything is pretty simple. Here for you we have a main.css file that will make your app looks like an JavaScript Excel program.

/* Remove the margin and set the default font */

body, html
{
    margin: 0;
    font: 100%/1.4 'Open Sans', Arial, sans-serif;
}

/* HEADER */

header
{
    background: #217346;
    height: 50px;
    width: 100%;
    line-height: 50px;
}

/* HEADER: Buttons */

header button
{
    font-size: 105%;
    height: 50px;
    color: #fff;
    background: none;
    border: none;
    padding: 0 15px;
    outline: none;

}

header button:hover
{
    transition: background 0.15s;
    -webkit-transition: background 0.15s;
    background: #195936;
}

/* MAIN TABLE */

main table
{
    border-collapse: collapse;
    table-layout: fixed;
    width: 100%;
}

/* MAIN TABLE: column headers (A-J) */

#corner
{
    background: #b4b4b4;
    width: 30px;
}

#corner, #spreadsheet th
{
    border: 1px solid #999999;
    border-top: none;
}

#spreadsheet th:not(#corner)
{
    background: #e6e6e6;
    font-weight: normal;
}

/* MAIN TABLE: Row numbers */

#spreadsheet tr td:first-child
{
    background: #e6e6e6;
    border: 1px solid #999999;
    border-left: none;
    text-align: center;
}

/* MAIN TABLE: inputs */

#spreadsheet td
{
    border: 1px solid #d4d4d4;
    padding: 0;
}

#spreadsheet td input
{
    border: none;
    outline: none;
    padding-left: 2px;
    width: 100%;
    height: 25px;
    box-sizing: border-box;
}

#spreadsheet td input:focus
{
    background: #fafafa;
    border: 1px solid #217346;
}

JavaScript Spreadsheet: Final Thoughts

There is nothing hard in developing the JavaScript spreadsheet application, and we’ve just proved it.

In the next tutorial we will add some new functions to this spreadsheet, so it will become even more functional.

You can download the source code from this tutorial here.

1 Comments

  1. Mike says:

    Your result looks so nice, I’m definitely using this!

Leave a Reply