The Definitive Guide to React Spreadsheets

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 requirements are simple — allow this data to be accessed and/or edited. From here, you now have several options to consider regarding your application and the requirements of your users. This blog will give you all of the information you need about React Spreadsheets, including:

• Do you need a Grid or a Spreadsheet?
• Client-Side or Server-Side?
• An Overview of React
• Key Features of React Spreadsheet Components
• Spreadsheet Use cases
• Adding a React Spreadsheet and Ribbon to a React 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, for the most important question when determining the best component to use for your application: 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 take many different forms, and the one you use depends on your application needs. If you need a client-side spreadsheet — one where the calculations and rendering are done within a web browser — a React spreadsheet component could be the best fit. Alternatively, if your app is a stand-alone application that runs on a client’s desktop, 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 clients’ machines. In this case, a .NET or Java spreadsheet document API would be the best option.

Overview of React

Building user interfaces out of individual components is simple and easy with the React framework. These components are React 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.

Key Features of React Spreadsheet Components

Familiar UI

Users of a React spreadsheet are accustomed to a specific user interface when working with spreadsheets, so a React version should provide that same experience so 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

React 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, 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 significant 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 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 React spreadsheets, charts and sparklines represent data from a spreadsheet in a graphical format. This is extensively used in data analytics, particularly for making sense of large amounts of data. These types of 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 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 typically 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 React 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 it 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 lets a developer have control over what types of data users can add to a spreadsheet.

Conditional Formatting

An important part of React 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 that change 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 React 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

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

Globalization

React 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 entries can be interacted with and displayed in multiple languages for those customers.

Spreadsheet Use Cases

React spreadsheets should support different use cases to be useful in many kinds of applications besides just spreadsheets.

Spreadsheet

The spreadsheet is the basic use case for a React 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 React spreadsheet component is that it should also function as an advanced datagrid. 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 React 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 React spreadsheet component should support printing or exporting to PDF for appropriate sharing with other departments or organizations.

Dashboard

Another common use case for a React 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 React spreadsheet component, 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 kinds of forms, including insurance, business, and tax forms.

Adding a React Spreadsheet and Ribbon to a React Application

Now that we have a better understanding of the many features and use cases for the top React spreadsheet components, we can look into adding a React 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 React but also Angular and Vue. This will allow us to quickly support all the features and use cases in this article.

Download the finished project to follow along.

Project Setup

We can start by setting up the project and creating the required files. We will enter all of our code in the generated App.js file. To start, open a command prompt and enter the following commands:

npm install -g create-react-app
create-react-app designercomponent
cd designercomponent
npm start

Installing the Required Files

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

npm install @mescius/spread-excelio @mescius/spread-sheets @mescius/spread-sheets-io @mescius/spread-sheets-barcode @mescius/spread-sheets-charts @mescius/spread-sheets-languagepackages @mescius/spread-sheets-pdf @mescius/spread-sheets-print @mescius/spread-sheets-shapes @mescius/spread-sheets-tablesheet @mescius/spread-sheets-pivot-addon @mescius/spread-sheets-designer @mescius/spread-sheets-designer-resources-en @mescius/spread-sheets-react @mescius/spread-sheets-designer-react

This will add the SpreadJS files into the application folder, which we then need to reference in the application code in src>App.js:

import '@mescius/spread-sheets-designer-resources-en';
import {Designer} from '@mescius/spread-sheets-designer-react';
import '@mescius/spread-sheets-designer/styles/gc.spread.sheets.designer.min.css'
import '@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css'

Integrating the Spreadsheet Ribbon

With SpreadJS, integrating a React spreadsheet ribbon is very simple. Now that we have the files imported, all we need to do is update the App function:

function App() {
   return (
      <Designer styleInfo = {{width: "1500px", height: '90vh'}}></Designer>
   );
}
export default App;

You can also apply the license key for the SpreadJS, ExcelIO, and Designer instances:

import React from 'react';
import '@mescius/spread-sheets-designer-resources-en';
import * as GC from '@mescius/spread-sheets';
import '@mescius/spread-sheets-designer';
import {Designer} from '@mescius/spread-sheets-designer-react';
import * as ExcelIO from "@mescius/spread-excelio";
 
var sjsLicense = "sjs-distribution-key";
GC.Spread.Sheets.LicenseKey = sjsLicense;
ExcelIO.LicenseKey = sjsLicense;
 
GC.Spread.Sheets.Designer.LicenseKey = " designer-component-distribution-key ";

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 an Excel file in SpreadJS and display that file in your React application.

Conclusion

There are several options to choose from when it comes to meeting the spreadsheet needs of customers. Utilizing a leading React spreadsheet component can solve most, if not all, of these requirements. Offer 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 React spreadsheet component.

This article only scratches the surface of React spreadsheet components and solutions. We reviewed the background of React 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 React 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 React spreadsheet. Now, it is up to you to try some React spreadsheet tools, experiment with different features, and see how to improve your application and user experience!