Overview

The TableSheet lets users enable defining column by the options which will toggle the visibility of the add column button, modify column, remove column and set primary key context menu on the column header:

interface GC.Spread.Sheets.TableSheet.ITableSheetOptions {
  // other properties...
  enableDefineColumn?: boolean;
}

 
 
Column Types

interface GC.Data.IColumn {
    type: string, // the built-in column types: Number, Text, Formula, Checkbox, Date, Currency, Percent, Phone, Email, URL, Lookup, CreatedTime, ModifiedTime, Attachment, Select, Barcode
}

The descriptions of the supported column types:

 
 
Configuration

To communicate with the table, it should configure the remote APIs:

var apiForDataUrl = './orders', apiForColumnUrl = './tables/order/columns';
var orderTable = dataManager.addTable("orderTable", {
        remote: {
            read: {
                url: apiForDataUrl,
            },
            update: {
                url: apiForDataUrl,
                method: 'PUT'
            },
            create: {
                url: apiForDataUrl,
            },
            delete: {
                url: apiForDataUrl,
            },
            getColumns: {
                url: apiForColumnUrl
            },
            addColumn: {
                url: apiForColumnUrl,
                method: 'POST'
            },
            updateColumn: {
                url: apiForColumnUrl,
                method: 'PUT'
            },
            removeColumn: {
                url: apiForColumnUrl,
                method: 'DELETE'
            },
            // batch: {
            //     url: './batch',
            // }
        },
        autoSync: true,
        // batch: true
    });

And the column remote APIs could be handled by the self-defined functions which are similar as the Handle Requests.

 
Request and Response

type Column = {
    field: string, // the name of the field
    dataType: 'number' | 'string' | 'boolean', // the data type could be converted to another data type on the remote storage source
    defaultValue?: 'number' | 'string' | 'boolean' | 'undefined', // the static default value for remote storage source
    isPrimaryKey?: boolean, // indicates the field could be the primary
}
type ColumnData = {
    column: Column,
}
type ColumnWithDefaultData = {
    column: Column,
    data?: any[], // in some cases, the data provided by the client should sync to the remote storage source, for example, the column with a formula default value is added to the table has some records
}
type ModifiedColumnData = {
    column: Column,
    originalColumn: Column,
}
type BatchItemData = {
    type: "updateColumn" | "addColumn" | "removeColumn", // it only shows the options about the column changes, the whole changes include the column and data if exited
    column: Column,
    data?: any[],
    originalColumn?: Column,
}
type BatchResultData = {
    succeed: boolean,
    reason?: string,
}

 
 
The Commands

Besides defining through UI, there are also the commands to define the columns:

DefineColumn

The DefineColumn command could add a column:

/**
 * GC.Spread.Sheets.Commands.DefineColumn: { canUndo: boolean, execute(context: GC.Spread.Sheets.Workbook, options: {sheetName: string, col?: number, column: GC.Data.IColumn}): any}
 * Represents the command used to define the column.
 * @property {boolean} canUndo - indicates whether the command supports undo and redo operations.
 * @property {function} execute - performs an execute or undo operation.
 * The arguments of the execute method are as follows.
 * @param {GC.Spread.Sheets.Workbook} context The context of the operation.
 * @param {Object} options The options of the operation.
 * @param {string} options.sheetName The sheet name.
 * @param {number} [options.col] The specified col for inserting, optional.
 * @param {GC.Data.IColumn} options.column The defined column.
 * @param {boolean} isUndo `true` if this is an undo operation; otherwise, `false`.
 */
spread.commandManager().execute({
  cmd: "DefineColumn",
  sheetName: spread.getActiveSheetTab().name(),
  // col: 3, // optional
  column: {
    type: 'Number', // specify the column type
    value: 'Number1', // the column identifier of the column instance from the column type
    caption: '# Number1', // the display of the column for the culture
    style: { formatter: '#,##0.00_);[Red](#,##0.00)' } // the formatter could be specified or be converted from the UI operations
  } as GC.Data.IColumn,
});

ModifyColumn

The ModifyColumn command could change the column properties:

/**
 * field GC.Spread.Sheets.Commands.ModifyColumn: { canUndo: boolean, execute(context: GC.Spread.Sheets.Workbook, options: {sheetName: string, col: number, column: GC.Data.IColumn}): any}
 * Represents the command used to modify the column.
 * @property {boolean} canUndo - indicates whether the command supports undo and redo operations.
 * @property {function} execute - performs an execute or undo operation.
 * The arguments of the execute method are as follows.
 * @param {GC.Spread.Sheets.Workbook} context The context of the operation.
 * @param {Object} options The options of the operation.
 * @param {string} options.sheetName The sheet name.
 * @param {number} options.col The specified col.
 * @param {GC.Data.IColumn} options.column The modified column.
 * @param {boolean} isUndo `true` if this is an undo operation; otherwise, `false`.
 */
spread.commandManager().execute({
  cmd: "ModifyColumn",
  sheetName: spread.getActiveSheetTab().name(),
  col: 3,
  column: {
    type: 'Text', // specify the column type
    value: 'Text1', // the column identifier of the column instance from the column type
    caption: 'A Text1', // the display of the column for the culture
  } as GC.Data.IColumn,
});

RemoveColumn

The RemoveColumn command could remove the column:

/**
 * field GC.Spread.Sheets.Commands.RemoveColumn: { canUndo: boolean, execute(context: GC.Spread.Sheets.Workbook, options: {sheetName: string, col: number}): any}
 * Represents the command used to remove the column.
 * @property {boolean} canUndo - indicates whether the command supports undo and redo operations.
 * @property {function} execute - performs an execute or undo operation.
 * The arguments of the execute method are as follows.
 * @param {GC.Spread.Sheets.Workbook} context The context of the operation.
 * @param {Object} options The options of the operation.
 * @param {string} options.sheetName The sheet name.
 * @param {number} options.col The specified col.
 * @param {boolean} isUndo `true` if this is an undo operation; otherwise, `false`.
 */
spread.commandManager().execute({
  cmd: "RemoveColumn",
  sheetName: spread.getActiveSheetTab().name(),
  col: 3,
});