Skip to main content Skip to footer

What's New in SpreadJS v17.1

SpreadJS v17.1 has just been released, and we have added a lot of exciting new features to the product, which we will discuss in this blog. These features include:

Workbook

Right-to-Left

The direction of text in a cell can now be changed to Right-to-left by setting the style’s textDirection property to rightToLeft. This can accommodate languages and scripts that are written and read from the right and ensures data will display correctly and naturally for those languages.

Righttoleft text direction

Demo | Help 

Bulleted List

As a part of our Rich Text Formatting, SpreadJS now supports using unordered bulleted and ordered numbered lists. List items have type, level, and richText as their attributes.

Bulleted List

Demo | Help

Custom Slicer Styles Enhancement

SpreadJS supports setting custom styles for item slicers, but with this release, we have added an API for runtime to store and modify the styles of those slicers. There is now a collection of item slicer styles on the workbook for things like getting, using, modifying, deleting, and setting a default style.

Demo

FileUpload CellType

With the SpreadJS 17.1 release, we are introducing a new FileUpload CellType, which allows users to select any file by clicking the file upload button inside of a cell. Users can also perform operations on these files, including previewing, downloading, and clearing. The default upload button shows in the cell like so:

Upload

For files other than images, the filename will show in the cell:

Modify_BuiltInFileIcons

For images, a thumbnail will show that can be previewed:

File Upload Operations

Demo | Help

Custom Properties Support

SpreadJS now supports setting custom properties for workbooks, including data like title, author, subject, etc. These properties can be managed within the workbook using the API or the SpreadJS Designer using the Advanced Properties window:

Advanced Properties

Properties

Demo | Help

Move/Insert Column or Row by Dragging Headers

Columns and rows can now be moved or inserted by dragging the headers. This can be enabled by setting the allowDragHeaderToMove enumeration of the spread options to row, column, or both. This can also be enabled in the SpreadJS Designer:

Drag Col Row Header

Drag Drop Col Header

Demo | Help

Paste Data Outside of SpreadJS with Formatting

With the 17.1 release, SpreadJS now supports copying and pasting formatting for cells between SpreadJS and Excel. This includes options like number and date formats.

Demo

Copy Shape and Chart as an Image

Shapes, charts, and slicers can now be copied to the clipboard and saved as images. We have added a new API to the Shape and Shape Base classes called toImageSrc. This is the same for Charts and Slicers as well. For the SpreadJS Designer, we also provide a new “Save as Picture” option when right-clicking on any of those objects:

Copy Shape

Demo | Help

Calculation

Formula Adjustment Performance Enhancement

With formulas, we have updated the internal logic to enhance performance when inserting/deleting rows/columns. This should result in faster changes to the calculation when using those actions.

Incremental Calculation

SpreadJS v17.1 now supports incremental calculation, which divides the entire calculation into segments during execution. This allows for responsiveness to user actions when the calculation task is large, preventing the UI from being unresponsive when a workbook contains many formulas. Developers will simply need to set the incrementalCalculation property of the Workbook class to true. There is also a status bar item that displays this calculation process:

Incremental Calculation

Demo | Help

Protected Sheets Hidden Option

The visibility of formula cells in a protected sheet can now be controlled using the hidden property of the Style class or the hidden method of the CellRange class. When enabled, the formula bar will not display any data when the hidden cell is active, the Input Editor will be empty in edit mode, and the Formula Editor Panel will not display formulas.

Demo | Help

Charts

Chart Table Structured References

Structured Reference Formulas have been supported in SpreadJS, and they are now supported within tables as a chart data source. If a chart is bound to a complete table or certain columns of a table that uses Table Structure References, then any update in the table will automatically update the series or data value of the chart at runtime.

Entirerange Chart Table Structure

Chart Data Label “Value Of Cell”

Chart data labels now support using cell references to display the values of selected cell ranges. Users can select a specific range of cells for the data labels of a chart.

Value of Cell

Source Linked Cell Formatter 

SpreadJS already allowed users to configure and customize data labels and axes in a chart to display actual value and other relevant data point information. With the 17.1 release, users can now apply cell formatting using linked cells to dynamically update the format of chart elements, including the data label, axis, and tooltip.

Cell Formatter

Table

Custom Styles Enhancement

Tables in SpreadJS have supported setting a custom style, but developers would have to manage that in their code and only at design time. With this enhancement, we have added an API to modify table styles and maintain a table style collection at runtime, allowing users to add, delete, and modify table styles. Table styles can now be referred to by the name that a user gives them, and developers can set a default style for a table.

Demo

TableSheet

Defined Column Functionality

SpreadJS TableSheet now supports the ability to add, update, and remove columns with meaningful column types to help design tables easily. The column types are as follows:

Column Type Data Type Description
Number number For most numerical values with specified formatting
Text string For the common text
Formula depends on the result To compute a value based on other fields in the record
Lookup depends on the related To look up a specific field in a related record
Date date To easily enter a date value
Checkbox boolean Useful to check/uncheck with a TRUE/FALSE data type
Select depends on the option Useful to select an option from a preset list
Currency number Indicates the currency with culture formatting
Percent number Indicates the number formatted as the percent
Phone string Indicates the digit string of numbers with mask validation
Email string Indicates the email address with mask validation
URL string Indicates the URL text
CreatedTime date To set a date when the record is created
ModifiedTime date To set a date when the fields updated in the record
Attachment object Allows to attach a file directly on the record
Barcode depends on the input Generates a specified barcode from the field

Users can customize column types as well, with the ability to pass in self-defined commands to the options for the TableSheet.

Demo | Help

Undo/Redo Support

Undo and Redo support has been added to SpreadJS TableSheets, allowing users to undo/redo operations in the following categories:

  • Configuration Changes: Filtering, sorting, and other configuration settings
  • Runtime UI Operations: Similar to worksheet operations like cell editing, adding/removing rows/columns, clipboard operations, dragging/moving rows/columns, and more
  • TableSheet API: most API actions that change data or settings, with the exception of the setDataView method

This is also supported in the SpreadJS Designer as well:

Undo Redo

Help

Outline Groups

With this release, SpreadJS TableSheet now supports grouping, where more than one field can be defined in the groupBy method to create an outline group. This multiple grouping allows a user to expand or collapse fields and include aggregations, headers, and footers.

Ts Grouping Multiple

Ts Grouplayout Multiple

These groups also support sorting that works between the group and base columns.

Ts Grouping SortStatus

Demo | Help

Designer 

Save File Dialog Customization

SpreadJS lets you save workbooks as multiple file types, but originally, users could only save as an SSJSON format. With this new customization, users can save existing content to other formats using the save file dialog. Users can also specify the file name to save as:

Save File Dialog

Open Dialogs in the Center of the Screen

In the SpreadJS Designer, dialogs would only be opened within the bounds of the Designer in the center of a web page. With this enhancement, dialogs can be customized to open in any location on the screen:

Control dialog position

Printing showBorder Option

The showBorder method, which controls whether borders are displayed during the printing process, already exists in SpreadJS. With this release, we have added that option to the UI:

Show border

It should also be noted that Excel does not support this feature, so after exporting to Excel, the outline border will still be displayed.

Help

Conditional Formatting Rules Manager Performance Enhancement

The Conditional Formatting Rules Manager has had significant performance enhancements, particularly when managing multiple rules in the dialog.

Conditional Formatting Rules Manager Current Selection Support

In some cases, workbooks could have a large number of conditional formats, which could make it complicated to find a specific format. The Rules Manager dialog now supports showing rules for particular areas, such as the current selection or a specific worksheet:

Current Selection

Conditional Formatting Rules Manager

PivotTable

Custom Styles Enhancement

As with the Custom Table Style Enhancement mentioned above, SpreadJS now gives users the ability to add, delete, and modify PivotTable styles at runtime.

Demo

PivotTable Grouping Compatibility Update

Excel changed the way grouping was done in PivotTable, so we have updated the grouping policy of SpreadJS PivotTables to match. It has been improved to enhance usability, flexibility, and clarity:

Old Behavior New Behavior
Default Field Source Name

The default field source names were directly derived from the intervals, such as years/months/Quarters. For example, grouping by years would generate a field named "Years."

The default field source name combines the original field name with the interval. For example, if the original field is "battleDate" and it is grouped by years, the generated field will be named "Years (battleDate)."

Origin Field Type

When a field was grouped, it was treated as a group field.

The origin field remains a normal field even after grouping.

Regrouping of Date Fields

Regrouping was required using the exact name of the original field.

Regrouping requires either the name of the original field or the generated grouping field.

Ungroup Functionality

Only the original field name was used to ungroup.

Both the original and generated field names can be used to ungroup.

ReportSheet

ReportSheet AutoFit Row/Column

SpreadJS ReportSheet now supports AutoFit for Rows and Columns. This allows the user to specify if the size of rows or columns should change depending on the text within them. With this, there is a new API to set the AutoFit property and a new UI in the Designer:

Autofit Row Column

The different types of AutoFit are shown here:

Autofit Types

Demo | Help

Page Total

The R.V function for SpreadJS ReportSheets produces the value of spilled cells in the sheet. With this release, we have added another parameter to it that specifies the current page. As an example, =SUM(R.V(C2,”CurrentPage”)) would produce the sum of all the spilled values in the current page. The template for this would be:

Page Total Template

Then, the first page would look like this:

Page Total

Demo

Framework Support

Angular 17

SpreadJS now supports Angular 17 in addition to versions 11 - 16.

Next.js 14

SpreadJS also now supports Next.js version 14.

Conclusion

That is several of the features added to SpreadJS with v17.1! To try out these features and more, be sure to download a trial of SpreadJS today!

For more information about these features, you can check out our Demos and Documentation.