The Definitive Guide to Angular 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 Angular Spreadsheets, including:

• Do you need a Grid or a Spreadsheet?
• Client-Side or Server-Side?
• An Overview of Angular
• Key Features of Angular Spreadsheet Components
• Spreadsheet Use cases
• Adding an Angular Spreadsheet and Ribbon to an Angular 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 — an Angular 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 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 component’s behavior. Each component also has a template that determines how it will be rendered to the DOM.

Key Features of Angular Spreadsheet Components

Familiar UI
Users of an Angular spreadsheet are accustomed to a specific user interface when working with spreadsheets, so an Angular 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
Angular 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 Angular 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
An Angular 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 Angular 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 an Angular 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
Angular 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
Angular 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

Angular 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 an Angular 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 an Angular 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 an Angular 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, an Angular spreadsheet component should support printing or exporting to PDF for appropriate sharing with other departments or organizations.

Dashboard
Another common use case for an Angular 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 an Angular 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 an Angular Spreadsheet and Ribbon to an Angular Application

Now that we have a better understanding of the many features and use cases for the top Angular spreadsheet components, we can look into adding an Angular 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 Angular but also React 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 @angular/cli
ng new designercomponent --style css --ssr false
cd designercomponent

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-Angular @mescius/spread-sheets-designer-Angular

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

@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 an Angular spreadsheet ribbon is very simple. Now that we have the files imported, all we need to do is update the src>app>app.component.html file:

<designer [props]="props"></designer>

And the src>app>app.component.ts file:

import { Component, ViewEncapsulation } from '@angular/core';

import * as GC from '@mescius/spread-sheets';
import "@mescius/spread-sheets-shapes";
import "@mescius/spread-sheets-charts";
import "@mescius/spread-sheets-slicers";
import "@mescius/spread-sheets-print";
import "@mescius/spread-sheets-formula-panel";
import "@mescius/spread-sheets-barcode";
import "@mescius/spread-sheets-pdf";
import "@mescius/spread-sheets-pivot-addon";
import "@mescius/spread-sheets-tablesheet";
import "@mescius/spread-sheets-ganttsheet";
import "@mescius/spread-sheets-reportsheet-addon";
import "@mescius/spread-sheets-io";
import '@mescius/spread-sheets-designer-resources-en';
import '@mescius/spread-sheets-designer';
import * as GcDesigner from '@mescius/spread-sheets-designer';
import { DesignerModule } from '@mescius/spread-sheets-designer-angular';

@Component({
  selector: 'app-root',
  standalone: true,
  imports: [DesignerModule],
  templateUrl: './app.component.html',
  styleUrls: ['./app.component.css'],
  encapsulation: ViewEncapsulation.None
})
export class AppComponent {
  props = {
    styleInfo: "width: 100%; height: 98vh; margin-top: 10px"
  };
}

You can also apply the license key for the SpreadJS, ExcelIO, and Designer instances in that src>app>app.component.ts file:

import { Component, ViewEncapsulation } from '@angular/core';
import * as GC from '@mescius/spread-sheets';
import "@mescius/spread-sheets-shapes";
import "@mescius/spread-sheets-charts";
import "@mescius/spread-sheets-slicers";
import "@mescius/spread-sheets-print";
import "@mescius/spread-sheets-formula-panel";
import "@mescius/spread-sheets-barcode";
import "@mescius/spread-sheets-pdf";
import "@mescius/spread-sheets-pivot-addon";
import "@mescius/spread-sheets-tablesheet";
import "@mescius/spread-sheets-ganttsheet";
import "@mescius/spread-sheets-reportsheet-addon";
import "@mescius/spread-sheets-io";
import '@mescius/spread-sheets-designer-resources-en';
import '@mescius/spread-sheets-designer';
import * as GcDesigner from '@mescius/spread-sheets-designer';
import { DesignerModule } from '@mescius/spread-sheets-designer-angular';

var sjsLicense = "sjs-distribution-key";
GC.Spread.Sheets.LicenseKey = sjsLicense;

(GC.Spread.Sheets as any).Designer.LicenseKey = "designer-component-distribution-key";Opening an Excel File

Finally, you can try running the app by just entering:

npm start

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 Angular application.

Conclusion

There are several options to choose from when it comes to meeting the spreadsheet needs of customers. Utilizing a leading Angular 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 an Angular spreadsheet component.

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