SpreadJS v18 has just been released, and we have added a lot of exciting new features to the product, which we will introduce in this blog. Highlights include:
- New DataCharts Add-On
- Workbook
- Right-to-Left Document Mode and Text Direction
- Bind Sheet Table to Data Manager Table
- Data Range Container
- Diagonal Lines Cell Type
- Prevent Cell Editing with Property Setting
- Horizontal Scrolling with Shift+Mouse Wheel
- Change Editor Status via Command
- Search Cell Comments
- getUsedRange API Tag Type Support
- Check/Uncheck Filter Items with Keyboard
- ComboBox CellType Data Source Binding Support
- Table Resizing Event Cancel Option
- Pivot Table Add-On
- ReportSheet Add-On
- Calculation
- Designer
- Frameworks
- Collaboration Preview
Ready to try it out? Download SpreadJS Today!
New DataCharts Add-On
For years, SpreadJS has empowered users with robust charting capabilities to display and summarize spreadsheet data seamlessly. While these existing charts cater to most needs, some scenarios require a more flexible approach.
With the release of SpreadJS v18, we are excited to introduce the DataCharts Add-On. This powerful new tool allows you to bind charts directly to the Data Manager data, eliminating the need to store data within the SpreadJS workbook.
For our ReportSheets Add-On users, the new DataCharts Add-On is included and seamlessly integrates with the same Data Manager, enabling users to effortlessly enhance reports with dynamic charts. If you wish to utilize the DataCharts Add-On within your spreadsheets outside the ReportSheets Add-On, a separate DataCharts Add-On license is required.
Workbook
Right-to-Left Document Mode and Text Direction
The standard orientation for SpreadJS is Left-to-Right, but with this release, we have officially added Right-to-Left orientation, which can be enabled by setting the RightToLeft option of the worksheet to true.
Bind Sheet Table to Data Manager Table
With v18, we have enhanced the Table Data Binding functionality to support binding to a table in the Data Manager. This means that the data and formulas of a sheet table can now be added to a data manager table to keep the data more centralized for use in other areas of SpreadJS. This functionality also supports binding specific columns in the Data Manager table or auto-generating columns. Additionally, when importing an Excel file, the sheet tables can be converted to Data Manager tables with a new import flag:
Data Range Container
To aid the implementation of the Data Manager Table Binding, a new Data Range feature provides the ability to fully control a specific sheet range area and convert row/column coordinates to data table coordinates, allowing users to focus on an application’s business logic more easily.
Data Ranges can be used by following the steps below:
- Create a custom Data Provider instance.
- Choose a target range in a sheet.
- Use the Data Range Manager of the sheet (sheet.dataRanges) to add a Data Range.
Diagonal Lines Cell Type
A new cell type has been added to SpreadJS, which allows us to insert diagonal lines within a cell. This can be useful for designing table headers or separate areas in reports. These cells can display multiple text items divided by diagonal lines in different sections:
These cell types also support many different properties, including value types, display rules, line styles, text orientation, text wrapping and clipping, and right-to-left layouts.
Prevent Cell Editing with Property Setting
A new property called allowEditInCell has been added to prevent a cell from being edited. Setting this property to false will prevent a cell from going into edit mode if a user double-clicks on a cell or uses the keyboard to start editing. This can be useful if the cell should only be changed with cell types like a ComboBox or a CheckBox.
Horizontal Scrolling with Shift+Mouse Wheel
Prior to this release, vertical scrolling in SpreadJS was performed using the mouse wheel or the vertical scroll bar, but horizontal scrolling could only be accomplished using the horizontal scrollbar. With this release, we have added the ability to scroll horizontally by using Shift + Mouse Wheel.
Change Editor Status via Command
Two new commands have been added to SpreadJS that allow us to change the editor status. These commands are:
- StartEdit: Used to edit the active cell using edit mode
- ChangeEditorStatus: Used to switch the editor status between enter and edit mode when a cell is being edited
Search Cell Comments
The existing search functionality in SpreadJS supported the following:
- Cell Values
- Cell Formulas
- Cell Tags
With the v18 release, searching now supports searching inside comments in a worksheet. With this enhancement, the SpreadJS Designer Component Search Dialog now also supports searching cell comments and tags.
getUsedRange API Tag Type Support
To support searching cell tags, the getUsedRange API has been updated to support tag types through a Tag option in the GC.Spread.Sheets.UsedRangeType enumeration.
Check/Uncheck Filter Items with Keyboard
New keyboard shortcuts have been added for interaction with filters in SpreadJS. When a user opens a filter, they can use the following keys to interact with it:
- Spacebar: Check/Uncheck an item in a list when the list is focused
- Left Arrow Key: Collapse all child items of the current item in the list when the list is focused
- Right Arrow Key: Expand all child items of the current item in the list when the list is focused
- Up and Down Arrow Keys: Outside of the list box in a filter, this will switch the focus in the filter dialog
ComboBox CellType Data Source Binding Support
ComboBox CellTypes in SpreadJS now support binding to a table column. That table can occur as a data table in the data manager or as the result of a formula. A new dataBinding method has been added to the ComboBox CellType to support this:
var binding = { dataSource: '=SORT(UNIQUE(QUERY("Products", {"productName","productId"})))', text: 0, value: 1 };
var cellType1 = new GC.Spread.Sheets.CellTypes.ComboBox();
cellType1.dataBinding(binding);
activeSheet.getCell(1, 3).cellType(cellType1);
Table Resizing Event Cancel Option
To control table resizing by setting a limit for the number of rows or columns a table can have, the TableResizing event now offers a Cancel parameter. This allows us to programmatically set whether to stop the current resize action, and it is set to false by default.
Pivot Table Add-On
Change Data Source
Pivot Tables now support changing the data source and synchronizing the existing layout information to the new data source. The updateSource method is used for changing the current data source and is useful when more rows need to be added, or an existing dataset has to be modified. This can also be accomplished with the Designer UI:
Show Subtotal in Single Field
Similar to Excel, subtotal information can now be displayed for individual fields in a Pivot Table. The visibility of these subtotals can be controlled using the subtotalVisible method:
pivotTable.subtotalVisible("Date", true); // Value can be true or false
Filter Dialog Keyboard Operations Support
Like the standard filter dialog in SpreadJS, the filter dialog for PivotTables also now supports keyboard operations:
- Tab - Switch focus to next item
- Shift + Tab - Switch focus to previous item
- Up/Down Arrow Keys - Switch focus to previous/next sub-items
- Left/Right Arrow Keys - Expand or collapse sub-items
- Space - Check/Uncheck item when in list, or execute current selection option
- Enter - Save current settings or execute operation item where focus is located
- Esc - Close the current dialog
ReportSheet Add-On
Chart Support
ReportSheets now support charts using the DataCharts functionality, which is included with the ReportSheet Add-On.
var templateSheet = report.getTemplate();
var chart = templateSheet.dataCharts.add("reportChart1", 20, 20, 300, 400, GC.Spread.Sheets.DataCharts.DataChartType.column);Note: To utilize DataCharts outside of ReportSheets, a separate DataCharts Add-On license is required.
There are two types of charts that can be inserted into a ReportSheet:
Floating Chart
Float Charts are the standard way of displaying charts, where the position is based on the top left cell, and the chart floats above the cells. These charts support binding to a table in the Template Sheet or a table in the Data Manager.
Template Cell Chart
Cell Charts are charts that are set within cells in a template sheet, similar to sparklines. This can be useful for setting up multiple charts for specific fields of data, such as a chart for different regions:
Pagination
These charts also support the pagination of data, allowing you to split data into pages that can be cycled through.
Expand/Collapse All
The expand and collapse functionality for ReportSheets has been enhanced to support expanding and collapsing all cells and dependent cells of a specific cell.
// Get all the cells spilled by the year template cell.
const yearCells = reportSheet.getCells(1, 0);
const firstYearCell = yearCells[0];
// Collapse ALL: collapse the first year cell and it's descendant cells.
reportSheet.toggleCollapseState(firstYearCell.row, firstYearCell.col, 'Collapsed', true);
// Expand ALL: expand the first year cell and it's descendant cells.
reportSheet.toggleCollapseState(firstYearCell.row, firstYearCell.col, 'Expanded', true);Find Fields in DataSource Panel
When creating a ReportSheet, a panel will show on the left that shows all the registered tables in the DataManager. With the v18 release, the Data Source panel now supports searching the fields:
More Aggregate Function Options in Wizard
The SpreadJS ReportSheet Wizard aids in creating reports and configuring report settings in an easy-to-use UI. With the v18 release, we have added more aggregation options, specifically to help with Grouped and Cross reports:
Container Support for Design Templates
When creating ReportSheet templates, containers can now be added to specific ranges, helping users easily design reports. These containers have three different types, and all the fields are set into the containers one by one:
- List - Fields are set left-to-right
- Group - Fields are set left-to-right
- Cross - Fields are set top-to-bottom
Once the fields are set, field labels and styles are automatically generated.
Dropdown Selection Feature for Field Value Editor
With v18, SpreadJS now supports selecting parameters for ReportSheet fields through a drop-down box in the field value editor in the Designer. This is useful for values with parameters to choose from:
Calculation
New Aggregation Functions (GROUPBY, PIVOTBY, and PERCENTOF)
SpreadJS v18 supports new aggregation functions:
- GROUPBY - Create a summary of data by grouping along row fields and aggregating the associated values.
Demo - PIVOTBY - Create a summary of data by grouping along two axes and aggregating the associated values.
Demo - PERCENTOF - Sums the first argument and divides it by the SUM of the second argument. This is particularly useful with GROUPBY and PIVOTBY, as it can return percentages.
Demo
Function Reference
The Function Reference functionality (also known as Eta Reduced Lambda Functions) simplifies a function by eliminating unnecessary abstractions when it has no further operations to perform on its argument. This allows us to use abbreviated syntax when possible. This can be accomplished by defining custom names with the content containing a formula name, such as “SUM”:
// Add a custom name with the content as the function name.
spread.addCustomName("op", "SUM");
// Use the custom name as a formula.
sheet.setFormula(0, 1, "op(C1:C5)");This type of reduction can also be used with LET functions and LAMBDA functions.
Designer
Support for Searching Invisible Ranges
An enhancement has been made to the Find/Replace dialog in the Designer to add an “Ignore Hidden” option to support searching invisible ranges. When set to true, hidden ranges will be ignored when searching and included when set to false:
Frameworks
Runtime and Design Vue Wrapper TypeScript Support
The Vue wrapper for SpreadJS now includes an index.d.ts file to support TypeScript with Vue 3.
Angular 18 Support
SpreadJS now has support for Angular 18 with this release.
Collaboration Preview
The last feature that we have added is our Collaboration functionality which is in beta for the v18 release. This is a set of server-side components that facilitate real-time editing and collaboration of client components, like SpreadJS. We have created a robust collaboration plugin for SpreadJS that is built on that collaboration framework, and it enables you to implement features like auto-saving and version control to help create your own proprietary collaboration platforms.
To try out this preview, a demo is included with the SpreadJS v18 download in the ‘Samples’ folder.
Conclusion
We are excited for you to try out all these great features in the new release.
Be sure to download a trial of SpreadJS today!
For more information about these features, check out our Demos and Documentation.
Tags:
Related Blogs
Try Our JavaScript Spreadsheet Components
SpreadJS Resources
