Datasource management within Spreadsheet

[Nick Fox]

Hello everyone,

I have been exploring the possibility of organizing a spreadsheet to manage some data. Specifically, I import an array of data into the spreadsheet as a datasource and feed it into the spreadsheet so that it can invoke the dataSourceChanged event.

Now, what I would like to achieve is the following:

1. When I modify a piece of data in the DataSource, the event triggered by dataSourceChanged only returns the edited row, but I can't find a way to get the updated DataSource. How can I achieve this?
2. When I manually move the DataSource within the spreadsheet, for example, from position A2 to position G5, is there a way to retain the reference to the DataSource? In other words, can I ensure that, even after moving the table, I can still trigger the dataSourceChanged event?
3. Assuming I have a [+] button in a cell at the end of the datasource, how can I add a row at the end of the datasource (append it), making sure it is linked to the datasource and possibly keeping the same cell styles as those in the datasource?
4. I also wanted to take the base64 of the fileContent about this spreadsheet. How can i do this?

Thank you very much for your help.

Best regards,

Nick.

[Babu Periyasamy]

Hi Nick Fox,

Query 1: Get the updated Datasource.

We have validated your reported query of getting updated dataSource in dataSourceChanged event. And we would like to let you know that, in the dataSourceChanged event of Spreadsheet triggers when editing or deleting values within the dataSource along with the edited row data only in the arguments received and it will not be updated to the dataSource property.

And to get the updated dataSource, we need to construct the dataSource using the getRowData method based on the used range of the sheet. For your convenience, we have prepared the sample in which we have constructed the updated dataSource using the getRowData method on a button click and attached below along with the code snippet for your reference,

Code snippet:

<button (click)="getData()">Get Data</button>

getData() {

    // Get the active sheet.

    let sheet: SheetModel = this.spreadsheetObj.getActiveSheet();

    // Get the used range indexes.

    let usedRange: UsedRangeModel = sheet.usedRange;

    let datas: Object[] = [];

    //Set the i is equal to 1 to skip the header values.

    for (let i: number = 1; i < usedRange.rowIndex; i++) {

      // To Get the data using get Row Data method.

      datas.push(this.spreadsheetObj.getRowData(i as number, sheet.index)[0]);

    }

    //Get the updated dataSource.

    console.log(datas);

  }

Sample link: https://stackblitz.com/edit/angular-i5uffe-sxbifq?file=src%2Fapp.component.ts

For more information, please refer the below mentioned KB,

https://support.syncfusion.com/kb/article/14322/how-to-get-the-modified-data-as-a-key-value-pair-in-a-javascript-spreadsheet

Query 2: Manually move the DataSource within the spreadsheet.

We have validated your reported query based on your shared details and still we need some more details to validate in our end, please share the below information,

1. As you have mentioned, “manually move the DataSource within the spreadsheet” whether you are trying to trigger dataSourceChanged event after copying the value from dataSource range and pasting the copied value outside the dataSource range.
2. And also, please share the details of whether you want to modify the whole dataSource range or to update another dataSource with the new range within the range.
3. Please share the detailed description of your requirement in the step-by-step process along with the video demonstration which will be more helpful for us to validate in our end.

And also, we would like to let you know that in our Spreadsheet component, we will trigger the `actionBeigin` and `actionComplete` events which will gets triggered when the Spreadsheet UI actions starts and gets triggered when the Spreadsheet UI actions gets completed respectively. And these events get triggered for the copy/cut/paste actions also in Spreadsheet.

Query 3: Add a row at the end of the datasource.

We have validated your shared query and before we proceed further, we need some additional information from your end for further validation regarding your query,

1. As you have mentioned, “add a row at the end of the datasource (append it)” whether you need to insert a new row into the Spreadsheet or you need to update another set of dataSource next to the previous dataSource.
2. Also, you have mentioned that “keeping the same cell styles as those in the datasource”, please share the details of the cell styles that you have maintained in the previous dataSource.
3. And also, share the details of whether you want to add the newly added row to the existing dataSource or update as the new dataSource.
4. If possible, please share the detailed description of your requirement along with the video demonstration which will be more helpful for us to validate in our end.

Please share the above requested details from your end. Based on that, we will check and provide you the better solution quickly.

Query 4:  Wanted to take the base64 of the fileContent about this spreadsheet.

We have validated your reported query of getting the base64 content of the Spreadsheet and we would like to let you know that in the Spreadsheet component, there is currently no direct option to save data as a Base64 string. You can achieve this by saving the Spreadsheet data as blob data and then converting that saved blob data to a Base64 string using `FileReader`.

And to get the blob data of Spreadsheet, you need to set the `needBlobData` as true and `isFullPost` as false in the beforeSave event and it will trigger the saveComplete event. And in the saveComplete event you will get the `blobData` of Spreadsheet in the args.

For your convenience, we have prepared the sample and attached below along with the code snippet for your reference,

Code snippet:

beforeSave(args: BeforeSaveEventArgs): void {

        args.needBlobData = true; // To trigger the saveComplete event.

        args.isFullPost = false; // Get the spreadsheet data as blob data in the saveComplete event.

    };

    saveComplete(args: SaveCompleteEventArgs): void {

        // Convert blob data to base64 string.

        let reader: FileReader = new FileReader();

        reader.readAsDataURL(args.blobData);

 

        reader.onloadend = () => {

            this.base64String = reader.result ? reader.result : '';

            console.log("Base64 string: ", this.base64String);

        };

    };

Sample link: https://stackblitz.com/edit/angular-qjsqec-fudlv8?file=src%2Fapp.component.ts

For more information, please refer the below documentation,

https://ej2.syncfusion.com/angular/documentation/spreadsheet/open-save#save-data-as-a-base64-string

Kindly, check the above details and get back to us if you need further clarifications.