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:
- PivotTable Enhancements
- TableSheet Enhancements
- Calculation Enhancements
- Shape Enhancements
- Chart Enhancements
- Workbook Enhancements
- Framework Support
- Designer Component Accessibility
- Designer Enhancements
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.
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 accessible by right-clicking on a cell in a PivotTable.
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:
Spreadsheet Filters
TableSheets now allows you to filter multiple columns at the same time.
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 displaying 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) 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 - @
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.
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:
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:
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.
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
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.
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.
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.
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:
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:
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
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.
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.
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:
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 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.
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!
Tags:
Related Blogs
Try Our JavaScript Spreadsheet Components
SpreadJS Resources
Kevin Ashley