Resize Tables

SpreadJS supports the table resizing feature just like in Excel.

Table Resizing Indicator - The resizing indicator is displayed at the lower-right corner of the table provided the value of the showResizeHandle method is set to true.

Table Resizing Handler - The table resizing handler is displayed when users hover the mouse over the table resizing indicator.

While working with tables in spreadsheets, users can use the showResizeHandle method to get or set a value that indicates whether to display the resize handle for tables or not.

When the value of the showResizeHandle method is set to true, the table displays a small resizing indicator at the lower-right corner of the table. Afterward, users can simply drag the table using the table resizing handler to resize the table to the desired size as depicted in the below image.

Resize Table to a Range

Users can resize tables to a larger range as well as a smaller range depending upon their specific requirements.

• Larger Range - When the table is resized by moving the table resizing handler towards the right direction or bottom direction; the cells in the expanded area will copy the same style and new columns or rows will be added respectively with new names.

• Smaller Range - When the table is resized by moving the table resizing handler towards the left direction or top direction; the data values of the table will remain intact but the style will be removed from the other columns or rows respectively.

Resize Table with Header Row and Total Row

Users can also resize the tables that contain a header row and a total row.

When the table containing a header row and total row is resized by moving the table resizing handler in the right direction, then the cells in the expanded area will copy the same style and new columns will be added with new names.

For the bottom direction, the row count of the resized range contains the header row and total row. For the left direction, the data values in the table will be reserved but the style will be removed from other columns. For the top direction, the row count will also be resized (as resized range row count + 1).

Resize Table Containing Formulas

When the table is resized, the selected range remains intact. After resizing the table containing styles and data, formulas will also remain intact and will apply automatically to the new range as well. The formula referring to the table range will be updated after resizing the table.

The following points must be kept in mind while resizing tables in SpreadJS:

• Users can only resize in either the column direction or row direction at the same time.

• The minimum size of the table is the cell range containing one table row and one table column excluding the Header Row and total row.

• The maximum size of the table is the cell range containing table row count, table column count, sheet row count, and sheet column count.

• If there are values in the expanded area, those values will be automatically filled in the table.

• In Excel, if the users resize a table that contains a total row; then all the values below the existing table range will fill up to the expanded range. However, in SpreadJS, the total row will overlap the last row which is below the existing table range.

• The table resizing feature is also supported when the cells in your spreadsheets contain sparklines.

Note: Tables can't be resized in the following scenarios:

• In table binding, the table resizing handler is disabled.

• When a table is resized over a span cell, the error "Cannot complete operation: A table cannot overlap with a table or merged cells" is thrown to indicate that the operation is invalid.

• When a table containing a total row is resized to bottom direction and filtered columns is resized, the error: "Cannot shift cells in a filtered range or table" is thrown.

• When a table is resized in the vertical direction and the table is filtered, the error: "Cannot resize a table that's adjacent to a hidden row or column" is thrown to indicate that the operation is invalid.

• When a table containing hidden rows and columns is resized in the vertical direction, the error "Cannot resize a table that's adjacent to a hidden row or column" is thrown to indicate that the operation is invalid.

• When users resize a table over another table, the error "Cannot complete operation: Operations that include cells both inside and outside a table range, and operation that affect cells that overlap multiple tables are not allowed" is thrown to indicate that the operation is invalid.

The following code sample shows how to enable the table resizing handler to resize tables while working with spreadsheets.

// Initializing Spread
var spread = new GC.Spread.Sheets.Workbook(document.getElementById('ss'), { sheetCount: 1 });

// Get the activesheet
var activeSheet = spread.getActiveSheet();
// Add Table
var table = activeSheet.tables.add("table1", 1, 1, 4, 4, GC.Spread.Sheets.Tables.TableThemes.light1);
// Enable Table Resize Handler by setting table's showResizeHandle method to true
table.showResizeHandle(true);
activeSheet.getCell(1, 1).text("First Name");
activeSheet.getCell(1, 2).text("Last Name");
activeSheet.getCell(1, 3).text("Score");
activeSheet.getCell(1, 4).text("Position");
activeSheet.getCell(2, 1).text("Alexa");
activeSheet.getCell(2, 2).text("Wilder");
activeSheet.getCell(2, 3).text("90");
activeSheet.getCell(2, 4).text("Web Developer");
activeSheet.getCell(3, 1).text("Victor");
activeSheet.getCell(3, 2).text("Wooten");
activeSheet.getCell(3, 3).text("70");
activeSheet.getCell(3, 4).text(".NET Developer");
activeSheet.getCell(4, 1).text("Ifeoma");
activeSheet.getCell(4, 2).text("Mays");
activeSheet.getCell(4, 3).text("85");
activeSheet.getCell(4, 4).text("Sales Manager");
for (var i = 0; i < 3; i++)
activeSheet.setColumnWidth(i, 90.0, GC.Spread.Sheets.SheetArea.viewport);
activeSheet.setColumnWidth(4, 120);