CRUD Operations

GanttSheet allows users to interact with data through row operations, including Create, Read, Update, and Delete (CRUD). These operations enable convenient synchronization of edited row data with your database.

The following GanttSheet class methods are available as row operations:

Method	Description
addRow	Adds a new row to the table.
removeRow	Removes the specified row from the table.
saveRow	Saves the changes of the specified row of the table to the data manager, including updated and inserted rows.
resetRow	Resets the changes of the specified row of the table.

GanttSheet Methods

Let's understand the CRUD operations in GanttSheet with sample codes.

Create (Insert Task)

Adds new tasks to the GanttSheet, including hierarchical relationships (e.g., parent-child associations). Tasks are queued in batch mode.

if (item.type == "insert") {
    item.dataItem.id = (item.sourceIndex + 1).toString();
    if (item.sourceParentIndex) {
        item.dataItem.parentId = item.sourceParentIndex + 1;
    }
    const params = { method: "POST", body: JSON.stringify(item.dataItem) };
    params.headers = { 'Content-Type': 'application/json; charset=utf-8' };
    results.push(fetch(url, params).then(resp => {
        console.log(resp)
        if (resp.ok) {
            return { succeed: true, data: resp.json() };
        } else {
            return { succeed: false, reason: resp.statusText };
        }
    }));
}

Read (Fetch Tasks)

Retrieves data from the server to populate the GanttSheet.

remote: {
    read: {
        url: apiUrl,
    },
}

Update (Modify Task)

Modifies task properties such as duration, cost, or dependencies. Updates are queued for batch processing.

if (item.type == "update") {
    const params = { method: "PUT", body: JSON.stringify(item.dataItem) };
    params.headers = { 'Content-Type': 'application/json; charset=utf-8' };
    results.push(fetch(url + item.dataItem.id, params).then(resp => {
        if (resp.ok) {
            return { succeed: true, data: resp.json() };
        } else {
            return { succeed: false, reason: resp.statusText };
        }
    }));
}

Delete (Remove Task)

Removes a specified task from the GanttSheet. Deletions are queued and processed in batch mode.

if (item.type == "delete") {
    const params = { method: "DELETE" };
    params.headers = { 'Content-Type': 'application/json; charset=utf-8' };
    results.push(fetch(url + item.dataItem.id, params).then(resp => {
        if (resp.ok) {
            return { succeed: true, data: resp.json() };
        } else {
            return { succeed: false, reason: resp.statusText };
        }
    }));
}

Batch Mode Synchronization

GanttSheet only supports batch mode for these operations.

To use GanttSheet, users must have a server that supports batch mode or implement server-side logic to handle batch updates. If server-side support is not available, a pseudo batch mode should be implemented on the client side.

// Submitting changes
ganttSheet.submitChanges();

Schema Configuration

The schema defines the structure of tasks in the GanttSheet, including hierarchy and column properties.

var myTable1 = dataManager.addTable("myTable1", {
    batch: true,
    remote: {
        read: {
            url: apiUrl,
        },
        batch: function (changes) {
            return sendRequest(apiUrl, { body: changes });
        }
    },
    schema: {
        hierarchy: {
            type: "Parent",
            column: "parentId",
        },
        columns: {
            id: { isPrimaryKey: true },
            taskNumber: { dataType: "rowOrder" },
        }
    }
});

Bind GanttSheet to View

Binds a task view to the GanttSheet for visualization and interaction.

var ganttSheet = spread.addSheetTab(0, "GanttSheet1", GC.Spread.Sheets.SheetType.ganttSheet);
var view = myTable1.addView("myView1", [
    { value: "taskNumber", caption: "NO", width: 60 },
    { value: '=CONCAT("(L",LEVEL(),"-",LEVELROWNUMBER(),")")', caption: "L" },
    { value: "name", caption: "Task Name", width: 200 },
    { value: "duration", caption: "Duration", width: 90 },
    { value: "predecessors", caption: "Predecessors", width: 60 },
    { value: "cost", caption: "Cost", style: { formatter: "$0" } }
]);
view.fetch().then(function () {
    ganttSheet.bindGanttView(view);
});

Customize Requests

The sendRequest function handles server requests for batch operations. This is essential for implementing pseudo-batch mode on the client side when server-side batch support is unavailable.

function sendRequest(url, options) {
    const results = [];
    for (const item of options.body) {
        // Insert, update, or delete logic
    }
    return Promise.all(results);
}

Example

To provide a clear understanding of how CRUD operations work, here is a complete example that combines individual code snippets into an organized implementation.

 <script>
     function getBaseApiUrl() {
         return 'https://developer.mescius.com/spreadjs/demos/features/ganttsheet/overview/purejs'.match(/http.+spreadjs\/demos\//)[0] + 'server/api';
     }

     let spread = new GC.Spread.Sheets.Workbook("spreadjs-host");
     spread.options.tabStripRatio = 0.8;
     spread.setSheetCount(0);

     function sendRequest(url, options) {
         const results = [];

         for (const item of options.body) {
             if (item.type == "insert") {
                 item.dataItem.id = (item.sourceIndex + 1).toString();
                 if (item.sourceParentIndex) {
                     item.dataItem.parentId = item.sourceParentIndex + 1;
                 }
                 const params = { method: "POST", body: JSON.stringify(item.dataItem) };
                 params.headers = { 'Content-Type': 'application/json; charset=utf-8' };
                 results.push(fetch(url, params).then(resp => {
                     console.log(resp)
                     if (resp.ok) {
                         return { succeed: true, data: resp.json() };
                     } else {
                         return { succeed: false, reason: resp.statusText };
                     }
                 }));
             }
             else if (item.type == "update") {
                 const params = { method: "PUT", body: JSON.stringify(item.dataItem) };
                 params.headers = { 'Content-Type': 'application/json; charset=utf-8' };

                 results.push(fetch(url + item.dataItem.id, params).then(resp => {
                     if (resp.ok) {
                         return { succeed: true, data: resp.json() };
                     } else {
                         return { succeed: false, reason: resp.statusText };
                     }
                 }));
             }
             else if (item.type == "delete") {
                 const params = { method: "DELETE" };
                 params.headers = { 'Content-Type': 'application/json; charset=utf-8' };

                 results.push(fetch(url + item.dataItem.id, params).then(resp => {
                     if (resp.ok) {
                         return { succeed: true, data: resp.json() };
                     } else {
                         return { succeed: false, reason: resp.statusText };
                     }
                 }));
             }
         }
         return Promise.all(results);
     }

     var tableName = "Gantt_Id";
     var baseApiUrl = getBaseApiUrl();
     var apiUrl = baseApiUrl + "/" + tableName;
     var dataManager = spread.dataManager();

     var myTable1 = dataManager.addTable("myTable1", {
         batch: true,
         remote: {
             read: {
                 url: apiUrl,
             },

             batch: function (changes) {
                 return sendRequest(apiUrl, { body: changes });
             }
         },
         schema: {
             hierarchy: {
                 type: "Parent",
                 column: "parentId"
             },
             columns: {
                 id: { isPrimaryKey: true },
                 taskNumber: { dataType: "rowOrder" }
             }
         }
     });

     var ganttSheet = spread.addSheetTab(0, "GanttSheet1", GC.Spread.Sheets.SheetType.ganttSheet);
     var view = myTable1.addView("myView1", [
         { value: "taskNumber", caption: "NO", width: 60 },
         { value: '=CONCAT("(L",LEVEL(),"-",LEVELROWNUMBER(),")")', caption: "L" },
         { value: "name", caption: "Task Name", width: 200 },
         { value: "duration", caption: "Duration", width: 90 },
         { value: "predecessors", caption: "Predecessors", width: 60 },
         { value: "cost", caption: "Cost", style: { formatter: "$0" } }
     ]);
     view.fetch().then(function () {
         ganttSheet.bindGanttView(view);
     });

     document.getElementById("btn").onclick = () => {
         ganttSheet.submitChanges();
     }
 </script>