The Definitive Guide to JavaScript Spreadsheet Components

Nearly every business application requires the display and/or editing of tabular data, whether that data comes from a database, external/internal files or parties, or even Microsoft Excel workbooks. The requirement is simple — allow this data to be accessed and/or edited. From here, there are several options to consider regarding your application and the requirements of your users. This blog will provide you with all necessary information about JavaScript Spreadsheets, including:

• Do you need a Grid or a Spreadsheet?
• Client-Side or Server-Side?
• An Overview of JavaScript and Major Frameworks
• Key Features of JavaScript Spreadsheet Components
• Spreadsheet Use Cases
• Adding a JavaScript Spreadsheet and Ribbon to a JavaScript Application

Grids vs. Spreadsheets

Grids and spreadsheets are both tools used for organizing and presenting data, but they have distinct characteristics.

Grids

• A grid is a basic structure that consists of rows and columns.
• Grids make it easy to bind to a singular data source to display tabular data.
• Formatting and other customizations are usually limited to the row and column levels, which is ok for several types of data presentation needs.

Spreadsheets

• Spreadsheets are designed for data manipulation and analysis.
• They use a grid-like structure where each cell can contain data, formatting, formulas, functions, and more.
• Spreadsheets allow users to perform calculations, create charts, and analyze data.
• They are commonly used for financial modeling, project management, data tracking, and other similar business applications.

In summary, grids are more general-purpose structures, while spreadsheets are specifically designed for data management and analysis.

Now, the most important question when determining the best component to use for your application is: Do you need a grid or a spreadsheet?

You should use a grid if:

• You want to work with tabular data
• You want to bind to a singular data source
• The fields of a data source are not going to change
• You only want to edit existing data or add new entries (rows) to it

A spreadsheet would be a better fit if:

• You want to provide an Excel-like spreadsheet UI and features for your users
• You need to import and/or export native Excel files
• You want to perform calculations on data and change cell values based on those calculations
• You need to allow your users to be able to better analyze the data
• You need to present many familiar UI features of Excel, such as charts, tables, sparklines, conditional formatting, and other similar features

Client-Side and Server-Side

Spreadsheet components can come in many different forms, and the one you use depends on your application needs. If you need a client-side spreadsheet where the calculations and rendering are done within a web browser, then a JavaScript spreadsheet component could be the best fit. Alternatively, if your app is a stand-alone application that runs on a client’s desktop, then something like a WinForms spreadsheet control could be better.

On the other hand, you might not need a UI and want to keep calculations and the bulk of your code running on the server side of your application, only sending what you need to your client’s machines. In this case, a .NET or Java spreadsheet document API would be the best option.

Overview of JavaScript and Major Frameworks

JavaScript

The landscape of web development presents many options for a developer in terms of choosing a language, an application style, front-end and back-end setups, and much more. One of these languages is known as JavaScript, a scripting language for the web. It runs client-side in a web browser and is lightweight, single-threaded, and cross-platform.

Angular

Built on TypeScript, Angular is a development platform consisting of a component-based framework, a collection of libraries, and a suite of developer tools. The components on which Angular is based have a decorator to define configuration options and a TypeScript class that defines the behavior of said components. Each component also has a template that determines how it will be rendered to the DOM.

React

Building user interfaces out of individual components is simple and easy with the React framework. These components are JavaScript functions that can be written with code and markup. They receive data and then return what will appear in the browser window. User interaction will cause React to automatically update the screen to match the new data.

Vue

Another JavaScript framework is Vue, a declarative, component-based programming framework. The two core features of the framework are its declarative rendering and reactivity. It builds on top of standard HTML, CSS, and JavaScript and focuses on performance and lightweight implementation. It also covers most of the common features that a developer would need for front-end development.

Key Features of JavaScript Spreadsheet Components

Familiar UI

Users of a JavaScript spreadsheet are accustomed to a specific user interface when working with spreadsheets, so a JavaScript version should provide that same experience so that the customer doesn’t have to learn a new UI, which also helps to lessen the user’s learning curve. This should include the standard spreadsheet controls, context menus, and even a ribbon UI for the customer to interact with and manipulate a spreadsheet.

Excel Import and Export

JavaScript spreadsheets should be able to open and save native Excel .XLSX files, or other popular formats, like CSV. Users should be able to load any existing Excel file they have, make changes, and then save those changes back to an Excel file. An Excel file should also be able to be password-protected should a requirement ask for it.

Calculation Engine

A major component of any spreadsheet is a calculation engine, which should provide hundreds of formulas to calculate data in a spreadsheet. These functions should be the same as those present in Excel and support built-in formulas, custom formulas, array formulas, and asynchronous functions. A user should be able to enter these formulas in a cell and provide feedback on the syntax. Then, the calculation engine should evaluate arithmetic expressions and return the resulting value(s) in a cell or cell range.

Charts and Shapes

With JavaScript spreadsheets, charts and sparklines represent data from a spreadsheet in a graphical format. They’re extensively used in data analytics, particularly for making sense of large amounts of data. These charts can include Column, Line, Pie, Area, Bar, XYScatter, Stock, Combo, Radar, Sunburst, Treemap, and Funnel.

Shapes, on the other hand, are graphics that can be used to convey different sorts of information, such as pictures or diagrams. In some cases, shapes can be bound to specific data to affect properties like size, shape, color, etc.

Tables and PivotTables

In some cases, the data in a spreadsheet might be a bit overwhelming to understand or analyze, which is where tables and PivotTables come in handy. Tables can be created from ranges of cells in a worksheet, typically containing related data in rows and columns. Tables usually allow you to change themes and styles to make the data more readable.

PivotTables, on the other hand, are complex tables mainly used for data analysis. PivotTables can calculate, summarize, and present bulk data meaningfully in a spreadsheet. Data in a PivotTable can be broken down into categories and subcategories, sorting and filtering operations can be applied, and various layouts, styles, and themes can be applied.

Cell Types and Data Validation

A JavaScript spreadsheet component should also provide functionality to apply cell types and data validation to individual cells. Cell Types define the kind of information that appears in a cell, how that information can be entered and displayed, and how the user can interact with it. Examples include entering or displaying numbers, lists, date/time, checkboxes, pickers, or custom and error alerts.

Data Validation gives developers control over what types of data users can add to a spreadsheet.

Conditional Formatting

An important part of JavaScript spreadsheet components is the ability to change the style of a cell based on specific data in the cell. This is known as conditional formatting — a way to specify conditions that data in a cell must satisfy, which changes visual properties like the cell text color, background color, or styles. This can help users automatically highlight important information or easily spot trends in the data using the values they specify.

Filtering and Sorting Data

Another common feature of a JavaScript spreadsheet is the ability to filter and sort data, allowing users to analyze information quickly and effectively. Filters can be added to data to separate data based on different conditions, including numbers, text, date, color, and custom conditions. A user should be able to see only rows of data that fit the specified criteria while all other data is hidden.

Sorting data should organize cells by a particular order, either ascending or descending. The criteria for sorting could also be by the cell’s value, background color, or font color. The sorting should also support using multiple sorting keys, sorting by one row or column first, then another, and so on.

Workbooks and Worksheets

JavaScript spreadsheet components should have a concept of workbooks and worksheets. Workbooks are essentially a collection of worksheets (also known as spreadsheets) that consist of cells in which you can enter and calculate data to better organize it. You should also be able to reference data on a different worksheet.

Globalization

JavaScript spreadsheets should support multiple languages, which is where globalization comes in handy. With potential customers in different countries, it may be a requirement to ensure that spreadsheets and formula entry can be interacted with and displayed in multiple languages for those customers.

Spreadsheet Component Use Cases

JavaScript spreadsheet components should support different use cases to be useful in many different types of applications besides solely spreadsheets.

Spreadsheet

The spreadsheet is the primary use case for a JavaScript spreadsheet component. This includes workbooks and worksheets with calculations and tables. Users should be able to store, manipulate, and analyze data, as well as organize it for searching, sorting, calculation, and visualization.

Advanced Grid

Another use case for a JavaScript spreadsheet component is an advanced data grid. This is more useful for working with tabular data in which new fields will not be added. This is ideal for requirements like analyzing and managing data in a column-oriented setting.

Reports with PDF / Print

Users might also want to use a JavaScript spreadsheet component for reporting purposes, allowing users to display and analyze data in a user-friendly way. Users can add the business context needed to their data to display that data in an effective way. This should also include features like pagination, data filtering, sorting, and conditional formatting. After creating reports, a JavaScript spreadsheet component should support printing or exporting to PDF for appropriate sharing with other departments or organizations.

Dashboard

Another common use case for a JavaScript spreadsheet component is the ability to easily create dashboards, allowing users to visualize data using data visualizations such as charts and shapes. Examples include showcasing company KPIs, sales, and finance reports.

Data Entry/Input Forms

Users should also be able to create input forms with a JavaScript spreadsheet component to lay out different cells for data entry, lock other cells for form information, and then gather and save the user-inputted data. This can be used for many different types of forms, including insurance, business, and tax forms.

Adding a JavaScript Spreadsheet And Ribbon to a JavaScript Application

Now that we have a better understanding of the many features and use cases for the top JavaScript spreadsheet components, we can explore adding a JavaScript spreadsheet and ribbon into an application.

For this example, we will use the SpreadJS spreadsheet and its Designer Ribbon Component Add-On, which supports not only JavaScript but also Angular, Vue, and React. This will allow us to quickly support all the features and use cases in this article.

The finished project can be downloaded from here.

Project Setup

Let’s start by setting up the project and creating a simple HTML file. We will put all of our code in this singular file, which we can also put in a separate folder that will hold all required files.

Installing the Required Files

Now, we can run an npm install command in our project to install the required files:

npm install @mescius/spread-sheets @mescius/spread-sheets-shapes @mescius/spread-sheets-charts @mescius/spread-sheets-slicers @mescius/spread-sheets-print @mescius/spread-sheets-barcode @mescius/spread-sheets-pdf @mescius/spread-sheets-pivot-addon @mescius/spread-sheets-tablesheet @mescius/spread-sheets-ganttsheet @mescius/spread-sheets-formula-panel @mescius/spread-sheets-reportsheet-addon @mescius/spread-sheets-io @mescius/spread-excelio @mescius/spread-sheets-designer @mescius/spread-sheets-designer-resources-en

This will add the SpreadJS files into the application folder, which we then need to reference in the application code:

<script type="text/javascript" src="./node_modules/@mescius/spread-sheets/dist/gc.spread.sheets.all.min.js"></script>
<script type="text/javascript" src="./node_modules/@mescius/spread-sheets-shapes/dist/gc.spread.sheets.shapes.min.js"></script>
<script type="text/javascript" src="./node_modules/@mescius/spread-sheets-charts/dist/gc.spread.sheets.charts.min.js"></script>
<script type="text/javascript" src="./node_modules/@mescius/spread-sheets-slicers/dist/gc.spread.sheets.slicers.min.js"></script>

<script type="text/javascript" src="./node_modules/@mescius/spread-sheets-print/dist/gc.spread.sheets.print.min.js"></script>
<script type="text/javascript" src="./node_modules/@mescius/spread-sheets-barcode/dist/gc.spread.sheets.barcode.min.js"></script>
<script type="text/javascript" src="./node_modules/@mescius/spread-sheets-pdf/dist/gc.spread.sheets.pdf.min.js"></script>
<script type="text/javascript" src="./node_modules/@mescius/spread-sheets-pivot-addon/dist/gc.spread.pivot.pivottables.min.js"></script>
<script type="text/javascript" src="./node_modules/@mescius/spread-sheets-tablesheet/dist/gc.spread.sheets.tablesheet.min.js"></script>
<script type="text/javascript" src="./node_modules/@mescius/spread-sheets-ganttsheet/dist/gc.spread.sheets.ganttsheet.min.js"></script>
<script type="text/javascript" src="./node_modules/@mescius/spread-sheets-formula-panel/dist/gc.spread.sheets.formulapanel.min.js"></script>
<script type="text/javascript" src="./node_modules/@mescius/spread-sheets-reportsheet-addon/dist/gc.spread.report.reportsheet.min.js"></script>
<script type="text/javascript" src="./node_modules/@mescius/spread-sheets-io/dist/gc.spread.sheets.io.min.js"></script>
<script type="text/javascript" src="./node_modules/@mescius/spread-excelio/dist/gc.spread.excelio.min.js"></script>

<script type="text/javascript" src="./lib/scripts/gc.spread.sheets.designer.resource.en.17.0.9.min.js"></script>
<script type="text/javascript" src="./node_modules/@mescius/spread-sheets-designer/dist/gc.spread.sheets.designer.all.min.js"></script>

Integrating the Spreadsheet Ribbon

With SpreadJS, integrating a JavaScript spreadsheet ribbon is really simple. Now that we have the files referenced, all we need to do is create a DIV element in the HTML to hold the ribbon and then initialize the ribbon with a config. You also need to set a license key as well:

<div id="gc-designer-container" role="application"></div>

<script type="text/javascript">
 window.onload = function () {

  //Apply License
  //GC.Spread.Sheets.LicenseKey = 'sjs-distribution-key';
  //GC.Spread.Sheets.Designer.LicenseKey = 'designer-component-distribution-key';

  var config = GC.Spread.Sheets.Designer.DefaultConfig;
  var designer = new GC.Spread.Sheets.Designer.Designer(document.getElementById("gc-designer-container"), config);
 }
</script>

Opening an Excel File

With the SpreadJS Ribbon Component, no further code is needed to support opening an Excel file. Simply clicking on FILE > Import will open up an Excel file in SpreadJS and display that file in your JavaScript application.

Conclusion

There are several options to choose from when it comes to meeting the spreadsheet needs of customers. Utilizing a leading JavaScript spreadsheet component can solve most, if not all, of these requirements. Offer your users functionality like data manipulation, calculations, a familiar UI, data visualization, and Excel compatibility. Empower users with fast and intuitive spreadsheet experiences in the comfort of their web browser with a JavaScript spreadsheet component.

This article only scratches the surface of JavaScript spreadsheet components and solutions. We covered the background of JavaScript as well as a few of the common frameworks. We discussed the difference between client and server-side controls, grids vs spreadsheets, as well as the key features and use cases for JavaScript spreadsheets. We also presented a short example showing how easy it can be to add these types of features to your own applications using a popular JavaScript spreadsheet. Now, it is up to you to try some JavaScript spreadsheet tools, experiment with different features, and see how you can improve your application and user experience!