Skip to main content Skip to footer

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

SubTotal Option

Interactive PivotTable Demo >

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.

GetPivotData Demo >

PivotTable Sorting

With this release we have added sorting functionality that is accessible by right-clicking on a cell in a PivotTable.

PivotTable Sort Demo>

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:

Data Source Spread Transformation

Spread Transformation Demo >

Spreadsheet Filters

TableSheets now allows you to filter multiple columns at the same time.

JavaScript TableSheet's support for spreadsheet filtering

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.

Style Rules Demo >

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 - @

Format Hints Demo >

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.

RegEx Demos >

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:

Supports cross worksheet formulas in a JavaScript Spreadsheet Component

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:

Shapes now support using sparkline functions as the source property

Sparkline Shape Demo >

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.

SpreadJS, the JavaScript Spreadsheet component, now supports TextBox Shapes

Text Box Shape Demo >

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

 Change the Z-Order of shapes using JavaScript Spreadsheet Control

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.

Gauge Charts - JavaScript Spreadsheet Component - SpreadJS

Combo Chart Demo >

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.

 Input Mask Cell Type in JavaScript Spreadsheet Solution

User Input Mask Demo >

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.

Interactive Protection Demo >

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

Filter Dialog Demo >

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:

ComboBox AllowFloat - SpreadJS - JavaScript Spreadsheet

Combo Box Demo >

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.

Interactive Table Demo >

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.

Check Box Demo >

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.

Rows and Columns Demo >

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:

JavaScript Spreadsheet Designer Component's new Accessibility Options

This basic accessibility is based on WAI-ARIA (). 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.

Resize Spreadsheet Dialog Boxes in JavaScript Apps

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.

Font-Size Drop-Down Enhancement - Designer Component

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!