SpreadJS v15.2 Released!

August 25 2022

The SpreadJS V15.2 (Service Pack 2) update has just been released, and with it comes some great new features and other fixes.

This blog will introduce several new features and how they can help benefit and enhance your JavaScript applications.

Be sure to try out all these features in our demos, and download a trial of SpreadJS today!

SpreadJS V15.2 Features

Name Box Component
Workbook Enhancements

Data Object CellType
Slicer Update
Table Slicer Multi-Select
Selection Navigation Enhancement
Support Multiple Hover Styles in Different Ranges
InvalidOperation Event Locked Cell Enhancements

Picture Shape Enhancements
Calculation Enhancements

Cross-Workbook Formula Source and Target
New Excel Functions

Text Manipulation Functions
Array Manipulation Functions

Relationship Display Formula

TableSheet Enhancements

Updating Data without a Page Refresh
Getting Changed TableSheet Info
Load TableSheet Template without Data
ComboBox/MultiColumn List for Lookup Column

PivotTable EnableDataValueEditing
Framework Support

Name Box Component

Namebox

The Name Box component of SpreadJS is used to display selected ranges, items, and named ranges. It creates and navigates to custom names within a spreadsheet. The name box has a few functionalities:

The user can enter text into the name box

If the text can’t be parsed into ranges, SpreadJS will create a custom name for the selected cells
If the text can be parsed into ranges, SpreadJS will add those ranges as selections in the worksheet
If the text can be parsed into ranges, but those ranges aren’t in the same worksheet, a warning will be shown
If the text can’t be parsed into ranges and is not a valid custom name, a warning will be shown

The user can select cell ranges

If the selected region is already associated with a custom name, the name box will display that name.

This functionality is already present in the designer, but with this release, we have separated it into its own component that can be used separately without the SpreadJS Designer.

Name Box Component Demo

Workbook Enhancements

DataObject Cell Type

With this release, we have added a new cell type to help with the data presentation in the form of a DataObject Cell Type. This means that you can have special cells bound to data, and selecting different properties of a data object is really easy. When a cell value is an object, and the cell type is set to DataObject, a cell button will automatically be added to the cell:

dataobject

Upon clicking this button, a drop-down will open, and if the cell value is a nested object, there will be a cascaded drop-down list:

drop-down

When a user chooses an item from that list, the selected item value will be extracted and displayed in adjacent empty cells to the right:

dataobject

DataObject CellType Demo

Slicer Update

When we first implemented slicers in SpreadJS, they were made using DOM elements. With this release, we have enhanced the slicers to be built using shapes, allowing us to implement even more easy-to-use slicer features, such as Table Slicer Multi-Select, and others.

Table Slicer Multi-Select

This customer-requested Excel feature adds a multi-select option to the Table Slicer. This feature allows customers to select certain items in a table slicer instead of just one or all items.

Table Slicer Multi Select

Selection Navigation Enhancement

This enhancement focuses on navigating selected cells. Previously SpreadJS would cancel a selection when selecting multiple cells, entering data, and then pressing enter to move to the next cell. We have changed this to match Excel behavior, so the selection now does not get removed:

Selection Navigation Enhancement

Support Multiple Hover Styles in Different Ranges

Previously, you could define a hover style for cell ranges in a sheet so that when a user hovers over that row, it shows a single style:

Hover style

With this enhancement, it is now possible to set different hover styles for multiple ranges:

Hover Style Options

InvalidOperation Event Locked Cell Enhancements

The InvalidOperation event fires when specific invalid operations are performed by the user, such as:

Invalid sheet name change
Changing part of an array formula
Setting an invalid formula
etc

This event will now fire for a few more invalid user interactions:

Attempting to type in a locked cell
Double-clicking a locked cell
Cutting a locked cell

Picture Shape Enhancements

The picture functionality of SpreadJS currently supports many features, including borders, background colors, and stretching. With this release, we have added even more picture customization options, including:

Brightness
Contrast
Recolor
Crop
Transparency
Geometry Types

In addition to these new options, we have added the Picture Format tab to the SpreadJS Designer so your users can take advantage of these new options using the SpreadJS Designer Component or your developers with the SpreadJS Designer desktop application.

Picture Shape

Picture Shape Demo

Calculation Enhancements

Cross-Workbook Formula Source and Target

Cross-Workbook formula support was added in the first service pack release of v15. With this release, we have added more information about the source and target references in the form of the new includeItemDetail parameter of the getExternalReferences function, which allows a developer to get the specific target row, column, and source ranges of a specific file to know exactly what cells that specific references are coming from.

New Excel Functions

We have added some new Excel functions to SpreadJS.

Text Manipulation Functions

The three new text manipulation functions include:

TEXTBEFORE - Returns text that’s before the delimiting characters
TEXTAFTER - Returns text that’s after the delimiting characters
TEXTSPLIT - Splits text into rows or columns using the delimiters

Text Manipulation

Text Manipulation Functions Demo

Array Manipulation Functions

The eleven new array manipulation functions include:

VSTACK - Stacks arrays vertically
HSTACK - Stacks arrays horizontally

Stacks

TOROW - Returns the array as one row
TOCOL - Returns the array as one column
WRAPROWS - Wraps a row array into a 2D array
WRAPCOLS - Wraps a column array into a 2D array

Wraps

TAKE - Returns rows or columns from array start or end
DROP - Drops rows or columns from array start or end
CHOOSEROWS - Returns the specified rows from an array
CHOSECOLS - Returns the specified columns from an array
EXPAND - Expands an array to the specified dimensions

Array Manipulations

Array Manipulation Functions Demo

Relationship Display Formula

We have also implemented a formula that helps with displaying object relationship values. For example, say you had an object called “Customer” with the following properties:

Name
ID
CompanyName

You can now reference these individual items in a cell by using syntax like this:

=[@customer.CompanyName]

TableSheet Enhancements

Updating Data without a Page Refresh

In our original implementation of TableSheet, a page would need to be refreshed to update the TableSheet from a connected data source. With this release, the data can be updated without refreshing the page through a new fetch method at the DataManager Table and View levels. Calling this will fetch the data from the data source and update the related TableSheets.

Getting Changed TableSheet Info

We have now added a getChanges method to fetch the items programmatically in a TableSheet that have changed. This new method returns an object array that has the following information:

Type of change - Update or insert for autoSync mode, and update, insert, or delete for bath mode
Data item - The current row data
Old data item - The original row data (optional)
Index - The TableSheet view index

Load TableSheet Template without Data

SpreadJS TableSheets now support saving a template without including the TableSheet data, then loading that data at a different time using the table’s fetch method described above. This lets the developer create a TableSheet without specifying the data initially but then load the data at a later point, such as when that data changes. This would look something like this:

// 1. Invoke the Workbook's fromJSON method
spread.fromJSON(spreadJson);

// 2. Update table options
let myTable = spread.dataManager().tables["myTable"];
myTable.options = {
    remote: {
        read: function () {
            return Promise.resolve(dataSource);
        }
    }
};

// 3. Invoke the table's fetch then setDataView
myTable.fetch().then(function() {
    let myView = myTable.views["myView"];
    let sheet = spread.getActiveSheetTab();
    sheet.setDataView(myView);
});

ComboBox/MultiColumn List for Lookup Column

Lookup columns in SpreadJS TableSheets are columns that are marked as “lookup” in the data schema and were made to support cross-column calculations. With this release, these lookup columns can automatically change the cell type depending on the data schema:

Column lookup as an array - Combo Box Cell Type
Relationship lookup - Multi-Column List
Relationship and column options in lookup - Multi-Column with specified columns

Lookup Column Demo

PivotTable EnableDataValueEditing

By default, cells within PivotTables in SpreadJS can’t be edited, only Pivot ranges, just like Excel. With this release, end-users can now edit PivotTable cell values and then update and reload the data source of a PivotTable to reflect the changes. For the developer, this is enabled with a property called pivot.EnableDataValueEditing.