Import Grid into Spreadsheet

[Nick Fox]

Hi everyone,

In my project, I have a function that retrieves rows from the database and so I have a JSON array, containing the rows, as response. For my purpose, I cannot use the dataSource object for injecting those data into the Spreadsheet, but I had to pass it to a Grid and then to the Spreadsheet.

So, what I'm asking to you is:

• Is there a way for take the data inside the Grid and put them into the Spreadsheet? 
• If yes, is it scalable also if I have thousand of thosand rows?

The case is the following: call the function, retrieve the rows, populate the grid, move the rows from the grid inside the spreadsheet.

Thank you!

Best regards,
Nick

[Babu Periyasamy]

Hi Nick Fox,

Currently we are validating your requirement based on your shared details and we will update you with further details as soon as possible.

[Jayachandran Jayasankar]

Hi Nick Fox,

We have validated your query based on the details you provided, and we understand that you need to dynamically transfer a JSON array from a Grid component to the Spreadsheet component. You can achieve this requirement using dataSource property present on both Grid and Spreadsheet component. We have shared the details on below.

Retrieve Data from Grid:

Once you have populated the Grid with your JSON data, you can obtain the grid data as an array of objects directly using dataSouce (gridInstance.dataSource) property. This will return the dataset as a JSON array, which can then be loaded into the Spreadsheet.

Transfer Data to Spreadsheet:

You can transfer the data to the Spreadsheet By updating the data source retrieved from the Grid in the range model of the desired sheet This RangeModel in the Spreadsheet allows you to specify settings for the range, including the data source, starting cell, and showFieldAsHeader properties for displaying data in the Spreadsheet. Finally, you should call dataBind and refresh methods to ensuring the changes made to the spreadsheet are properly applied and reflected.

Please find the code to get data from Grid and assign to the range property of the Spreadsheet component:

    const gridData = this.grid.dataSource;

    const activeSheet: SheetModel = this.spreadsheetObj.getActiveSheet();

    const ranges: RangeModel[] = activeSheet.ranges;

    if (ranges.length) {

      // Update the existing range with grid data

      Object.assign(ranges[0], { dataSource: gridData, startCell: 'A1', showFieldAsHeader: true });

    } else {

      // Add a new range if no range exists

      ranges.push({ dataSource: gridData, startCell: 'A1',showFieldAsHeader: true });

    }

    this.spreadsheetObj.dataBind();

    this.spreadsheetObj.refresh();

Sample Link: https://stackblitz.com/edit/angular-fguut9-88zzn7?file=src%2Fapp.component.ts

In this sample, grid data will move to the spreadsheet component by clicking on the Transfer Data button.

Please find the documentation related to different kind of data binding, open and save data.

Data binding in Angular Spreadsheet component | Syncfusion

Open save in Angular Spreadsheet component | Syncfusion

Please feel free to reach out if you have further questions or need additional clarification.

Best regards,

Jayachandran

[Nick Fox]

Hi Jayachandran,

it works fine and it's so good for my purpose! Thank you for the help!

Btw, I have another question. Is it possible to apply also a cell style on the cells written in the Spreadsheet, after the binding? 

For example, I push the dataSource from the grid to the spreadsheet, and I want that the new populated cells should be readOnly, autoFit in width and with a specific style. Is it possible, always in a scalable way?

Thank you again,

Best regards,
Nick

[Babu Periyasamy]

Hi Nick Fox,

We have validated your queries regarding autofitting columns, making cells readonly, and applying cell styles in the Spreadsheet. Below are the details for each functionality:

Autofitting the columns:

To autofit columns programmatically in the Spreadsheet, you can utilize the `autofit` method. This method adjusts the width of the columns or height of the rows based on the content within the specified range.

Make the cells readonly:

To make specific cells readonly in the Spreadsheet, you can use the `setRangeReadOnly` public method, which has been available since version 26.1.35. This method allows you to make a range of cells, an entire row, or a column readonly without protecting the entire spreadsheet. For further details, please refer the below documentation:

https://ej2.syncfusion.com/angular/documentation/spreadsheet/protect-sheet#make-cells-read-only-without-protecting-worksheet

Apply styles to the cells:

To apply styles to a range of cells programmatically in the Spreadsheet, you can use the `cellFormat` method. This allows you to apply various styles, such as background color, font color, and font family, etc., to specific ranges. For additional information, please refer the below documentation:

https://ej2.syncfusion.com/angular/documentation/spreadsheet/formatting#text-and-cell-formatting

Also, we have modified the previously shared sample by applying the autofit, readonly and cell styles while populating the data into the Spreadsheet. For your convenience, we have shared the code snippet and modified sample for your reference,

Code snippet:

//Call the setTimeout to make sure that the UI has been refreshed.

    setTimeout(() => {

      let usedRange: UsedRangeModel = this.spreadsheetObj.getActiveSheet().usedRange;

      let endColumnHeader: string = getColumnHeaderText(usedRange.colIndex + 1);

      let rangeAddress: string = getRangeAddress([0, 0, usedRange.rowIndex, usedRange.colIndex]);

      //Apply the cell styles for the range of cells.

      this.spreadsheetObj.cellFormat({ fontWeight: 'bold', textAlign: 'center', verticalAlign: 'middle' }, `A1:${endColumnHeader}1`);

      //Apply the autoFit based on the used column.

      this.spreadsheetObj.autoFit(`A:${endColumnHeader}`);

      //Make the cells readOnly based on the usedRange.

      this.spreadsheetObj.setRangeReadOnly(true, rangeAddress, 0);

})

Modified sample: https://stackblitz.com/edit/angular-fguut9-rsr4gw?file=src%2Fapp.component.ts

Kindly check the above details and get back to us for further clarifications.

[Nick Fox]

Hi  Babu Periyasamy,

Thank you so much for your help! It was very helpful for me!

Is there a way for providing an ordering functionality on the columns in the spreadsheet then? 

So, I'm importing the Grid's dataSource inside the Spreadsheet using your code, but I would like to add the possibility to order the columns inside the spreadsheet. How can I do that?

Thank you,

Nick. 

[Deira Raj Rajkumar]

Hi Nick Fox,

We have validated your query regarding ordering the columns inside the spreadsheet.

To order the column in the spreadsheet, you can use the `fieldsOrder` property of the Range. This property allows you to specify the order in which columns should be displayed. 

For additional information please refer the below documentation:

https://ej2.syncfusion.com/angular/documentation/api/spreadsheet/range/

Also, we have modified the previously shared sample by applying the fieldsOrder property while populating the data into the Spreadsheet. For your convenience, we have shared the code snippet and modified sample for your reference,

ranges.push({

        dataSource: gridData,

        startCell: 'A1',

        showFieldAsHeader: true,

        //Add the order in which you want the column to display the data

        fieldsOrder : ["OrderID", "CustomerID", "Freight", "ShippedDate", "ShipCountry"]

      });

Modified sample: StackBlitz Sample Link

Kindly check the above details and get back to us for further clarifications.

[Nick Fox]

Hi Deira Raj Rajkumar,

I'm sorry, I misspoke, and it’s probably my fault that you didn’t fully understand the requirements!

Let me try to rephrase my issue: after importing the grid into the spreadsheet and applying the readOnly setting to the range containing the imported grid data, I would like it to be possible to sort the rows (after the import) by applying sorting on the columns.

This means that, assuming my grid has the following columns: ["OrderID", "CustomerID", "Freight", "ShippedDate", "ShipCountry"], I want to be able to apply, for example, ascending/descending sorting on the OrderID column in the same way it is done in Excel. I have my data table, and if I right-click, I should be able to sort my table according to the selected sorting option.

How can I achieve a similar result?

Sorry again and thank you for the answer.

Best regards,
Nick

[Deira Raj Rajkumar]

Hi Nick Fox,

We have validated your request regarding sorting operations on imported columns when using the `readOnly` setting for the range containing the imported grid data.

Regarding readOnly in spreadsheet:

We would like to let you know that the setRangeReadOnly method(used here for making the particular range readOnly) is used to control editing permissions for specific cells in the spreadsheet. When the readOnly setting is enabled (set to true), editing, cell formatting(like changing the font family, font color, background color, etc) cannot be done to the data in that range. Unfortunately, this also disables functionalities like sorting, as sorting modifies the arrangement of the data, which is restricted by the read-only setting.

Making readOnly in Excel:

You have mentioned that you have achieved this in the Excel. We suspect that you are using protect sheet option with lock cell disabled. We have attached below a sample video on how we suspect you have achieved this in Excel.

Video demonstration:Please refer the attachment.

Currently, we don’t have option for allowing sorting while protecting the sheet in our Spreadsheet. However, we have already confirmed this as an improvement and logged it as a feature. It will be available in any of our upcoming releases. You can communicate and track the status of the feature using the below link from our feedback portal.

Feedback link for tracking purposes: https://www.syncfusion.com/feedback/40357/provide-support-for-protect-sheet-limitation-settings-as-like-excel

Kindly review the above details and please provide the following information based on that we will check the feasibility and provide a sample level work around for your exact requirement.

Please clarify the purpose of using the readOnly option.

• Is to prevent cell editing only?
• Or do you want to completely make it readOnly like preventing editing and cell formatting option?
• Or are you aiming to achieve a specific any use case with this setting? If so, please share us the use case.
• Could you please share us the steps you used to perform the sorting operation in Excel?
• Could you please share the details about “what are actions should be allowed & what are actions should be restricted” when set readonly to a cell?

Please provide the above information, so that we can review it and offer the exact solutions.

Attachment: SortingInExcel_3ec9d566.zip