[{"id":"53ae7f99-150b-4b50-9f72-02f7f2ea5efe","tags":[{"product":null,"links":null,"id":"c3c0ef13-c7a9-4649-9fcb-23b3abec175d","name":"New","color":"Red","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"e371599e-67ae-4635-802b-0ada531e5b5b","tags":[{"product":null,"links":null,"id":"2f377cfe-f512-431a-895b-f89af32041ce","name":"Upd","color":"Blue","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"f309ec90-559b-42bf-955b-492431019196","tags":[{"product":null,"links":null,"id":"c3c0ef13-c7a9-4649-9fcb-23b3abec175d","name":"New","color":"Red","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"28d36d47-c582-4210-a848-5ba48491bb80","tags":[{"product":null,"links":null,"id":"2f377cfe-f512-431a-895b-f89af32041ce","name":"Upd","color":"Blue","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"9d13df60-758a-4cc3-9cda-8d6234e00821","tags":[{"product":null,"links":null,"id":"2f377cfe-f512-431a-895b-f89af32041ce","name":"Upd","color":"Blue","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"2e473c2c-8b35-40de-bdce-f21478f434bc","tags":[{"product":null,"links":null,"id":"c3c0ef13-c7a9-4649-9fcb-23b3abec175d","name":"New","color":"Red","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"e19a99e0-5b6f-4c8b-b872-47b3e78011bf","tags":[{"product":null,"links":null,"id":"2f377cfe-f512-431a-895b-f89af32041ce","name":"Upd","color":"Blue","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"2f718a98-6d54-4dc1-89c3-6b87cfabd16b","tags":[{"product":null,"links":null,"id":"2f377cfe-f512-431a-895b-f89af32041ce","name":"Upd","color":"Blue","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"df066912-4ce1-4484-b00c-86b34a6e977b","tags":[{"product":null,"links":null,"id":"2f377cfe-f512-431a-895b-f89af32041ce","name":"Upd","color":"Blue","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"1fcab497-8c1f-413e-bce0-f3ae72229e85","tags":[{"product":null,"links":null,"id":"2f377cfe-f512-431a-895b-f89af32041ce","name":"Upd","color":"Blue","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"3d90b977-451c-42ce-a26a-1e32c1082471","tags":[{"product":null,"links":null,"id":"c3c0ef13-c7a9-4649-9fcb-23b3abec175d","name":"New","color":"Red","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"6433ad22-3173-436a-8ff9-355a17142bc5","tags":[{"product":null,"links":null,"id":"2f377cfe-f512-431a-895b-f89af32041ce","name":"Upd","color":"Blue","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"e82311a5-f99e-4075-8710-9a76a2da67a7","tags":[{"product":null,"links":null,"id":"c3c0ef13-c7a9-4649-9fcb-23b3abec175d","name":"New","color":"Red","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"7bebaae0-d5e2-4db3-ab34-644272d2aed6","tags":[{"product":null,"links":null,"id":"2f377cfe-f512-431a-895b-f89af32041ce","name":"Upd","color":"Blue","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"2c77a1e3-7af7-4dca-88aa-ee59b41d7896","tags":[{"product":null,"links":null,"id":"2f377cfe-f512-431a-895b-f89af32041ce","name":"Upd","color":"Blue","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"5a3f52b5-5b23-473e-bdbc-906381b9a948","tags":[{"product":null,"links":null,"id":"c3c0ef13-c7a9-4649-9fcb-23b3abec175d","name":"New","color":"Red","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"299d63a8-7f0b-46a4-a152-9a7ca85a2170","tags":[{"product":null,"links":null,"id":"c3c0ef13-c7a9-4649-9fcb-23b3abec175d","name":"New","color":"Red","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"906b4555-b1b6-4ddb-a290-c13cb8a153a0","tags":[{"product":null,"links":null,"id":"2f377cfe-f512-431a-895b-f89af32041ce","name":"Upd","color":"Blue","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"efa3eb50-4277-46ee-b451-c5096e889fff","tags":[{"product":null,"links":null,"id":"2f377cfe-f512-431a-895b-f89af32041ce","name":"Upd","color":"Blue","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"1d9b7a41-588f-47d1-bb81-f46146e062fb","tags":[{"product":null,"links":null,"id":"2f377cfe-f512-431a-895b-f89af32041ce","name":"Upd","color":"Blue","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"c1687a0f-e3a0-4074-996c-94cbacf49f29","tags":[{"product":null,"links":null,"id":"2f377cfe-f512-431a-895b-f89af32041ce","name":"Upd","color":"Blue","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"269fc082-c3b5-4208-a987-c1a3c1dee620","tags":[{"product":null,"links":null,"id":"2f377cfe-f512-431a-895b-f89af32041ce","name":"Upd","color":"Blue","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"cb76b7de-9b29-4d6b-b644-de3c3c0a1262","tags":[{"product":null,"links":null,"id":"2f377cfe-f512-431a-895b-f89af32041ce","name":"Upd","color":"Blue","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"4dc9d5da-9bf3-4baf-9fae-6b34511062a4","tags":[{"product":null,"links":null,"id":"c3c0ef13-c7a9-4649-9fcb-23b3abec175d","name":"New","color":"Red","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"e4e74cf8-8702-45a6-a536-86d03b66af49","tags":[{"product":null,"links":null,"id":"2f377cfe-f512-431a-895b-f89af32041ce","name":"Upd","color":"Blue","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"abe88d62-9eb6-4113-89f3-abb0cff27c3a","tags":[{"product":null,"links":null,"id":"2f377cfe-f512-431a-895b-f89af32041ce","name":"Upd","color":"Blue","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"f3751417-d894-4cf1-8a8c-c2e7391dd4b1","tags":[{"product":null,"links":null,"id":"2f377cfe-f512-431a-895b-f89af32041ce","name":"Upd","color":"Blue","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"49ae1d04-aba0-4311-8548-df297a5aedc0","tags":[{"product":null,"links":null,"id":"2f377cfe-f512-431a-895b-f89af32041ce","name":"Upd","color":"Blue","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"861d71ff-44db-4563-a24c-e1a389708c19","tags":[{"product":null,"links":null,"id":"c3c0ef13-c7a9-4649-9fcb-23b3abec175d","name":"New","color":"Red","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"2f6715bc-8b05-4653-8ec5-0dd835dc6035","tags":[{"product":null,"links":null,"id":"2f377cfe-f512-431a-895b-f89af32041ce","name":"Upd","color":"Blue","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"d2f5c978-f4e4-4c18-ab43-e8fbbc6ad92b","tags":[{"product":null,"links":null,"id":"c3c0ef13-c7a9-4649-9fcb-23b3abec175d","name":"New","color":"Red","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"e183d612-0813-4e0f-b217-308d71c14637","tags":[{"product":null,"links":null,"id":"c3c0ef13-c7a9-4649-9fcb-23b3abec175d","name":"New","color":"Red","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"f8f319ea-ec97-4d69-a862-c6a17191f0f7","tags":[{"product":null,"links":null,"id":"c3c0ef13-c7a9-4649-9fcb-23b3abec175d","name":"New","color":"Red","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"d895fc4b-55bb-46ee-bd74-7a0abb51650c","tags":[{"product":null,"links":null,"id":"c3c0ef13-c7a9-4649-9fcb-23b3abec175d","name":"New","color":"Red","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"680fba3c-ad50-41a6-bc56-acd4f229de8f","tags":[{"product":null,"links":null,"id":"c3c0ef13-c7a9-4649-9fcb-23b3abec175d","name":"New","color":"Red","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"1234e85c-d175-4f94-ac83-b53b5e3596d2","tags":[{"product":null,"links":null,"id":"c3c0ef13-c7a9-4649-9fcb-23b3abec175d","name":"New","color":"Red","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"8fb554aa-3192-402b-9d6b-1ec2b4b99ea0","tags":[{"product":null,"links":null,"id":"c3c0ef13-c7a9-4649-9fcb-23b3abec175d","name":"New","color":"Red","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"aff9d5e3-7e18-4287-892f-8d7844e9ddf2","tags":[{"product":null,"links":null,"id":"2f377cfe-f512-431a-895b-f89af32041ce","name":"Upd","color":"Blue","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"6a23926e-a2ac-4c07-ba79-2f7751f098ee","tags":[{"product":null,"links":null,"id":"2f377cfe-f512-431a-895b-f89af32041ce","name":"Upd","color":"Blue","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"51c9c955-8c66-45d8-bb0c-718aee1073da","tags":[{"product":null,"links":null,"id":"c3c0ef13-c7a9-4649-9fcb-23b3abec175d","name":"New","color":"Red","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"6174a29f-1d15-4a80-a860-d28fdecca505","tags":[{"product":null,"links":null,"id":"2f377cfe-f512-431a-895b-f89af32041ce","name":"Upd","color":"Blue","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"51709a04-b2cd-4465-b2a2-da7beb2e4f15","tags":[{"product":null,"links":null,"id":"c3c0ef13-c7a9-4649-9fcb-23b3abec175d","name":"New","color":"Red","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"897b8725-abb5-428a-bd7a-a610fae8e28c","tags":[{"product":null,"links":null,"id":"c3c0ef13-c7a9-4649-9fcb-23b3abec175d","name":"New","color":"Red","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"c00c0609-ac23-4859-8442-be43ed745ad3","tags":[{"product":null,"links":null,"id":"c3c0ef13-c7a9-4649-9fcb-23b3abec175d","name":"New","color":"Red","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"0996d76b-b253-49a7-9ea8-89d81ce0d42f","tags":[{"product":null,"links":null,"id":"c3c0ef13-c7a9-4649-9fcb-23b3abec175d","name":"New","color":"Red","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"3c007776-7473-4203-8cc1-9ed79bb512df","tags":[{"product":null,"links":null,"id":"2f377cfe-f512-431a-895b-f89af32041ce","name":"Upd","color":"Blue","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"4bb1ced3-7a10-4719-854b-513729b31e53","tags":[{"product":null,"links":null,"id":"2f377cfe-f512-431a-895b-f89af32041ce","name":"Upd","color":"Blue","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"53155f14-d53e-4d53-a087-0a32d9b088e8","tags":[{"product":null,"links":null,"id":"2f377cfe-f512-431a-895b-f89af32041ce","name":"Upd","color":"Blue","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"279e57c0-0954-49d6-9076-2133a204e24f","tags":[{"product":null,"links":null,"id":"c3c0ef13-c7a9-4649-9fcb-23b3abec175d","name":"New","color":"Red","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"e9c38f09-cfd3-4a78-b2cf-412e3c1b5945","tags":[{"product":null,"links":null,"id":"2f377cfe-f512-431a-895b-f89af32041ce","name":"Upd","color":"Blue","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"754efafe-744e-452e-9e46-833c9b36c763","tags":[{"product":null,"links":null,"id":"2f377cfe-f512-431a-895b-f89af32041ce","name":"Upd","color":"Blue","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"be5fe8a1-1660-4dc4-b7fb-ceb038047086","tags":[{"product":null,"links":null,"id":"2f377cfe-f512-431a-895b-f89af32041ce","name":"Upd","color":"Blue","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]}]
        
(Showing Draft Content)

Define Columns

TableSheet in SpreadJS allows you to add, update, or remove a column with meaningful column types to design the table at runtime.

To display the Add Column button behind the last column, set the enableDefineColumn TableSheet option to true. On right-clicking, it displays the context menu with options to modify, remove, and set the primary key of the column as depicted in the image below.


ts-definecolumn


The following sample code shows how to enable the Add Column button in the TableSheet.

tableSheet.options.enableDefineColumn = true;

Built-In Column Types

TableSheet allows you to define Column Type with predefined column properties that are categorized according to the type of column. Here is the list of built-in column types- Number, Text, Formula, Checkbox, Date, Currency, Percent, Phone, Email, URL, Lookup, CreatedTime, ModifiedTime, Attachment, Select, and Barcode.

The main properties defined for the column types are as follows:

  • Value (Required): Includes column value but this field cannot hold formula or relation if its type is not Formula or Lookup type.

  • Caption (Optional): Includes the display name for the column header.

  • Type (Required): Allows to select a built-in column type. Some of the built-in columns are displayed in the image below.


    ts-definecolumn-button

Data types and sample column options of all the supported column types are listed in the table below.

Column Type

Data Type

Sample Column Options

Number

number

{

    value: "Number",

    type: "Number",

    caption: "Number",

    dataType: "number",

    defaultValue: 0,

    style: {formatter: "0.00", mask: { pattern: "0{0,}" } }

}

Text

string

{

    value: "Text",

    type: "Text",

    caption: "Text",

    dataType: "string"

}

Formula

depends on the result

{

    name: "NamedFormula", // it's a name of the formula for the Referencing Calculated Fields which will be a named formula

    value: "=[@Amount]*[@UnitPrice]",

    type: "Formula",

    caption: "Total Sales",

    dataType: "formula",

    style: {formatter: "0.00"}

    readonly: true,

}

Lookup

depends on the related

{

    value: "customer.ContactName",

    type: "Lookup",

    caption: "Customer",

    readonly: true,

}

Date

date

type DateTimePickerOptions = {

    showTime: boolean;

    calendarPage: "year" | "month" | "day";

    startDay: "monday" | "tuesday" | "wednesday" | "thursday" | "friday" | "saturday" | "sunday";

};

{

    value: "Date",

    type: "Date",

    caption: "Date",

    dataType: "date",

    style: {

        formatter: "m/d/yyyy",

        cellButtons:[{

            buttonBackColor: "transparent",

            command: "openDateTimePicker",

            enabled: true,

            visibility: 'always',

            imageType: "dropdown",

            useButtonStyle: false,

        }],

        dropDowns:[{

            type: "dateTimePicker",

            // The cell type is DateTimePickerOptions

            option: {

                calendarPage: "day",

                showTime: false,

                startDay: "sunday",

            }

        }]

    }

}

Checkbox

boolean

type CheckboxOptions = {

    type: "checkbox";

    caption: string;

    textTrue: string;

    textIndeterminate: string;

    textFalse: string;

    textAlign: "top" | "bottom" | "left" | "right";

    isThreeState: boolean;

    boxSize: number;

};

{

    value: "Checkbox",

    type: "Checkbox",

    caption: "Checkbox",

    dataType: "boolean",

    style: {

        // The cell type is CheckboxOptions

        cellType: {

            type: "checkbox"

        }

    }

}

Select

depends on the option

type ItemOptions = {

    text: string;

    value: any;

};

type ComboBoxOptions = {

    type: "combobox";

    editorValueType: "text" | "index" | "value";

    items: string[] | ItemOptions[];

    itemHeight: number;

    maxDropDownItems: number;

    editable: boolean;

};

{

    value: "Select",

    type: "Select",

    caption: "Select",

    dataType: "number",

    style: {

        // The cell type is ComboBoxOptions

        cellType: {

            type: "combobox",

        }

    }

}

Currency

number

{

    value: "Currency",

    type: "Currency",

    caption: "Currency",

    dataType: "number",

    style: {formatter: "[$$-409]#,##0.00"}

}

Percent

number

{

    value: "Percent",

    type: "Percent",

    caption: "Percent",

    dataType: "number",

    style: {formatter: "0%"}

}

Phone

string

{

    value: "Phone",

    type: "Phone",

    caption: "Phone",

    dataType: "string",

    style: {

        mask: {

            pattern: "1 \(0{3}\) 0{3}-0{4}"

        }

    }

}

Email

string

{

    value: "Email",

    type: "Email",

    caption: "Email",

    dataType: "string",

    style: {

        mask: {

            pattern: "[a0.\-]{1,}@[a0.\\-]{1,}.(com|cn|gov|edu)"

        }

    }

}

URL

string

type HyperlinkOptions = {

    type: "hyperlink";

    linkColor: ColorString;

    visitedLinkColor: ColorString;

    text: string;

    linkToolTip: string;

    target: "blank" | "self" | "parent" | "top";

    activeOnClick: boolean;

};

{

    value: "URL",

    type: "URL",

    caption: "URL",

    dataType: "string",

    style: {

        // The cell type is HyperlinkOptions

        cellType: {

            type: "hyperlink"

        }

    }

}

CreatedTime

date

{

    value: "CreatedTime",

    type: "CreatedTime",

    caption: "CreatedTime",

    dataType: "date",

    defaultValue: "=NOW()",

    readonly: true,

    trigger: {

        when: "onNew",

        formula: "=NOW()",

    },

    style: {

        formatter: "m/d/yyyy",

        // not necessary options(cell buttons and dropDowns)

        cellButtons:[{

            buttonBackColor: "transparent",

            command: "openDateTimePicker",

            enabled: true,

            visibility: 'always',

            imageType: "dropdown",

            useButtonStyle: false,

        }],

        dropDowns:[{

            type: "dateTimePicker",

            option: {

                calendarPage: "day",

                showTime: false,

                startDay: "sunday",

            }

        }]

    }

}

ModifiedTime

date

{

    value: "ModifiedTime",

    type: "ModifiedTime",

    caption: "ModifiedTime",

    dataType: "date",

    readonly: true,

    trigger: {

        when: "onNewAndUpdate",

        formula: "=NOW()",

        fields: "*",

    },

    style: {

     formatter: "m/d/yyyy",

     // not necessary options(cell buttons and dropDowns)

      cellButtons:[{

        buttonBackColor: "transparent",

        command: "openDateTimePicker",

        enabled: true,

        visibility: 'always',

        imageType: "dropdown",

        useButtonStyle: false,

        }],

        dropDowns:[{

            type: "dateTimePicker",

            option: {

                calendarPage: "day",

                showTime: false,

                startDay: "sunday",

            }

        }]

    }

}

Attachement

object

type FileUploadOptions = {

    type: "fileUpload";

    maxSize?: number;

    accept?: string;

    isPreviewEnabled?: boolean;

    isDownloadEnabled?: boolean;

    isClearEnabled?: boolean;

    marginTop?: number;

    marginRight?: number;

    marginBottom?: number;

    marginLeft?: number;

    valuePath?: string;

};

{

    value: "Attachment",

    type: "Attachment",

    caption: "Attachment",

    style: {

    // The cell type is FileUploadOptions

        cellType: {

            type: "fileUpload"

        }

    }

}

Barcode

depends on the input

{

    value: "Barcode",

    type: "Barcode",

    caption: "Barcode",

    defaultValue: 0,

    style: {

        formatter: "=BC_QRCODE([@Barcode],\"#000000\",\"#FFFFFF\",\"L\",2,\"auto\",\"auto\",false,0,,\"UTF-8\",,,,)"

    }

}

Remote Configuration

SpreadJS allows you to communicate with the remote table using custom functions as mentioned below.

Custom Functions

Description

SampleDefinition

getColumns

Retrieve the columns from the table.

getColumns: {  

    url: apiColumnUrl   }

addColumn

Add a column to the table.

addColumn: {

    url: apiColumnUrl,

    method: 'POST'   }

updateColumn

Update the properties of a column in the table.

updateColumn: {

    url: apiColumnUrl,

    method: 'PUT'    }

removeColumn

Remove a column from the table.

removeColumn: {

    url: apiColumnUrl,

    method: 'DELETE'   }

batch

Changes are posted together in the batch mode.

batch: {

    url: apiUrl + 'Collection'  }

The appropriate request and response linked to the custom functions during their interaction with the data structure are as follows:

Custom Functions

Request Data

Response Data

getColumns

None

Column[]

addColumn

ColumnWithDefaultData

ColumnData

updateColumn

ModifiedColumnData

ModifiedColumnData

removeColumn

ColumnData

ColumnData

batch

BatchItemData[]

BatchResultData[]

The column remote APIs can be handled by self-defined functions similar to Handle Requests in the autoSync and batch modes are as follows:

var orderTable = dataManager.addTable("orderTable", {
        remote: {
            read: function(): Promise<any[]>{
                return Promise.resolve([...])
            },
            getColumns: function(): Promise<Column[]>{
                return Promise.resolve([...])
            },
            addColumn: function(change): Promise<ny>{
                return Promise.resolve()
            },
            updateColumn: function(change): Promise<any>{
                return Promise.resolve();
            },
            removeColumn: function(change): Promise<any>{
                return Promise.resolve();
            },
            // batch: function(changes): Promise<any>{
            //   return Promise.resolve([...]);
            // },
        },
        autoSync: true,
        // batch: true
 });

When the autoSync mode is enabled, any changes in the columns of TableSheet will sync to the remote table immediately. However, when the batch mode is enabled, the column changes will be stored in the local. Then on calling the submitChange command, the changed column information and changed data information are sent to the remote table together.

[
    { "type": "updateColumn", "column": { field: 'columnName', dataType: 'number', ...otherproperties }, "originalColumn": { field: 'columnName0', ...otherproperties } },
    { "type": "addColumn", "column": { field: 'columnName1', dataType: 'number', ...otherproperties }, },
    { "type": "removeColumn", "column": { field: 'columnName2', ...otherproperties }, },
   
    { "type": "delete", "dataItem": { ...} },
    { "type": "insert", "dataItem": { ...} },
    { "type": "update", "dataItem": { ...}, "oldDataItem": { ...}, "sourceIndex": 1 }
]

Whereas, on calling the cancelChange command, it cancels all the changes. For the batch mode, the getChanges command on the TableSheet retrieves the column and data changes together.

Commands to Define Columns

SpreadJS also provides functionality to add, update, or remove columns using commands like DefineColumn, ModifyColumn, and RemoveColumn. These commands enable you to add, update, and delete columns from a table by implementing their properties such as value, dataType, defaultValue, isPrimaryKey, type, or name from the IColumn interface.

Let's discuss some code samples to implement each of these commands.

Define Column

The following sample code shows how to implement the DefineColumn command.

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

Modify Column

The following sample code shows how to implement the ModifyColumn command.

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

Remove Column

The following sample code shows how to implement the RemoveColumn command.

spread.commandManager().execute({
  cmd: "RemoveColumn",
  sheetName: spread.getActiveSheetTab().name(),
  col: 3,
});

Define Custom Columns

TableSheet provides the ability to customize the column types or add some special column types using the self-defined commands such as defineColumnCommand, submitDefineColumnCommand or update the built-in columns using columnTypeItems command.

The following code sample shows how to add some special column types or update the built-in column types.

// Select some column types to show
const items = tableSheet.options.columnTypeItems.filter((item)=>item.name === 'Select' || item.name === 'Attachment' || item.name === 'Barcode' || item.name === 'CreatedTime' || item.name === 'ModifiedTime' || item.name === 'Currency')

tableSheet.options.columnTypeItems = tableSheet.options.columnTypeItems.slice(0, 4).concat(items);

// Add self-defined column type
tableSheet.options.columnTypeItems.unshift({ name: 'CustomizingType', text: 'Progress', iconClass: 'gc-defined-column-type-icon-number'
});

The defineColumnCommand specifies the command to replace the default command that opens the defined column popup for the Add Column button or Modify Column menu item.  

The following sample code shows how to implement defineColumnCommand.

// Handle opening defined column popup
const defineCommand = tableSheet.options.defineColumnCommand;
tableSheet.options.defineColumnCommand = 'defineColumnCustom';
  spread.commandManager().register('defineColumnCustom', {
    canUndo: false,
    execute: function (context, options) {
    // it could build self-defined UI on the modify/add column triggered
    // opening the default UI for the sample
       options.cmd = defineCommand;
       spread.commandManager().execute(options);
    }
});

The submitDefineColumnCommand specifies the command for submitting defined column options when the submit button is selected for the column defined popup.

The following sample code shows how to implement submitDefineColumnCustom.

// Handle submitting defined column options
tableSheet.options.submitDefineColumnCommand = 'submitDefineColumnCustom';
spread.commandManager().register('submitDefineColumnCustom', {
  canUndo: false,
  execute: function (context, options) {
    if (options.command === 'DefineColumn' && options.column.type === 'CustomizingType') {
        options.column.defaultValue = 0.1;
        options.column.style = {
          formatter: "=HBARSPARKLINE([@"+options.column.value+"], \"#66B032\")"
        };
}
  options.cmd = options.command;
  spread.commandManager().execute(options);
}
});

Using Designer

SpreadJS Designer component also provides the ability to define columns by selecting the Enable Define Column checkbox from the TABLE SHEET DESIGN > Options group.


Enable Define Column checkbox


You can also configure the remote APIs to communicate with remote data tables in the SpreadJS Designer component from the DATA > DataSource > Table section, if the remote data table supports.


ts-definecolumn-remoteConfiguration