TableSheet Data Range

When you register a TableSheet into a worksheet area as a data range, then TableSheet and the data range are considered as same instances. Any changes made to the TableSheet will be automatically reflected in the data range and vice versa. This ensures consistency across both representations.

The Data Range inherits most TableSheet features like sorting, filtering, and styling that are available in the TableSheet. This allows you to leverage the powerful features of TableSheet within specific areas of a worksheet, providing a flexible and consistent way to manage and display data. Therefore, if the source TableSheet is removed, the corresponding data range will also be removed.

The following sample code registers a TableSheet into a worksheet as a data range.

var tableSheetName = "TableSheet1";
var dataRangeName = "TableSheetDataRange";
var dataRangeStartRow = 2;
var dataRangeStartCol = 2;
sheet.dataRanges.add(dataRangeName, tableSheetName, new GC.Spread.Sheets.Range(dataRangeStartRow, dataRangeStartCol, -1, -1));

If you want to stick the TableSheet header to keep it visible while scrolling, set the sticky options with a positive number. The number of sticky rows will be automatically determined based on the number of rows in the TableSheet’s column header. This way you don’t have to manually specify the number of sticky rows.

The following sample code displays the sticky TableSheet header.

var options = { sticky: { top: 1 } };
sheet.dataRanges.add(dataRangeName, tableSheetName, new GC.Spread.Sheets.Range(dataRangeStartRow, dataRangeStartCol, -1, -1), options);

Note: The pinned rows are not included in the sticky headers.

Policies for TableSheet Data Range

When using a TableSheet as a data range, the following policies are applied:

• For the data range:

  • The size of the TableSheet data range increases or decreases automatically.

  • The range.rowCount and range.colCount properties are ignored.

  • Hidden columns and rows in the TableSheet are not displayed in the data range.

  • Always shows the maximum number of rows and columns that can fit within the worksheet.

• For exporting to Excel:

  • The TableSheet data range is exported as a table in Excel.

  • The corner and all the columns of the TableSheet row header are clipped.

  • If the TableSheet has multiple header rows, only the last row of the multiple headers is exported without spans.

  • The outline group layout is exported as a condensed group layout.

Limitation

The following TableSheet features are not supported in the data range:

• Free header areas.

• Column header Fit Mode, such as stacked column headers.

• Definition of columns with specific custom properties or behaviors.