Skip to main content Skip to footer

SpreadJS V16 Service Pack 1 Released!

SpreadJS V16.1 (Service Pack 1) is here! We have a lot of new features and enhancements to existing functionality, like new cell types, updated Framework support, improvements to charts, PivotTables, TableSheets, and so much more! This blog will introduce the following features:

For more information on how to utilize these features, you can check our documentation and demos.

To give these features a try, be sure to download SpreadJS today!

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 for Single Fields

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 accessible by right-clicking on a cell in a PivotTable.

PivotTable Sort Demo>

TableSheet Enhancements

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:

JavaScript Data Source Transformation

Spread Transformation Demo >

Spreadsheet Filters

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

TableSheet's support for spreadsheet filters

Interactive Filters Demo >


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 displaying 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) with a value of 0.1 and the percent formatter applied to it (0%), you were to reference that cell in a formula like =A1+1. 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 extract, match, and replace 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:

Quick Typing for Cross Worksheet Formulas

Shape Enhancements

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:

Sparkline Functions

Sparkline Shape Demo >

Text Box Shape

SpreadJS now supports TextBox Shapes, which means you can add boxes anywhere on the worksheet with text inside of them. You can also utilize the resizeToFitText option to automatically resize the text box to fit the user’s input.

Text Box Shape

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 the shape above every other shape
  • Send Backward - Move the shape below another shape
  • Send to Back - Move the shape underneath every other shape

Z-Order Methods and Designer Support

Form Control Enabled Property

In a previous release, we added Form Controls, which let you add different types of buttons and interactive UI for form-like interfaces. With this release, we have also added an enabled property that will give you control over whether or not a user can interact with the form control.

Form Control Demos >

Chart Enhancements

Gauge Charts

An excellent 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 allow you to visualize a single value of data quantitatively.

Gauge Charts

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 differently, 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 preventing input errors, such as 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 Style

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:

var myCulture = GC.Spread.Common.CultureManager.getCultureInfo();
myCulture.TextFormat.lineBreakingChar = [" ", "+", 
myCulture.TextFormat.lineBreakingForbidStart = ["》"];
myCulture.TextFormat.lineBreakingForbidEnd = ["《"];

activeSheet.setValue(0, 0, "1-1+2+3+4+5+6+7");
activeSheet.getCell(0, 0).wordWrap(true);
activeSheet.setValue(1, 0, "《abc》《def》《ghk》");
activeSheet.getCell(1, 0).wordWrap(true);

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

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

Sometimes, 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 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 >

Styles Copy Clipboard Content Optimization

SpreadJS now generates optimized HTML content for the clipboard when copying styled ranges. This improves the performance and memory consumption of large clipboard operations.

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 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 protect rows and columns that have 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:

Designer Component Accessibility

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.

Resizable Dialogs

Font-Size Drop-Down Enhancement

In the Designer Component, you can change the font size by clicking 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

Those are just a few of the features that have been added to SpreadJS with this new release, and a full list of fixes can be found here.

To give these features a try, be sure to download SpreadJS today!



comments powered by Disqus