What's New in SpreadJS v15.2

Name Box Component

The Name Box component of SpreadJS is used to display selected ranges, items, named ranges, and create and navigate 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 out 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 data presentation in the form of a DataObject Cell Type. This means that you can have special cells that are 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:

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:

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 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 in the near future.

Table Slicer Multi-Select

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

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:

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:

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

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 customzation options, including:

• Brightness
• Contrast
• Recolor
• Crop
• Transparency
• Geometry Types

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

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, that allows a developer to actually 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 Functions Demo

Array Manipulation Functions

The eleven new array manipulation functions include:

• VSTACK - Stacks arrays vertically
• HSTACK - Stacks arrays horizontally

• 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

• 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 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 in order 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, and then loading that data at a different time using the table’s fetch method as described above. This essentially lets the developer create a TableSheet without having to specify 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 was 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 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 are now able to 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.

 

New Framework Support

SpreadJS now includes support for the latest version of both React 18 and Angular 14.

These were many of the new features that we added in the v15 service pack 2 release of SpreadJS. Be sure to try out all these features in our demos, and download a trial of SpreadJS today!