[]
DEMOS DOC DESIGNER API
DOWNLOAD
(Showing Draft Content)

Convert from/to Data Table

SpreadJS enables users to seamlessly convert between Regular Tables and Data Tables, addressing scenarios requiring dynamic data integration or static data management. This capability is critical for applications needing structured data binding (e.g., real-time analytics, reporting) or snapshot preservation (e.g., data auditing, template reuse). The conversion process supports two implementation methods:

  1. ​​API-driven approach​​: Utilize dedicated methods like convertToDataTable and convertFromDataTable for programmatic control.

  2. ​​Designer UI operations​​: Perform conversions through SpreadJS Designer’s visual interface.

This guide details step-by-step procedures for both approaches, emphasizing practical use cases and technical precision.

Convert Table to Data Table

You could easily integrate existing table data into the Data Manager ecosystem. This enables you to leverage advanced features like direct data binding to controls, sophisticated filtering, sorting, grouping, and data source manipulation provided by the Data Manager, while still using the visual table representation on the sheet.

Important Notes and Policies

When converting a table to a Data Table, keep the following policies in mind:

  • Unbound Requirement: Only regular sheet tables that are not already bound to a data source can be converted using this feature.

  • Data and Formula Clearing: The data and formulas present within the data range of the original regular table will be cleared from the sheet cells after conversion. The data and column definitions are now managed by the Data Manager table.

  • Data Manager Table Creation: A new table is created in the spread.dataManager().tables collection.

  • Binding: The original sheet table is rebound to this newly created Data Manager table. The sheet table now serves as a view for the data managed by the Data Manager.

  • Naming: The new Data Manager table will attempt to use the same name as the original regular table. If a Data Manager table with that name already exists, the new Data Manager table's name will be automatically adjusted by appending a number (starting from 1 plus the maximum existing number with the same prefix) to ensure uniqueness.

Using API

Follow the provided code to call convertToDataTable to convert a regular table into a data table.

// add a regular table to sheet
var sheet = spread.getActiveSheet();
sheet.tables.add('table1', 0, 0, 4, 4, GC.Spread.Sheets.Tables.TableThemes.light1);
// set value to the table
sheet.setArray(0, 0, [
      ['Id', 'Name', 'Age', 'Grade'],
      [1000, 'Tom', 23, 98],
      [1001, 'Bob', 22, 80],
      [1002, 'Tony', 23, 99]
]);

let dmTable = spread.dataManager().tables['table1']; // the dmTable will undefined
// convert the regular table to the table creating and binding a data manager table
let table = sheet.tables.convertToDataTable('table1');
// there will be a data manager table created from the table1
let bindingTable = table.getBindingSource(); // the regular table is converted to data table
dmTable = spread.dataManager().tables['table1']; // the dmTable will created
// bindingTable === dmTable // Return true. the bindingTable from table is the dmTable from data manager

Using Designer

The Designer provides a user-friendly interface to perform the conversion.

  1. Select the Table: Click anywhere within the regular table you wish to convert. This will activate the Table Design tab in the ribbon. The bound source is empty in the Binding Source within the Tables Binding box of Table Design tab.


    5.2

  2. Access Convert Option:

    • Go to the Table Design tab in the ribbon. In the "Table Binding" section, click the "Convert to Data Table" button.

      5.3

    • Alternatively, right-click on the table. In the context menu that appears, hover over the "Table" sub-menu, and then select "Convert to Data Table".

      image

  3. Click OK in the dialog.


    image

  4. Conversion successful. You can see that the previously empty bound source now displays "Table2"—this indicates the successful transformation of the original regular table into a data table.


    5.6

    image

Convert Data Table to Table

SpreadJS provides a method to disconnect a sheet table from its underlying DataManager source. When you convert a data-bound table:

  • The data and any column formulas currently displayed in the table are copied and set directly into the cells of the worksheet range occupied by the table.

  • The table instance on the worksheet becomes a regular table, no longer bound to the DataManager.

  • The original data table in the DataManager itself is not removed or affected by this conversion process. It remains available in the DataManager if needed for other purposes.

This feature is useful when you need to capture a snapshot of the data from a data source and work with it statically within the worksheet, without the table automatically updating from the source or requiring the DataManager connection for that specific table instance.

Using API

Follow this code to call convertFromDataTable for converting a data-bound table back to a regular table.

var sheet = spread.getActiveSheet();

// add a data source
const productsTable = spread.dataManager().addTable("products", {
  remote: {
         read: {
         url: 'https://northwind.vercel.app/api/products'
        }
  }
});

productsTable.fetch().then(()=>{
// add a normal Table and bind a data manager table by addFromDataSource
sheet.tables.addFromDataSource("Table1", 0, 0, "products", GC.Spread.Sheets.Tables.TableThemes.medium7);

// convert the table binding a data manager table to a regular table
let table = sheet.tables.convertFromDataTable('Table1');
let bindingTable = table.getBindingSource(); // the bindingTable be null for the data table is converted back to the regular table
});

Using Designer

The Designer provides a user-friendly interface to perform the conversion.

  1. Select the Table: Click anywhere within the data table you wish to convert. This will activate the Table Design tab in the ribbon.


    4.1

  2. Access Convert Option:

    • Go to the Table Design tab in the ribbon. In the "Table Binding" section, click the "Convert from Data Table" button.

      4.2

    • Alternatively, right-click on the table. In the context menu that appears, hover over the "Table" sub-menu, and then select "Convert from Data Table".

      image

  3. Click OK in the dialog.


    image

  4. Conversion successful. You can see that the previously bound source is empty in the Binding Source within the Tables Binding box of Table Design tab.


    4.3