how to populate complex object and create nested columns in spreadsheet react

Question

I hope this message finds you well.I'm writing to seek guidance regarding the utilization of the Syncfusion Spreadsheet component to manage complex structured data, including nested columns. Additionally, I need assistance with integrating CRUD (Create, Read, Update, Delete) API operations in this context.While exploring the capabilities of the Spreadsheet component, I encountered a scenario where I'm required to render a complex object structure with nested columns. Alongside this, I'm aiming to implement CRUD operations to interact with the backend API seamlessly.The data structure I'm working with involves nested arrays and objects, and I'm seeking guidance on effectively rendering this structure within the Spreadsheet. Moreover, I'll need to ensure that nested columns accurately represent the relationships between various objects and arrays.To illustrate, here's a simplified example of the data structure I intend to populate:REACT-JAVASCRIPT  const data = {        data: [        {            "initialBid": [            {                "_id": "6526c39936905db1150a6751",                "amount": 12000,                "biddersId": "651168a65dbaaacc054f017d",                "divisionCategoryId": "651df47301eb1793ac7db572",                "projectId": "651df38401eb1793ac7db567",                "createdAt": "2023-10-11T15:47:37.455Z",                "updatedAt": "2023-10-11T15:47:37.455Z",                "__v": 0            },            {                "_id": "653a0802315d5d58cde59f88",                "amount": 500,                "biddersId": "651167bd5dbaaacc054f0171",                "divisionCategoryId": "651df47301eb1793ac7db572",                "projectId": "651df38401eb1793ac7db567",                "createdAt": "2023-10-26T06:32:34.058Z",                "updatedAt": "2023-10-26T06:32:34.058Z",                "__v": 0            }            ],            "DivisionCategoryId": "651df47301eb1793ac7db572",            "DivisionCategoryName": "division category testing 1",            "BreakDownItems": [            {                "BreakDownItem": {                "_id": "651df57501eb1793ac7db576",                "divisionCategoryId": "651df47301eb1793ac7db572",                "projectId": "651df38401eb1793ac7db567",                "BreakdownItemName": "item name test 1",                "BreakdownItemQuantity": 12,                "BreakdownItemPerUnit": 23,                "BreakdownItemArea": "Qw",                "createdAt": "2023-10-04T23:29:57.718Z",                "updatedAt": "2023-10-04T23:29:57.718Z",                "__v": 0                },                "IndividualBid": [                {                    "_id": "6526c47f36905db1150a6763",                    "amount": 120000,                    "biddersId": "651168a65dbaaacc054f017d",                    "breakDownItemId": "651df57501eb1793ac7db576",                    "createdAt": "2023-10-11T15:51:27.534Z",                    "updatedAt": "2023-10-11T15:51:27.534Z",                    "__v": 0                },                {                    "_id": "6526c55f360df49d124757cc",                    "amount": 160000,                    "biddersId": "651167bd5dbaaacc054f0171",                    "breakDownItemId": "651df57501eb1793ac7db576",                    "createdAt": "2023-10-11T15:55:11.990Z",                    "updatedAt": "2023-10-11T15:55:11.990Z",                    "__v": 0                }                ]            },            {                "BreakDownItem": {                "_id": "651df57501eb1793ac7db576",                "divisionCategoryId": "651df47301eb1793ac7db572",                "projectId": "651df38401eb1793ac7db567",                "BreakdownItemName": "item name test 1",                "BreakdownItemQuantity": 12,                "BreakdownItemPerUnit": 23,                "BreakdownItemArea": "Qw",                "createdAt": "2023-10-04T23:29:57.718Z",                "updatedAt": "2023-10-04T23:29:57.718Z",                "__v": 0                },                "IndividualBid": [                {                    "_id": "6526c47f36905db1150a6763",                    "amount": 120000,                    "biddersId": "651168a65dbaaacc054f017d",                    "breakDownItemId": "651df57501eb1793ac7db576",                    "createdAt": "2023-10-11T15:51:27.534Z",                    "updatedAt": "2023-10-11T15:51:27.534Z",                    "__v": 0                },                {                    "_id": "6526c55f360df49d124757cc",                    "amount": 160000,                    "biddersId": "651167bd5dbaaacc054f0171",                    "breakDownItemId": "651df57501eb1793ac7db576",                    "createdAt": "2023-10-11T15:55:11.990Z",                    "updatedAt": "2023-10-11T15:55:11.990Z",                    "__v": 0                }                ]            }            ],            "Bidders": [            {                "_id": "651167bd5dbaaacc054f0171",                "phoneNumber": "03182834216",                "companyName": "JD Electrics Ltd.",                "contact": "jdelectrics@gmail.com",                "createdAt": "2023-09-25T10:58:05.648Z",                "updatedAt": "2023-09-25T10:58:05.648Z",                "__v": 0,                "divisionCategoryId": "651df47301eb1793ac7db572",                "projectId": "651df38401eb1793ac7db567"            }            ]        }        ]    }Furthermore, the image I'm attaching exemplifies the desired outcome, particularly emphasizing the nested column structure within the Spreadsheet component. I am hoping to replicate this layout and behavior programmatically while maintaining the connections between nested columns and data elements.Additionally, I'd greatly appreciate any insights, examples, or best practices that showcase how to seamlessly perform CRUD operations within this structured environment, allowing smooth interactions with the backend API.Attached is the image depicting the desired nested column structure within the Spreadsheet component, which I aim to replicate programmatically.Looking forward to your response and insights.bidder_page.png22 KB

Sangeetha Priya Murugan · Answer

Hi Asad,Regarding update data source:Currently, spreadsheet does not support with updating the
complex structure of data (nested column and array of nested column data) into
it. You can be able to update the data to the spreadsheet as key-value object
pair.However, we have prepared a sample with reference to the
screenshot attached. Attached the sample below for your reference.Stackblitz Sample: https://stackblitz.com/edit/react-nlo53q-dpoczz?file=index.js,data.js
The below actions have been performed on the sample
attached.
 The data source that has been provided at your end is
     of array of collections. Therefore, we have modified it accordingly to the
     spreadsheet and loaded it from cell A8. Attached screen shot for
     reference.

Using setBorder method, we have applied the
     border for the cells with reference to the screenshot attached.
 The data has been updated to the spreadsheet via three
     methods. 
              -- Using cell data
     binding.
              -- Using updateCell
     method.
              -- Using updateRange
     method.
Using cell data binding:We have
updated the data directly to the <cell-directive> property along with the
index position to update data.Using updateCell method: To update individual cell value you can use the updateCell
method. The data updated via updateCell method are attached below. Using updateRange method: To update a
data to a range of address you can use the updateRange method. Initially we
have created a collection of data to be updated.Using updateRange method updated those data to the sheet as
expected. Attached screenshot for reference.>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> Regarding CRUD operations:We have checked your requirement
and the dataSourceChanged event handler will be triggered when editing, inserting, and deleting a row is made on the data source
range. It is triggered with parameter named action which indicates edit, add, and delete for the respective action. By
using this event you can do your customization in your end.Stackblitz Sample: https://stackblitz.com/edit/react-cukmit-8plgsj?file=index.js
API Reference: https://helpej2.syncfusion.com/react/documentation/api/spreadsheet/#datasourcechanged
Regarding storing the data:We suspect that you need to update the data to the database
and to retrieve it again to load into the spreadsheet. This can be done on the
below possible ways.
 You can save the data as JSON to the database.
     This will maintain every detail of the data including styles,
     formula, etc...  To save the data as JSON you can use the saveAsJson
     method and to load the saved JSON you can use the openFromJson
     method. Attached knowledge base link for reference.
https://support.syncfusion.com/kb/article/10357/how-to-save-and-retrieve-the-javascript-spreadsheet-data-as-json-in-database
Attached
API links for reference.https://ej2.syncfusion.com/angular/documentation/api/spreadsheet/#saveasjson

https://ej2.syncfusion.com/angular/documentation/api/spreadsheet/#openfromjson
 You can also save the data as byte array on the data
     base and can be able to retrieve it. To
     save the spreadsheet data as a byte array to server, you need to send the
     file name, JSON data, content type, version type from client to server. To
     load the byte array in spreadsheet, you need to send file name from client
     to server.
https://support.syncfusion.com/kb/article/10471/how-to-save-and-retrieve-javascript-spreadsheet-data-as-a-byte-array-in-the-database

You can use the cellSave event to save the data to the
     SQL table. As the cellSave event gets triggered every time when a cell is
     being saved the modified data can be fetched by getData method. The
     fetched data can be stored into a SQL table as key value object pair.
https://support.syncfusion.com/kb/article/10925/how-to-load-and-save-a-sql-table-in-javascript-spreadsheet-using-cellsave-event
Note: You can also use file import and export support available in
spreadsheet. Instead, performing the customizations you can simply import the
excel file into the spreadsheet with the customizations made and can save those
data to the database. https://ej2.syncfusion.com/react/documentation/spreadsheet/open-save
Attached links for reference.UG Documentationhttps://ej2.syncfusion.com/react/documentation/spreadsheet/getting-started
https://ej2.syncfusion.com/react/documentation/spreadsheet/data-binding
https://ej2.syncfusion.com/react/documentation/spreadsheet/editing
API Referencehttps://helpej2.syncfusion.com/react/documentation/api/spreadsheet/
https://helpej2.syncfusion.com/react/documentation/api/spreadsheet/#updatecellhttps://helpej2.syncfusion.com/react/documentation/api/spreadsheet/#updaterange
https://helpej2.syncfusion.com/react/documentation/api/spreadsheet/#setborderhttps://helpej2.syncfusion.com/react/documentation/api/spreadsheet/#merge
Get back to us if you need any other further clarifications
on this.