Best way to update a range of cell values in spreadsheet

Question

I looking for the best method to update a range of cell values in a spreadsheet. I have a spreadsheet "template" that I load from a json object ("jsonData") using:

spreadsheet.openFromJson({file: jsonData.jsonObject});

This spreadsheet contains formatting and formulas. What I would like to do is perform a "bulk update" to some of the cell values. Instead of using a lot of updateCell() calls, I was hoping that I could do something like:

spreadsheet.sheets[0].ranges[0] = {dataSource: newValues, showFieldAsHeader: false, startCell: 'A2'};

where newValues would be an array of new cell values. I want to preserve the existing formatting and formulas, but just change the values within a range and have everything recalculate. In this example, I want to change A2:C3 to [Green, 100, 200] which would overwrite [White,20,6].

I am not sure how to format the newValues array/obj for this to work correctly. Attached is an example. Click the Load From JSON button to load the template, then try to click the Bulk Update button to perform the update action (which doesn't work).

Can you recommend the best (fastest) approach to update a range of cells in a spreadsheet?

EJ2 - Javascript - 19.2.0.44

Attachment: testss_df3f0f00.zip

John · Answer

To provide a bit more context, my ultimate goal is to have a spreadsheet control that serves as a "detail grid" in my application. When the user selects a database record from a grid control (i.e. master) the spreadsheet control would update with data associated with that record. This detail spreadsheet will be rather complicated with formulas and some dynamic behavior, such as row/col hiding depending on the selected data. The detail spreadsheet will serve as data entry. I like the look and behavior of the spreadsheet control much better than having a nested grid or master/detail grids.

I hope this information doesn't detract from my original question, but I thought it may be helpful to see the path that I'm headed down.

Sangeetha Priya Murugan · Answer

Hi John, 
 
Thank you for contacting Syncfusion support. 
 
We have checked your reported requirement and we would like to let you know that you have loaded the JSON data using openFromJson method. But in the loaded JSON you haven’t set the ranges property, the data loaded in the rows property. So, we would suggest you to clear the rows property as like as below to update the values in the ranges property. 
 
Code Example: 
 




function pokeData() { 
 
    //var newValues = [{"Color": 'Green',"Bin 1": 100,"Bin 2": 200}]; 
    var newValues =[[{value : "Green"},{value:100},{value:200}]]; 
 
    spreadsheet.sheets[0].ranges[0] = { 
        dataSource: newValues, 
        showFieldAsHeader: false, 
        startCell: 'A2' 
    }; 
    spreadsheet.sheets[0].rows = []; // to clear the rows object 
    spreadsheet.refresh();  //?? 
} 
 
 
For your convenience, we have modified your sample based on our suggestion. Please find the link below. 
 
Sample Link: https://www.syncfusion.com/downloads/support/forum/166914/ze/testss-240146134  
Could you please check the above sample and get back to us, if you need any further assistance on this. 
 
Regards, 
Sangeetha M

John · Answer

Sangeetha,Thank you for your reply and your example.  However, this is not the behavior that I seek.  In your example, the resulting spreadsheet contains only the new data.  The old data is cleared. This isn't what I was hoping for.  Instead, I was expecting this: Notice how D3 recalculated after A3..C3 was updated.  I need all of the other cells in the spreadsheet to be preserved, and allow only a range of cell values to be changed.  I can do this with calls to .updateCell() individually.  But I was hoping that there was a range function that could be used to speed this up when I have LOTS of cells to update at once.  For example .updateRange("A3..C3",{...}).  I could write my own using .updateCell(), but I'm looking for something that would give the best performance, e.g. perhaps delaying the formula recalculations, or screen repaints, etc., until all of the cells were updated.Thanks.  I will appreciate any help or insight that you can provide.  (By the way, I am really excited about this control and cannot wait to use it in my current project.)

Sangeetha Priya Murugan · Answer

Hi John, 
 
Thank you for your update. 
 
We have checked your reported requirement and it can be achievable in our spreadsheet by using the updateCell method as like as below. 
 
Code Example: 
 




 
function pokeData() { 
 
    //var newValues = [{"Color": 'Green',"Bin 1": 100,"Bin 2": 200}]; 
    var newValues =[[{value : "Green1"},{value:100},{value:200}], 
    [{value : "Green2"},{value:200},{value:400}], 
    [{value : "Green3"},{value:300},{value:600}]]; 
 
    var index = ejs.spreadsheet.getRangeIndexes("A2"); // startcell  
    for(var i = 0;i< newValues.length;i++){ // for row iteration 
        for(var j=0;j<newValues[i].length;j++) { // for column iteration 
            var address = ejs.spreadsheet.getRangeAddress([index[0] + i, index[1] + j, index[2] + i, index[3] + j]); 
            spreadsheet.updateCell({ value: newValues[i][j].value}, address);  
     } 
    } 
 
} 
 
 
For your convenience, we have modified your sample based on our suggestion. Please find the link below. 
 
Sample Link: https://www.syncfusion.com/downloads/support/forum/166914/ze/testss2030394408  
 
Could you please check the above details and get back to us, if you need any further assistance onn this. 
 
Regards, 
Sangeetha M

John · Answer

This method seems very slow, especially if you need to update 100's of cells.  Is there a way to update the values of a lot of cells faster without losing the cell styles?  Perhaps there is some type of lockUpdates() call that would improve performance.  Ideally, a new method "updateCells" would be nice, that works as fast as the openFromJSON() or ranges[] = {} ,methods.

Gayathri KarunaiAnandam · Answer

Hi John, 

We have checked your reported case in our end. It took 4 seconds to update 100 cells having value field. To proceed further, please share the below details, 


UI screenshot/Video demonstration of the issue you are facing.
Share the CellModel you are updating.
If possible, please share the update cell method related code.
Share the number of rows and columns you are updating.

Please share the above requested details, based on that we will check and update promptly. 

Regards, 
Gayathri K

John · Answer

Thanks for your prompt reply. Let me put together a sample project that demonstrates the issue.  It may take a couple of days as I have some pressing obligations today and tomorrow.

Gayathri KarunaiAnandam · Answer

Okay John, we will wait to hear from you.

John · Answer

Ok, I have uploaded an example that compares two different methods to update a batch of cell values. To run the test, click each of the buttons which will populate the spreadsheet with random numbers. The execution time of each method is output to the console.

Notice how much faster the save/load method is than the updateCell() method. The difference is over 100 times faster.

There appears to be quite a lot of overhead with the updateCell() method, unless you only need to update a few cells at a time. My use case will frequently need to update many cells at once. It seems that saving and loading the whole spreadsheet is a bit drastic and may interfere with the current user's activity if they are manually editing other cells.

What I think is needed is a method that will update a range of cell values at once, using similar logic as the openFromJSON. This method could take a starting cell target (top-left) and an 2D array of values to plug into the spreadsheet.

Thoughts?

Attachment: testss_df5d807e.zip

Gayathri KarunaiAnandam · Answer

Hi John, 

We can update the cell values faster by updating the cellModel. We can use this code snippet when the number of cells need to update is higher. Please check the below code snippet. 





function pokeData() { 
    console.time("updatecell") 
    var ActiveSheet = spreadsheet.getActiveSheet(); 
    for (var i = 1; i < 100; i++) {  
        if (!ActiveSheet.rows[i]) { // Assigning empty row, if it is not available. 
 
            ActiveSheet.rows[i] = { cells: [] }; 
       
          } 
         // for row iteration  
        for (var j = 1; j < 13; j++) { // for column iteration  
            if (!ActiveSheet.rows[i].cells[j]) { // Assign empty cell is that cell is not available. 
 
                ActiveSheet.rows[i].cells[j] = {}; 
           
              } 
              ActiveSheet.rows[i].cells[j].value = rndnum(); 
        } 
         
    } 
    spreadsheet.resize(); 
    console.timeEnd("updatecell") 
} 
 

By using the above code we can update cell value within 6 ms. Please check and get back to us, if you need further assistance. 

Regards, 
Gayathri K

John · Answer

Thank you for your reply.  I was able to implement this method successfully with satisfactory performance.  The only change was to call spreadsheet.refresh() instead of resize() as the formulas were not being recalculated after update.  This put the total time to update the 1200 cells to around 100ms.  Perfect!

Gayathri KarunaiAnandam · Answer

Hi John, 
 
We are happy to hear that your requirement has been fulfilled. Please feel free to contact us if you need any further assistance on this. 
 
Regards, 
Gayathri K