Is it possible to prevent multiple datasource ranges from being created when call updateRange() method?

[cy]

Hi.

I am working with large datasets in spreadsheets. In special cases, I need to update the data source of a specific range. After using updateRange to change the data source, I noticed that when I do something the updated range cells, the datasourceChange event is triggered multiple times. Upon examining the parameters of the datasourceChange event handler, I noticed that the rangeIndex is different each time. It seems that a new data source range is being added each time I execute updateRange. However, what I want is not to add a new data source range but to update the original data source, maintaining a single range.

Due to the large volume of data, I have chosen to change only the specific data that needs to be updated.

Is there a way to change the values of the cells displayed in the spreadsheet and the data source together while keeping only one data source range?

Here is my code and I attached the browser console log of args from the datasourceChange event handler.

let newRange = {

dataSource: dataSource, // specific datasource that need to be changed

showFieldAsHeader: false,

startCell: 'A2'

}

spreadsheet.value.updateRange(newRange)

[Babu Periyasamy]

Hi cy,

We have checked your reported query based on your provided details and we suspect that you want to maintain the single ranges property event after updating the dataSource. And it can be achieved by updating the dataSource in the ranges property on sheet instead of updating the range all the time.

For your convenience, we have prepared the sample in which we have updated the range using the updateRange method when the ranges property is empty and if we there is a range available already then, we will update only the dataSource of the ranges property on a button click.

Below attached the created sample along with the code snippet and video demonstration,

Code snippet:

updateRange: function() {

      var spreadsheet = this.$refs.spreadsheet;

      var activeSheet = spreadsheet.ej2Instances.getActiveSheet();

      //Check whether the ranges is already there or empty.

     if (activeSheet.ranges.length === 0) {

        // If there is no range already, we can update the range using updateRange method. 

        spreadsheet.updateRange({dataSource: this.dataSource, showFieldAsHeader: false, startCell: 'A2'}, spreadsheet.activeSheetIndex);

      } else {

        // If there is already ranges available, then we can update the new dataSource in the available ranges itself.

        activeSheet.ranges[0].dataSource = this.newData;

        // Call the dataBind method as we are changing the ranges property.

        spreadsheet.dataBind();

      }

    }

Sample link: https://stackblitz.com/edit/r2zc9c-whgeun?file=src%2FApp.vue

Video link: https://www.syncfusion.com/downloads/support/directtrac/general/ze/Update_range_in_Spreadsheet1865952883

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

[cy]

Hello. I have applied your suggestion. Your example works perfectly. 

However, I encountered several issues when applying it to my project.

When I call created event handler in spreadsheet , I apply various cell settings.(validation, merge, cell styling, autoFit, ... etc) 

If something is set during the create event, it seems that the datasource binding is not applied to the screen. 
(But when I check the datasource, datasource is changed.)

Additionally, my data includes newline characters (''), and in such cases, the changed datasource is not applied to the screen.

I'm not sure if it is related to the create event. But I cannot find another reason.

Here are the test cases I have tried:

1. Data source without newline characters, not using the created event handler. -> works dataBind()
2. Data source without newline characters, with cell settings in the created event. -> not works dataBind()
3. Data source with newline characters. -> not works dataBind()

Here are created event handler example and sheet screenshot.

const created = () => {

const spreadsheetRef = spreadsheet.value

const activeSheet = spreadsheet.value.ej2Instances.getActiveSheet()

const rowDataIndex = activeSheet.rowCount - 1

const colDataIndex = activeSheet.colCount - 1

const lastCell = getCellAddress(rowDataIndex, colDataIndex)

spreadsheetRef.cellFormat({

border: '1px solid #333'

}, `A1:${lastCell}`)

spreadsheetRef.cellFormat({

backgroundColor: '#f2f2f2',

fontSize: '11pt'

}, `A1:${getCellAddress(0, colDataIndex)}`)

spreadsheetRef.autoFit('A:E')

spreadsheetRef.wrap(`A1:${lastCell}`, true)

for (let i = 0; i < groupCount; i++) {

spreadsheetRef.merge(`${groupStartCell}:${groupEndCell}`)

spreadsheetRef.addDataValidation({ type: 'List', inCellDropDown: true, value1: '10000,20000,30000' }, `${validationRange}`)

}

spreadsheetRef.cellFormat({ textAlign: 'right' }, formatRange)

spreadsheetRef.numberFormat('#,##0', formatRange)

spreadsheetRef.lockCells(lockRange, false)

spreadsheetRef.ej2Instances.freezePanes(1, 0)

}

[Janakiraman Sakthivel]

Hi cy,

Currently, we are validating your reported query at our end and will update you with further details soon. We appreciate your patience until then.

[Janakiraman Sakthivel]

Hi cy,

We have validated your reported problem and were able to replicate it using the code you shared within the created event. When a range is already available, directly updating the "dataSource" of the range property causes the issue, as mentioned in our previous update.

To resolve this reported problem, we suggest you update the ranges property of the sheet after updating the data source of the range property separately, as we highlighted in the code snippet below.

CODE SNIPPET:

updateRange: function() {

      var spreadsheet = this.$refs.spreadsheet;

      var activeSheet = spreadsheet.ej2Instances.getActiveSheet();

      //Check whether the ranges is already there or empty.

      if (activeSheet.ranges.length === 0) {

            // If there is no range already, we can update the range using updateRange method. 

            spreadsheet.updateRange({dataSource: this.newData}, spreadsheet.activeSheetIndex);

      } else {

            // If there is already ranges available, then we can update the new dataSource in the available ranges itself.

            var ranges = activeSheet.ranges;

            ranges[0].dataSource = this.newData;

            this.$refs.spreadsheet.ej2Instances.getActiveSheet().ranges = ranges;

            // Call the dataBind method as we are changing the ranges property.

            spreadsheet.dataBind();

      }

    }

For your convenience, we have shared a modified sample below.

Sample: 7aj1fs (forked) - StackBlitz

And we would like to inform you that the created event is only triggered when the component is created. Therefore, if you want to apply cell settings such as validation, merge, cell styling, and autoFit to the data source changed range, we suggest applying these settings within the dataSourceChanged event to achieve this.

Could you please assign the data source as suggested above and get back to us if you need any further assistance on this?

[cy]

Thank you for the sample!

It's working now.

Thank you.