SpreadJS v13 Table Enhancements

November 06 2019

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.

SpreadJS v13 Table Enhancements

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.

Auto Expand

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.

Conditional formats and data validation

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);

Resize Handle

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.

Header Scrolling

SpreadJS now supports inserting or deleting table rows and columns using the context menu.

Table Context Menu Enhancement

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.

Table selection and navigation

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));