Worksheet Data Range

Worksheet Data Range

Posted by: ichioka.yuji on 23 March 2025, 12:59 am EST

• 

  ichioka.yuji

  • Post Options:
  • Link
    Copy

  Posted 23 March 2025, 12:59 am EST - Updated 23 March 2025, 1:02 am EST

  Hello,

  The Worksheet Data Range supported in V18 is very convenient. In my application, I dynamically add and remove Data Ranges on specific cell ranges.

  By pressing the Delete key on a cell within the Data Range, the value corresponding to that cell in the data source is cleared. However, after removing the Data Range, the formulas or values in the original worksheet cells are also cleared.

  I want the operations of setting or clearing values on the Data Range to affect only the associated data source, without impacting the data model of the original worksheet.

  In my application codes, the setValue method of the IDataProvider interface always returns true, and the onClear method always returns false. However, it seems that this is not working as intended.

  https://developer.mescius.com/spreadjs/api/interfaces/GC.Spread.Sheets.DataRange.IDataProvider#setvalue

  https://developer.mescius.com/spreadjs/api/interfaces/GC.Spread.Sheets.DataRange.IDataProvider#onclear

  Any helps would be appreciated.

  Yuji

• 

  priyam.kushwaha

  

  • Post Options:
  • Link
    Copy

  Posted 24 March 2025, 8:15 am EST

  Hi,

  Thank you for reaching out.

  Based on our observations, the behavior you are experiencing is expected and aligns with how IDataProvider interacts with the worksheet model and Data Range. Here is a breakdown of the behavior:

  When setValue returns true

  The value is set in the data range only and not in the worksheet model. You can verify this behavior as follows:

  • Return false from the setValue method.
  • Modify the value in the area where the data range is present.
  • Save the workbook and open it in another SpreadJS instance that does not contain the data range provider.
  • The value will be present in the exported workbook.

  Please refer to the below screen recording illustrating the same: setValue: https://drive.google.com/file/d/1XeMu75IUXDsVRy9TVnOtItFP_JHRqI1r/view?usp=sharing

  When onClear returns false

  The value in the data range remains as it is until the value is externally set to null as illustrated on the Demo Site.

  If you have any other understanding or require additional guidance, please let us know.

  Best Regards,

• 

  ichioka.yuji

  • Post Options:
  • Link
    Copy

  Posted 25 March 2025, 10:10 am EST

  Hello

  With the demo, here is the procedure I tried.

  1. Add return false to the end of the implementation codes of onClear method.
  2. Set the cell E23 to a cetain value like ”1234".
  3. Click the “Move Down” button to overwrap the table (or the data range) on the cell E23.
  4. Move the cursor to the cell E23 and push DELETE key there.
  5. Click the “Move up” button and see the cell E23.
  6. The cell E23 value was cleared. This is not behaivior as I expected.

  Furthermore, the result was the same when I tried adding “return true;” instead of “return false;” in the step 1 above. Please let me know what is different when onClear method returns true or false;

  [Demo]
  
  https://developer.mescius.com/spreadjs/demos/sample/features/data-range/basic/purejs/

• 

  priyam.kushwaha

  

  • Post Options:
  • Link
    Copy

  Posted 26 March 2025, 7:30 am EST

  Hi,

  We can replicate the behavior you mentioned on our end. However, it seems to be by design because the delete key is pressed on a worksheet cell, which should delete both the sheet model value and the data provider value. We are discussing it with the concerned teams if it is possible to delete only the data range value. The internal tracking ID for the same is SJS-28886.

  In addition, the onClear method is not defined properly in the documentation, therefore, we are discussing the behavior of this method with the concerned teams and will update you as soon as we get any information from their end. The internal tracking ID for the same is SJS-28887.

  Best Regards,

• 

  ichioka.yuji

  • Post Options:
  • Link
    Copy

  Posted 27 March 2025, 9:48 am EST

  Thank you for your reply.

  In addition, it would be perfect if it is possible to prevent to set it to the sheet model even when a formula (e.g. “=E23*2”) is entered into a cell of the data range.

  Using the return value of the setValue method, I can control whether or not the sheet model should be changed when I enter a new value on the data range, such as “1234”. However, there is no way to do this when a new formula is entered.

• 

  ankit.kumar

  

  • Post Options:
  • Link
    Copy

  Posted 28 March 2025, 5:00 am EST

  Hi,

  You could use the SpreadJS events to detect if a formula is entered in the data range and prevent editing the sheet model. Please refer to the code snippet below, which illustrates the same:

  spread.bind(GC.Spread.Sheets.Events.EditEnding, function(sender, args) {
    if(args.editingText) {
      const range = dataRange.range();
      if(range.contains(args.row, args.col, 1, 1)) {
        if(args.editingText.startsWith("=")) {
          args.cancel = true;
          spread.suspendEvent();
          args.sheet.endEdit(true);
          spread.resumeEvent();
        }
      }
    }
  });

  You can refer to the attached sample that uses the above code snippet and handles the formula as expected : https://jscodemine.mescius.io/share/lkLP7edc0EWW-GNH_2P8Rw/?defaultOpen={"OpenedFileName"%3A["%2Findex.html"%2C"%2Fapp.js"]%2C"ActiveFile"%3A"%2Fapp.js"}

  In addition, we are also discussing this case with the concerned teams and will update you as soon as we get any information from their end. The internal tracking ID for the same is SJS-28950.

  Please feel free to reach out if you encounter any further issues or require additional guidance.

  Regards,

  Ankit