What's New in SpreadJS v16.1
PivotTable Enhancements
SubTotal Option for Single Fields
We have enhanced PivotTable support by adding a subtotal option for single fields. Previously, SpreadJS would change the subtotal position to be at the bottom of each field. Now you can change the position for individual fields
GetPivotData functions for PivotTable references
The “Use GetPivotData functions for PivotTable references” option has been added as an option for the formulas in the workbook, and can be enabled or disabled. This option lets you reference PivotTable cells even if the layout of the data changes.
PivotTable Sorting
With this release we have added sorting functionality that is accessible by right-clicking on a cell in a PivotTable.
TableSheet Enhancements
Bind DataManager Views to a Worksheet Table
You can now utilize a View from a DataManager as the data source for a table in a worksheet. Simply create a table or view in the DataManager, and then use the sheet.tables.addFromDataSource() method to bind to that view:
var sheet = spread.getActiveSheet();
var dataManager = spread.dataManager();
var productTable = dataManager.addTable("productTable", {
remote: {
read: {
url: 'https://northwind.now.sh/api/products'
}
}
});
productTable.fetch().then(function (args) {
var productView = productTable.addView("productView");
sheet.tables.addFromDataSource("Table2", 10, 1, productView, GC.Spread.Sheets.Tables.TableThemes.light2);
});Data Source Spread Transformation
In some cases, the data in the DataManager could be in the form of nested objects with different properties. Previously, you would need to use calculated columns or dot delimiters to indicate the properties of those objects as fields. With this release, you can simply specify “spread: true” to ensure that the first-level children of an object are spread out into separate fields:
Spreadsheet Filters
TableSheets now allows you to filter multiple columns at the same time.
Interactive Filters Demo >
Reordering
You can reorder sheets in the workbook by clicking and dragging the sheet tabs at the bottom of the workbook. With this release you can now reorder worksheets and TableSheets together so the order of worksheets and TableSheets can be changed however you want.
Header Style Rule
Style rules are now supported for column headers in a TableSheet. By default, the TableSheet would automatically add a locked icon to a column if it was set to read-only. You can now set specific style rules for how to display column headers in certain circumstances.
Calculation Enhancements
Auto Formatting for Formula Results
After entering a formula in SpreadJS, you can now have the result be auto-formatted according to the data in the formula. For example, if you had a cell (A1) that had a value of 0.1 and the percent formatter applied to it (0%), and you were to reference that cell in a formula like =A1+1, then the result would automatically format as a percentage as well: 110%. The auto-format will be one of the following types:
-
Empty - null/undefined/”general”/””
-
Number - 0 0.00
-
Currency (Accounting) - $#,##0.00
-
Date (Time) - m/d/yyyy h:mm or h:mm:ss AM/PM
-
Percentage - 0.00%
-
Fraction - # ?/?
-
Scientific - 0.00E+00
-
Text - @
RegEx Functions
We have added some new RegEx functions to SpreadJS:
-
GC.REGEXEXTRACT - Extracts the matching substrings according to a regular expression
-
GC.REGEXMATCH - Tests to see if a piece of text matches a regular expression
-
GC.REGEXREPLACE - Replaces part of a text string with a different text string using regular expressions
These new functions can help with extracting, matching, and replacing text within cells.
Quick Typing for Cross Worksheet Formulas
When entering a formula in SpreadJS, you can select a sheet tab at the bottom of the workbook to include a reference to it in the formula and stay in edit mode. However, there may be too many sheets to display at one time and therefore can’t be selected. The new AllSheetsList button can now be used to select sheets for references in formulas:
Sparkline Functions
Shapes now support using sparkline functions as the source property. This can be done via style.fill.src. When used, it will create the sparkline and use it as the background of the shape. For example, you could create a standard rectangle shape and use a Pie Chart sparkline within:
Text Box Shape
SpreadJS now supports TextBox Shapes, which means you can now add boxes anywhere on the worksheet that have text inside of them. You can also utilize the resizeToFitText option to automatically resize the text box to fit the user’s input.
Z-Order Methods and Designer Support
SpreadJS and the Designer Component now support changing the Z-Order of shapes. This means that you can now change the visual order of shapes relative to each other using the following behaviors:
-
Bring Forward - Move shape above another shape
-
Bring to Front - Move shape above every other shape
-
Send Backward - Move shape below another shape
-
Send to Back - Move shape underneath every other shape
Chart Enhancements
Gauge Charts
A great chart enhancement we have made is the addition of Gauge Charts. This new combo chart lets you create a kind of gauge or speedometer. Gauge charts are a combination of pie and doughnut charts that let you visualize a single value of data in a quantitative way.
Invert If Negative
Column and Bar charts have been enhanced with an invertIfNegative option. This lets you format positive and negative values different, allowing for a clearer visual distinction. You can enable this option on a specific series in a chart, and SpreadJS will automatically invert the data. You can use the invertColor option to choose a specific color for the inverted series.
Workbook Enhancements
Input Mask Style
A new input feature that we have added is the Mask. This is used to constrain user input in a set format, ensuring data integrity while also preventing input errors, for example entering a formatted phone number. The pattern can either be a String or Date pattern, and can be used in conjunction with formulas to produce a specific result.
Password-protected Worksheets
Worksheets in SpreadJS can now be password protected. You can set a password for specific sheets or the entire workbook, matching Excel functionality.
Resizable Filter Window
The drop-down filter dialog can now be resized to fit user needs. Resizing follows the below behavior:
-
Keeps track of the size that the user last set it to
-
Will not resize below a minimum required size to fit the contents
-
A global setting to enable/disable resizable filter dialogs
Word Line-Breaking
SpreadJS now supports custom line-breaking, which lets you define the characters that indicate when a line break should happen. For example, you could create a culture and then add line break information in the Text Format:
Interactive Globalization Demo >
ComboBox AllowFloat
In some cases, a user would be interacting with a ComboBox, and depending on the positioning and contents, the full ComboBox could not be displayed properly. With this release we have added an allowFloat property on the ComboBoxCellType that, when enabled, allows the ComboBox to float outside of the SpreadJS instance on the page, like so:
Insert Cut Cells for Tables
SpreadJS already supports inserting copied or cut cells outside of a table range. Now with this release copied and cut cells can be inserted into a Table.
Display Zero
In some cases, a worksheet may have formulas that result in 0 in a cell, and a user might not want those cells to show any value. To support this, we have added the showZeros option, which can be enabled to show 0 values in cells, and disabled to show the cell value as blank.
Checkbox CellType Line Break
Checkboxes are a CellType in SpreadJS that allow you to put a checkbox inside of a cell and have a label next to the box. In some cases, the text might be too long for the cell, which is why we have added support for line breaks in CheckBox CellTypes via the cellStyle.wordWrap property. When set to true, the text will automatically wrap in the cell if it is larger than the width of the cell.
Excel Styles Copy Optimization
SpreadJS now supports better organization when copying data to the clipboard, resulting in smaller clipboard sizes. This is as a result of saving styles by CSS classes, similar to the way Excel saves data to the clipboard when copying.
Preserve WordWrap
As another copy/paste enhancement, SpreadJS now supports preserving cell word-wrapping when copying from one SpreadJS instance to another, or from SpreadJS to Excel. If wordwrap is enabled for a cell, that cell wordwrap will automatically work when copied over.
Column/Row Header Layout Enhancement
SpreadJS supports addRows/Columns and deleteRows/Columns, and with this release these functions now work with the Column and Row headers. You just need to specify SheetArea.colHeader or SheetArea.rowHeader when calling those functions.
Style Decoration Icons
The icons property in SpreadJS has been enhanced to support style decoration. These icons are a set of self-defined icons that can be embedded inside or outside of the cell. Since these are implemented using styles, you can specify the source, width, height, and position for the icons.
SetRowCount and SetColumnCount Data Protection
In previous releases of SpreadJS, the SetRowCount and SetColumnCount functions could delete rows or columns, even if the rows and columns that would be deleted had data in them. With this release, we have added a new parameter called guardContent, which will let you prevent row and columns that had data in them using those functions.
Framework Support
Next.js Support
The Next.js framework is a React-based framework that lets you create full-stack web applications. SpreadJS has added support for use within Next.js. This should give you all of the features you need for production, including hybrid static and server rendering, TypeScript support, smart bundling, route pre-fetching, and more.
Nuxt Support
Nuxt is a Vue-based open source framework that helps with web development, and is now supported for use with SpreadJS in this release.
Angular 15 Support
SpreadJS now supports the latest Angular 15 with the Angular wrapper.
Designer Component Accessibility
One of the biggest additions we have made to the Designer Component has been to add accessibility options. This can be enabled in the Spread Settings dialog:
This basic accessibility is based on WAI-ARIA (WAI-ARIA Overview). It should be noted that different screen readers could result in different text being read for certain options. We will continue to enhance this support across all Designer features.
Designer Enhancements
Resizable Conditional Formatting and Named Range Dialogs
The Designer Component now supports resizable dialogs. This is simply a new “resizable” property added to the dialog template:
// GC.Spread.Sheets.Designer.IDialogTemplate
export interface IDialogTemplate {
templateName: string;
title?: string;
modal?: boolean;
content: IComponentRenderType[];
buttons?: IComponentDialogButtonOption[];
resizable?: boolean; // new
}When that property is set to true, the dialog will support resizing in the left, right, and bottom directions. In addition, the initial size of the dialog is the minimum size of the dialog.
Font-Size Drop-Down Enhancement
In the Designer Component, you can change the font size by clicking on the drop down and selecting a number. With this release, we have now made that drop-down box editable, so you can set the font-size to include decimals. This is limited from size 1 to 409, and the decimals have to be in multiples of 0.5.
These are many of the new features that we added to SpreadJS in v16.1. Be sure to check out our demos and documentation for more information on these features, and download a trial of SpreadJS to try it out for yourself!