How to Create a Spreadsheet component using HTML and JavaScript

aliyu-s (50)in #utopian-io • 7 years ago (edited)

What Will I Learn?

Write here briefly the details of what the user is going to learn in a bullet list.

You will learn about HandsonTable UI spreadsheet component
You will learn how to integrate a spreadsheet in your web application
You will learn how to setup a simple web page for the spreadsheet integration

Requirements

You must have basic knowledge on how to create a website
You must have basic JavaScript knowledge
You need Text Editor, browser and internet connection to download resources if necessary

Difficulty

Intermediate

Tutorial Contents

Introduction

HandsonTable Community Edition is an open source JavaScript Spreadsheet used in producing spreadsheet component for web applications. HandsonTable is a JavaScript and HTML 5 User Interface (UI) component that you can integrate in your applications. It supports Vue, React, Angula and Polymer.

For this tutorial, we are going to design a simple web page and integrate the HandsonTable spreadsheet using HTML5 and JavaScript. There are many ways where HandsonTable can be such as editing a database, analyzing sales or financial reports, data management etc.

There are various features that HandsonTable provide such as sorting of data, validating data, conditional formatting, freezing, resizing and moving rows/columns, adding comments to cells etc. for this tutorial, we are going to make use of data sorting, resizing rows/columns, adding columns and dragging cells to populate data.

PREVIEW OF FINAL RESULT

STEPS

Setting up Web page
Follow the steps below carefully to achieve the desired goal;

Launch your text editor and create a file named “index.html”.
Declare the standard HTML5 elements used in defining a document;

 <!doctype html>
<html lang="en">
  <head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
    <meta name="description" content="">
    <meta name="author" content="">
    <title>Utopian-io – HandsonTable Spreadsheet Web Component</title>
  </head>
<body>
    
</body>
</html>

We will use bootstrap’s HTML/CSS framework to set up the web page. I downloaded and included it locally. Visit the website https://getbootstrap.com/ to also download or use their CDN links.
We add the following piece of code from the picture below within the body tag to setup the navigational menu and main body of the web page.

Preview of the page in browser

Integrating HandsonTable Spreadsheet

We are now going to integrate the HandsonTable Spreadsheet into our web page. First, head over to the HandsonTable Community Edition website https://handsontable.com/community-download to download it. We have the option of downloading the Zip Archive, installation through npm or including the CDN links directly into our web page.

For this tutorial, we are going to use the CDN links. The CSS and JS links are;

CSS:

<link href="https://cdnjs.cloudflare.com/ajax/libs/handsontable/0.35.1/handsontable.min.css" rel="stylesheet">

JS:

<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/handsontable/0.35.1/handsontable.min.js"></script>

Copy the CSS CDN link and paste within the <head> tag after bootstrap and the JS after the bootstrap JS file at the bottom of the page.

Adding the Spreadsheet component

To add the spreadsheet, we need to create a <div> tag within the main body of the page.

<div id="employee">
</div>

Add the following JavaScript code to initialize the spreadsheet;

<script type="text/javascript">
var data = [{
  "first_name": "Martita",
  "last_name": "Nutkins",
  "email": "mnutkins0@nifty.com",
  "gender": "Female",
  "ip_address": "80.215.109.49"
}, {
  "first_name": "Coletta",
  "last_name": "Carnall",
  "email": "ccarnall1@umich.edu",
  "gender": "Female",
  "ip_address": "81.195.227.229"
} ]
</script>

The piece of code above is JSON Data. The var data stores the list of employees. The JSON data was generated using https://www.mockaroo.com/. A tool used in generating mockup data.

var container = document.getElementById(employee);
        var hot = new Handsontable(container, {
          data: data,
          rowHeaders: true,
          colHeaders: false,
          colHeaders: ['Firts name', 'Last name', 'email', 'Gender', 'IP Address'],
          columnSorting: true,
          sortIndicator: true
        });

Add the following piece of code above after var data is enclosed to initialize and configure the spreadsheet;
Meaning of the HandsonTable options we used;
- data: data, = reference the variable data we store the JSON objects
- rowHeaders: true, = to display a counter for the rows populated
- colHeaders: false, = this disables the default spreadsheet column header of ‘A-Z’
- columnSorting: true, = this enables data sorting
- sortIndicator = true = this displays an arrow on a sorted column
For more options check the handsonTable Spreadsheet documentation

Preview
It’s working…

As you can see the arrows highlighting the key areas we worked on. The top arrow shows the email was sorted and displaying the sortIndicator. The second arrow shows that two rows were drag and data is being inputted into a cell.

Source Code

The full source code;

<!DOCTYPE html>
<html lang="en">
  <head>
      <meta charset="utf-8">
      <title>Utopian-io | How to create a Spreadsheet component using HTML and JavaScript</title>
      <link href="css/bootstrap.min.css" rel="stylesheet">
      <link href="https://cdnjs.cloudflare.com/ajax/libs/handsontable/0.35.1/handsontable.min.css" rel="stylesheet">
  </head>
<body style="padding-top: 5rem;">
  <nav class="navbar navbar-expand-md navbar-dark bg-dark fixed-top">
    <div class="container">
      <a class="navbar-brand" href="#">Utopian-io</a>
      <button class="navbar-toggler" type="button" data-toggle="collapse" data-target="#navbarsExampleDefault" aria-controls="navbarsExampleDefault" aria-expanded="false" aria-label="Toggle navigation">
        <span class="navbar-toggler-icon"></span>
      </button>
      <div class="collapse navbar-collapse" id="navbarsExampleDefault">
        <ul class="navbar-nav mr-auto"> 
          <li class="nav-item active">
            <a class="nav-link" href="#">Home <span class="sr-only">(current)</span></a>
          </li>  
          <li class="nav-item">
            <a class="nav-link" href="#">Get Started</a>
          </li> 
          <li class="nav-item">
            <a class="nav-link" href="#">Community</a>
          </li>     
        </ul>                   
        <a class="btn btn-success my-2 my-sm-0" href="#"><i class="fa fa-sign-in"></i> Login</a>                   
      </div>
    </div>
    </nav>
    <main role="main" class="container">
      <h1>Employee Database</h1>
        <p class="lead">Managing employee database using Spreadsheet</p>    
      <div class="container welcome-div">      
           <div id="employee">
           </div>
      </div>    
    </main> 
    <script src="js/jquery.min.js"></script>
    <script src="js/bootstrap.min.js"></script>
    <script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/handsontable/0.35.1/handsontable.min.js"></script>  
    <script type="text/javascript">
          var data = [{
          "first_name": "Martita",
          "last_name": "Nutkins",
          "email": "mnutkins0@nifty.com",
          "gender": "Female",
          "ip_address": "80.215.109.49"
        }, {
          "first_name": "Coletta",
          "last_name": "Carnall",
          "email": "ccarnall1@umich.edu",
          "gender": "Female",
          "ip_address": "81.195.227.229"
        }, {
          "first_name": "Drusi",
          "last_name": "Meale",
          "email": "dmeale9@pcworld.com",
          "gender": "Female",
          "ip_address": "150.32.90.216"
        }]
        var container = document.getElementById('employee');
        var hot = new Handsontable(container, {
          data: data,
          rowHeaders: true,
          colHeaders: false,
          colHeaders: ['Firts name', 'Last name', 'email', 'Gender', 'IP Address'],
          columnSorting: true,
          sortIndicator: true
        });        
    </script>  
</body>
</html>

Curriculum

Follow more of my tutorials below;

Posted on Utopian.io - Rewarding Open Source Contributors

#tutorials #opensource #spreadsheet

7 years ago in #utopian-io by aliyu-s (50)

Sort:

roj (61) 7 years ago

Thank you for the contribution. It has been approved.

You can contact us on Discord.
[utopian-moderator]

$1.61

1 vote

[-]

utopian.tip (46) 7 years ago

Hey @roj, I just gave you a tip for your hard work on moderation. Upvote this comment to support the utopian moderators and increase your future rewards!

$0.00

1 vote

[-]

aliyu-s (50) 7 years ago

Thank you!

$0.00

[-]

steemitstats (47) 7 years ago

@aliyu-s, Approve is not my ability, but I can upvote you.

$0.00

[-]

adiveharry (29) 7 years ago

This is no doubt a valid contribution, can only imagine how much time you put into this. Good job 👍

$0.00

[-]

aliyu-s (50) 7 years ago

Thank you @adiveharry

$0.00

[-]

utopian-io (71) 7 years ago

Hey @aliyu-s I am @utopian-io. I have just upvoted you!

Achievements

You have less than 500 followers. Just gave you a gift to help you succeed!
Seems like you contribute quite often. AMAZING!

Suggestions

Contribute more often to get higher and higher rewards. I wish to see you often!
Work on your followers to increase the votes/rewards. I follow what humans do and my vote is mainly based on that. Good luck!

Get Noticed!

Did you know project owners can manually vote with their own voting power or by voting power delegated to their projects? Ask the project owner to review your contributions!

Community-Driven Witness!

I am the first and only Steem Community-Driven Witness. Participate on Discord. Lets GROW TOGETHER!

Vote for my Witness With SteemConnect
Proxy vote to Utopian Witness with SteemConnect
Or vote/proxy on Steemit Witnesses

Up-vote this comment to grow my power and help Open Source contributions like this one. Want to chat? Join me on Discord https://discord.gg/Pc8HG9x

$0.00