What's New in SpreadJS v16.2
Gantt Sheet Add-On Beta
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.
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:
Formatting
Formulas that are long and complex can be formatted into separate lines with the appropriate indentation and nesting:
Flexible Outline View
Formula outlines can be collapsed and expanded:
Auto-Completion Suggestions
Formulas can be entered quickly and correctly with suggested function, custom names, and enumerated parameters:
Tooltip Information
Tooltips with information about a function will show when a user hovers over a function name or types a function:
Lint Option
Formula errors such as incorrect parameters and unmatched brackets will be flagged:
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.
 |
 |
 |
Syntax Highlighting
Different syntax elements of a formula (such as the referenced ranges) are highlighted accordingly to enhance readability.
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}");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.
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.
 |
 |
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());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:
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");
}
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.
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.
Angular 16 Support
SpreadJS now supports Angular 16 for our Angular wrapper and the Designer Component.