What's New in SpreadJS v18

New DataCharts Add-On

For years, SpreadJS has empowered users with robust charting capabilities to display and summarize spreadsheet data seamlessly. While these existing charts cater to most needs, some scenarios require a more flexible approach.

With the release of SpreadJS v18, we are excited to introduce the DataCharts Add-On. This powerful new tool allows you to bind charts directly to the Data Manager data, eliminating the need to store data within the SpreadJS workbook.

For our ReportSheets Add-On users, the new DataCharts Add-On is included and seamlessly integrates with the same Data Manager, enabling users to effortlessly enhance reports with dynamic charts. If you wish to utilize the DataCharts Add-On within your spreadsheets outside the ReportSheets Add-On, a separate DataCharts Add-On license is required.

Documentation | Demo

Workbook

Right-to-Left Document Mode and Text Direction

The standard orientation for SpreadJS is Left-to-Right, but with this release, we have officially added Right-to-Left orientation, which can be enabled by setting the RightToLeft option of the worksheet to true.

Documentation | Demo

Bind Sheet Table to Data Manager Table

With v18, we have enhanced the Table Data Binding functionality to support binding to a table in the Data Manager. This means that the data and formulas of a sheet table can now be added to a data manager table to keep the data more centralized for use in other areas of SpreadJS. This functionality also supports binding specific columns in the Data Manager table or auto-generating columns. Additionally, when importing an Excel file, the sheet tables can be converted to Data Manager tables with a new import flag:

Documentation | Demo 

Data Range Container

To aid the implementation of the Data Manager Table Binding, a new Data Range feature provides the ability to fully control a specific sheet range area and convert row/column coordinates to data table coordinates, allowing users to focus on an application’s business logic more easily.

Data Ranges can be used by following the steps below:

1. Create a custom Data Provider instance.
2. Choose a target range in a sheet.
3. Use the Data Range Manager of the sheet (sheet.dataRanges) to add a Data Range.

Documentation | Demo

Diagonal Lines Cell Type

A new cell type has been added to SpreadJS, which allows us to insert diagonal lines within a cell. This can be useful for designing table headers or separate areas in reports. These cells can display multiple text items divided by diagonal lines in different sections:

These cell types also support many different properties, including value types, display rules, line styles, text orientation, text wrapping and clipping, and right-to-left layouts.

Documentation | Demo

Prevent Cell Editing with Property Setting

A new property called allowEditInCell has been added to prevent a cell from being edited. Setting this property to false will prevent a cell from going into edit mode if a user double-clicks on a cell or uses the keyboard to start editing. This can be useful if the cell should only be changed with cell types like a ComboBox or a CheckBox.

Documentation | Demo

Horizontal Scrolling with Shift+Mouse Wheel

Prior to this release, vertical scrolling in SpreadJS was performed using the mouse wheel or the vertical scroll bar, but horizontal scrolling could only be accomplished using the horizontal scrollbar. With this release, we have added the ability to scroll horizontally by using Shift + Mouse Wheel.

Documentation | Demo

Change Editor Status via Command

Two new commands have been added to SpreadJS that allow us to change the editor status. These commands are:

• StartEdit: Used to edit the active cell using edit mode
• ChangeEditorStatus: Used to switch the editor status between enter and edit mode when a cell is being edited

Documentation

Search Cell Comments

The existing search functionality in SpreadJS supported the following:

• Cell Values
• Cell Formulas
• Cell Tags

With the v18 release, searching now supports searching inside comments in a worksheet. With this enhancement, the SpreadJS Designer Component Search Dialog now also supports searching cell comments and tags.

Documentation

getUsedRange API Tag Type Support

To support searching cell tags, the getUsedRange API has been updated to support tag types through a Tag option in the GC.Spread.Sheets.UsedRangeType enumeration.

Documentation | Demo

Check/Uncheck Filter Items with Keyboard

New keyboard shortcuts have been added for interaction with filters in SpreadJS. When a user opens a filter, they can use the following keys to interact with it:

• Spacebar: Check/Uncheck an item in a list when the list is focused
• Left Arrow Key: Collapse all child items of the current item in the list when the list is focused
• Right Arrow Key: Expand all child items of the current item in the list when the list is focused
• Up and Down Arrow Keys: Outside of the list box in a filter, this will switch the focus in the filter dialog

Documentation | Demo

ComboBox CellType Data Source Binding Support

ComboBox CellTypes in SpreadJS now support binding to a table column. That table can occur as a data table in the data manager or as the result of a formula. A new dataBinding method has been added to the ComboBox CellType to support this:

var binding = { dataSource: '=SORT(UNIQUE(QUERY("Products", {"productName","productId"})))', text: 0, value: 1 };
var cellType1 = new GC.Spread.Sheets.CellTypes.ComboBox();
cellType1.dataBinding(binding);
activeSheet.getCell(1, 3).cellType(cellType1);

Documentation | Demo

Table Resizing Event Cancel Option

To control table resizing by setting a limit for the number of rows or columns a table can have, the TableResizing event now offers a Cancel parameter. This allows us to programmatically set whether to stop the current resize action, and it is set to false by default.

Documentation | Demo

Pivot Table Add-On

Change Data Source

Pivot Tables now support changing the data source and synchronizing the existing layout information to the new data source. The updateSource method is used for changing the current data source and is useful when more rows need to be added, or an existing dataset has to be modified. This can also be accomplished with the Designer UI:

Documentation | Demo

Show Subtotal in Single Field

Similar to Excel, subtotal information can now be displayed for individual fields in a Pivot Table. The visibility of these subtotals can be controlled using the subtotalVisible method:

pivotTable.subtotalVisible("Date", true); // Value can be true or false

Documentation | Demo

Filter Dialog Keyboard Operations Support

Like the standard filter dialog in SpreadJS, the filter dialog for PivotTables also now supports keyboard operations:

• Tab - Switch focus to next item
• Shift + Tab - Switch focus to previous item
• Up/Down Arrow Keys - Switch focus to previous/next sub-items
• Left/Right Arrow Keys - Expand or collapse sub-items
• Space - Check/Uncheck item when in list, or execute current selection option
• Enter - Save current settings or execute operation item where focus is located
• Esc - Close the current dialog

Documentation | Demo

ReportSheet Add-On

Chart Support

ReportSheets now support charts using the DataCharts functionality, which is included with the ReportSheet Add-On.

var templateSheet = report.getTemplate();
var chart = templateSheet.dataCharts.add("reportChart1", 20, 20, 300, 400, GC.Spread.Sheets.DataCharts.DataChartType.column);

Note: To utilize DataCharts outside of ReportSheets, a separate DataCharts Add-On license is required.

There are two types of charts that can be inserted into a ReportSheet:

Floating Chart

Float Charts are the standard way of displaying charts, where the position is based on the top left cell, and the chart floats above the cells. These charts support binding to a table in the Template Sheet or a table in the Data Manager.

Template Cell Chart

Cell Charts are charts that are set within cells in a template sheet, similar to sparklines. This can be useful for setting up multiple charts for specific fields of data, such as a chart for different regions:

Pagination

These charts also support the pagination of data, allowing you to split data into pages that can be cycled through.

Documentation | Demo

Expand/Collapse All

The expand and collapse functionality for ReportSheets has been enhanced to support expanding and collapsing all cells and dependent cells of a specific cell.

// Get all the cells spilled by the year template cell.
const yearCells = reportSheet.getCells(1, 0);
const firstYearCell = yearCells[0];

// Collapse ALL: collapse the first year cell and it's descendant cells.
reportSheet.toggleCollapseState(firstYearCell.row, firstYearCell.col, 'Collapsed', true);

// Expand ALL: expand the first year cell and it's descendant cells.
reportSheet.toggleCollapseState(firstYearCell.row, firstYearCell.col, 'Expanded', true);

Documentation | Demo

Find Fields in DataSource Panel

When creating a ReportSheet, a panel will show on the left that shows all the registered tables in the DataManager. With the v18 release, the Data Source panel now supports searching the fields:

Documentation | Demo

More Aggregate Function Options in Wizard

The SpreadJS ReportSheet Wizard aids in creating reports and configuring report settings in an easy-to-use UI. With the v18 release, we have added more aggregation options, specifically to help with Grouped and Cross reports:

Documentation | Demo

Container Support for Design Templates

When creating ReportSheet templates, containers can now be added to specific ranges, helping users easily design reports. These containers have three different types, and all the fields are set into the containers one by one:

• List - Fields are set left-to-right
• Group - Fields are set left-to-right
• Cross - Fields are set top-to-bottom

Once the fields are set, field labels and styles are automatically generated.

Documentation | Demo

Dropdown Selection Feature for Field Value Editor

With v18, SpreadJS now supports selecting parameters for ReportSheet fields through a drop-down box in the field value editor in the Designer. This is useful for values with parameters to choose from:

Documentation | Demo

Calculation

New Aggregation Functions (GROUPBY, PIVOTBY, and PERCENTOF)

SpreadJS v18 supports new aggregation functions:

• GROUPBY - Create a summary of data by grouping along row fields and aggregating the associated values.
  Demo
• PIVOTBY - Create a summary of data by grouping along two axes and aggregating the associated values.
  Demo
• PERCENTOF - Sums the first argument and divides it by the SUM of the second argument. This is particularly useful with GROUPBY and PIVOTBY, as it can return percentages.
  Demo

Function Reference

The Function Reference functionality (also known as Eta Reduced Lambda Functions) simplifies a function by eliminating unnecessary abstractions when it has no further operations to perform on its argument. This allows us to use abbreviated syntax when possible. This can be accomplished by defining custom names with the content containing a formula name, such as “SUM”:

// Add a custom name with the content as the function name.
spread.addCustomName("op", "SUM");

// Use the custom name as a formula.
sheet.setFormula(0, 1, "op(C1:C5)");

This type of reduction can also be used with LET functions and LAMBDA functions.

Documentation | Demo

Designer

Support for Searching Invisible Ranges

An enhancement has been made to the Find/Replace dialog in the Designer to add an “Ignore Hidden” option to support searching invisible ranges. When set to true, hidden ranges will be ignored when searching and included when set to false:

Documentation | Demo

Frameworks

Runtime and Design Vue Wrapper TypeScript Support

The Vue wrapper for SpreadJS now includes an index.d.ts file to support TypeScript with Vue 3.

Angular 18 Support

SpreadJS now has support for Angular 18 with this release.

Collaboration Preview

The last feature that we have added is our Collaboration functionality which is in beta for the v18 release. This is a set of server-side components that facilitate real-time editing and collaboration of client components, like SpreadJS. We have created a robust collaboration plugin for SpreadJS that is built on that collaboration framework, and it enables you to implement features like auto-saving and version control to help create your own proprietary collaboration platforms.

To try out this preview, a demo is included with the SpreadJS v18 download in the ‘Samples’ folder.