In the SpreadJS v13 release, the table functionality has been enhanced. These enhancements include resize handlers, scrolling headers, changes to context menus, table selection, tab navigation, and a new totals row. These additions help make the tables in SpreadJS more Excel-like, they are easier to use, and allow further customization in end-users' applications.
Let’s take a look at these features:
1). Auto Expand
SpreadJS now supports auto-expand features for unbound tables, just like Microsoft Excel. The following scenarios are now supported using auto expand:
a). Input Users can enter data in the next row or next column immediately to the right or below the table area. The table will be automatically expanded to include the new data.
b). copy-cut/paste When performing a copy-cut/paste operation, the table will automatically expand if the paste area exceeds the range of the table, and there is no value below/right of the data.
c). drag-fill/drag-drop The auto expand feature is also supported for drag-fill/drag-drop operations.
2). Conditional formats and data validation range expand
Spread supports conditional formatting and data validation when the table layout changed using one of the following operations:
- table insert rows
- table insert columns
- table auto expand
- table resize
If the existing table has conditional formatting/data validation rules and the newly added table row or column intersects or is adjacent to the range of the conditional format/data validation, the rules will automatically be applied to the newly added data.
3). Resize Handle
SpreadJS also supports resizing handler to support dragging the table to the required size. Resize handler could be toggled using the showResizeHandle method like:
let table = sheet.tables.add("table1", 0, 0, 3, 3); // enable resize handler
table.showResizeHandle(true);
// disable resize handler table.showResizeHandle(false);
4). Header scrolling
In v13, SpreadJS has also introduced support to show the table column headers in the spreadsheet headers, when the table headers are scrolled out of view.
If the user scrolls the table with many rows normally the table headers will scroll out of view of the user. Now, when this happens, you can fix the table headers to the spreadsheet column headers so the users can still see and perform operations on the table, such as filtering.
5). Table Context Menu Enhancement
SpreadJS now supports inserting or deleting table rows and columns using the context menu.
6). Table selection and navigation
SpreadJS v13 introduces Excel-like table selection. You can select a table column by hovering over the top edge of the table column header, until the cursors changes. Then, click the header to select the table column. Similarly, we could select the left-top corner of the table to select the table.
In addition to the table selection, v13 also introduces better navigation support within a table. Now you can press the tab key to select the next cell or shift + tab to select the previous cell.
7). Total row
SpreadJS v13 now supports adding a table totals row,like Excel. SpreadJS also supports total row formulas. The user can select the dropdown list to quickly choose and insert the desired summary function.
Use the showFooter method to enable/disable the total row as follows:
// create table
let table = sheet.tables.add(“table”, 1, 1, 5, 5);
// enable footer row
table.showFooter(true);
// control visibility of formula dropdown for footer row
table.useFooterDropDownList(true);
8). Column binding enhancements
SpreadJS V13 has added enhancement support for table column binding. You can now bind the cellType, formatter, and value convert function to the table column. To bind columns, use the bind/bindColumns method as shown in the following code snippet:
// create table
let table = sheet.tables.add(“table”, 1, 1, 5, 5);
// define column
let columns = [];
// bind formatter
columns.push(new GC.Spread.Sheets.Tables.TableColumn(1, null, “Formatted Date”, “M/dd/yyyy));
// bind cell type
columns.push(new GC.Spread.Sheets.Tables.TableColumn(2, null, “Check box cell type”, null, new GC.Spread.Sheets.CellTypes.CheckBox));
Download Sample
Sharad Tomer