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.
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.
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.
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:
For files other than images, the filename will show in the cell:
For images, a thumbnail will show that can be previewed:
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:
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:
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.
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:
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:
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.
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.
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.
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.
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.
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 |
| 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.
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:
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.
These groups also support sorting that works between the group and base columns.
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:
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:
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:
It should also be noted that Excel does not support this feature, so after exporting to Excel, the outline border will still be displayed.
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:
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.
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:
The different types of AutoFit are shown here:
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:
Then, the first page would look like this:
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.
Updates for SpreadJS v17.1
Related Links