Skip to main content Skip to footer

What's New in SpreadJS v16.2

Gantt Sheet Add-On Beta

JavaScript GanttSheet Add-on - BETA release

With the SpreadJS 16.2 release, we have added a Beta version of a new Add-On known as Gantt Sheet. This is a full Gantt implementation inside of a specialized sheet in SpreadJS. It has its own headers and uses shapes to draw the Gantt tasks. There are multiple options that Gantt Sheet offers including:

  • Zoom and Scroll for the timescale

  • Styles for taskbars and gridlines

  • Layout

  • Task operations

  • Calendar

  • Importing and Exporting

The Gantt Sheet is built on a fast data-bound DataTable view with gantt behavior and a spreadsheet user interface. It can be bound to a DataManager view with a specific schema.

JavaScript GanttSheet - Gantt Chart JS Component

Documentation | GanttSheet Demos

Formula Editor Panel

Viewing, editing, and debugging long and complex formulas in SpreadJS is now even easier with the Formula Editor Panel. This new component provides the following functionality:

Formula Syntax Highlighting

Formula text will be displayed in different colors according to the type of token:

JavaScript Formula Editor Panel - syntax highlighting

Formatting

Formulas that are long and complex can be formatted into separate lines with the appropriate indentation and nesting:

formula editor panel - formatting

Flexible Outline View

Formula outlines can be collapsed and expanded:

JavaScript Formula Editor Panel - expand and collapse complex formulas

Auto-Completion Suggestions

Formulas can be entered quickly and correctly with suggested function, custom names, and enumerated parameters:

Auto-Completion Suggestions - JavaScript Formula Editor Panel

Auto-Completion Suggestions - JavaScript Formula Editor Panel

Auto-Completion Suggestions - JavaScript Formula Editor Panel

Tooltip Information

Tooltips with information about a function will show when a user hovers over a function name or types a function:

JavaScript Spreadsheet Formula Editor Panel - Tooltip Function Information

Lint Option

Formula errors such as incorrect parameters and unmatched brackets will be flagged:

JavaScript Spreadsheet Formula Editor Panel - flag formula errors - lint optinos

JavaScript Spreadsheet Formula Editor Panel - Lint Option - flag formula errors

Theme Customization

You can change the appearance of the Formula Editor by changing the theme CSS, which include properties such as tokens, tooltips, backgrounds, foregrounds, and referenced ranges.

Change the theme of the JS Spreadsheet Formula Editor Panel Change the theme of the JS Spreadsheet Formula Editor Panel Change the theme of the JS Spreadsheet Formula Editor Panel

Syntax Highlighting

Different syntax elements of a formula (such as the referenced ranges) are highlighted accordingly to enhance readability.

Highlight the range of cells in the formula - JS formula editor panel

Learn more about the new Formula Editor Panel:

Documentation | Formula Editor Panel Demo

Accessibility

Cell.altText Property

In our goal to add more accessibility options to SpreadJS, we have added the Cell.altText property in this release. This property lets you set specific text that will be read by a screen reader when accessibility is set to true:

sheet.getCell(1, 1).value(1000).altText("Sales amount is {value}");

Accessibility Demos

Calculation Enhancements

SUMIFS and XLOOKUP Performance Enhancement

The SUMIFS and XLOOKUP functions have been enhanced in terms of performance, so workbooks that use these functions should be faster in certain circumstances.

Excel Manual Calculation Options

SpreadJS now supports manual calculation. When set, formulas will not automatically calculate; rather formulas will only calculate when edited or when a new formula is entered. This is supported through a new CalculationMode enumeration which is auto by default, and can be set to manual. With automatic calculation, all dirty cells (cells that have been changed) will be calculated if relevant cells (cells that are referenced in those dirty cells) have been changed. With manual calculation, the only cells that are calculated are the ones that have been directly changed; cells that reference that changed cell will not be calculated.

Calculation Service Demo

Cross-Sheet Reference Support when Entering Formulas

In SpreadJS, a user can enter formulas in a cell by typing “=”. Normally, a user would only be able to select cells with the cursor to reference in that formula as long as they were on the same sheet. With this release, we have added the ability to click on a different sheet tab while editing a cell formula in order to reference cells in other worksheets. This functionality can be enabled or disabled depending on your preference by changing the CSS.

Cross-Sheet Reference Support when Entering Formulas - JS Spreadsheet Cross-Sheet Reference Support when Entering Formulas - JS Spreadsheet

Workbook Enhancements

Cell defaultValue Property

We have added a property that allows you to set the default value for a cell. Normally, a cell will display blank when there is no value in it. With this release you can set any value or formula as the default value of a cell. You can get and set the default value with a simple property:

activeSheet.getCell(0, 0).defaultValue(20);
console.log("DefaultValue of cell(0,0) using defaulValue method: " + activeSheet.getCell(0, 0).defaultValue());

Cell defaultValue Demo

Date Time Picker and Slider Enhancement

Date Time Pickers and Sliders have been enhanced to support users defining a time period or range that includes either:

  • a start and end time

  • a numerical period from a start number to an end number

This enhancement represents a simple object that can be stored as a cell value, which just has start and end properties that can either be Dates or Integers.

Excel Styles Copy Optimization

Copying styles in SpreadJS has been enhanced to match Excel. Now, SpreadJS will generate named styles for cells and add them on top of the HTML clipboard content instead of adding the same style definition for each cell, which reduces the command size.

TableSheet Enhancement

Window Aggregation Function

The WINDOW functions added in this release provide data analysis in a TableSheet. These functions can perform aggregation, ranking, and analytical calculations over a particular window (a set of table rows that are related to the current row) and produce a result for each row. This allows you to work with both aggregate and non-aggregate values at the same time. These new functions are follows:

  • Ranking Functions

    • CUMEDIST - returns the cumulative distribution value

    • DENSERANK - returns the rank of the current row within its partition without gaps

    • ROWNUMBER - returns the number of the current row within the partition

    • W_PERCENTRANK - returns the percentage of the rank value

    • W_RANK - returns the rank of the current row within its partition with gaps

  • Analytic Functions

    • FIRSTVALUE - returns the value of the argument from the first row of the window frame

    • LAG - provides access to the value from a row at a given physical offset that leads to the current row

    • LASTVALUE - returns the value of the argument from the last row of the window frame

    • LEAD - provides access to the value from a row at a given physical offset that follows the current row

    • NTHVALUE - returns the value of the argument from the Nth row of the window frame

    • NTILE - returns the bucket number of the current row within its partition

The best example of an implementation of Window functions is a running total. You can use Window functions to create a running total of some data, and use things like the PARTITIONBY function to get running totals for each year or something similar:

Window Aggregation Function - JS TableSheet Component

TableSheet's Window Functions Demo

Designer Enhancements

Default Active Ribbon Tab

The first enhancement we made to the Designer Component in this release was the ability to set a default active tab in the ribbon. By setting a default active tab in the configuration, that specific tab will be open upon loading the SpreadJS Designer Component.

// Use the activeRibbonTab method of the designer component to set the active ribbon tab.
var config = GC.Spread.Sheets.Designer.DefaultConfig;
var designer = new GC.Spread.Sheets.Designer.Designer(document.getElementById("designerHost"), config);
let currentActiveRibbonTab = designer.activeRibbonTab();
    if (currentActiveRibbonTab !== "insert") 
        {
        designer.activeRibbonTab("insert");
        }

Default Active Ribbon Tab - JS Spreadsheet Designer Ribbon UI

Excel Themes, Colors, and Fonts

We have added Themes, Colors, and Fonts buttons to the Designer, which lets a user switch themes or color sets to affect all shapes, charts, and built-in cell styles that reference those theme colors.

Set Excel like themes, colors, and fonts with the new buttons in the JS Designer Component

Excel File Password

SpreadJS supports opening and saving Excel files with passwords, and we have now added that functionality to the SpreadJS Designer in the File Dialog.

Read-Only Names in Name Manager

Names can now be set to read-only in the name manager with this release. This can prevent your users from changing specific names in the workbook.

prevent users from changing specific names in the workbook - set read-only names in the name manger

Angular 16 Support

SpreadJS now supports Angular 16 for our Angular wrapper and the Designer Component.