Data validation gets cleared with style

Data validation gets cleared with style

Posted by: akshay.bhinge on 15 July 2022, 5:38 am EST

• 

  akshay.bhinge

  • Post Options:
  • Link
    Copy

  Posted 15 July 2022, 5:38 am EST

  Hi,

  In my use case, I’m clearing the styles from a range but it also clears out the validators added to the cells in that range.
  
  Is there any way I can preserve the validation while clearing the styles?

  I’ve also the code snippet I’m using for your reference:

  
  sheet.clear(
  	0,
          1,
          50,
          50,
          GC.Spread.Sheets.SheetArea.viewport,
          GC.Spread.Sheets.StorageType.style
  );	
  
  

• 

  akshay.bhinge

  • Post Options:
  • Link
    Copy

  Posted 15 July 2022, 5:48 am EST - Updated 3 October 2022, 9:11 am EST

  I’m also adding the picture of the cell data validator I mentioned in the ticket:

• 

  avinash.pathak

  • Post Options:
  • Link
    Copy

  Posted 18 July 2022, 5:30 am EST

  Hi,

  For this, you may use the following code snippet. Please have a look and let me know if you still face any issues.

  
  	var command = {
  			canUndo: true,
  			execute: function(spread, options, isUndo) {
  				var Commands = GC.Spread.Sheets.Commands;
  				if (isUndo) {
  					Commands.undoTransaction(spread, options);
  					return true;
  				} else {
  					Commands.startTransaction(spread, options);
  					spread.suspendPaint();
  					var selections = options.selections;
                      var sheet = spread.getActiveSheet();
  					selections.forEach(function(sel) {
                          for(let r= sel.row;r<sel.row+sel.rowCount; r++){
                              for(let c= sel.col;c<sel.col+sel.colCount; c++){
                                  sheet.setStyle(r,c,null)
                              }
                          }
  					});
  					spread.resumePaint();
  					Commands.endTransaction(spread, options);
  					return true;
  				}
  			}
  		};
  		var commandManager = ss.commandManager();
  		commandManager.register('myClear', command);
  
  /*override old function*/
  
  let old = GC.Spread.Sheets.Worksheet.prototype.clear;
  GC.Spread.Sheets.Worksheet.prototype.clear = function(r,c,rCount,cCount,v,sType){
      if(sType === GC.Spread.Sheets.StorageType.style){
     return commandManager.execute({
          cmd:"myClear",
          sheetName:ss.getActiveSheet().name(),
          selections:[ss.getActiveSheet().getRange(r,c,rCount,cCount,v)]
      })
      }
      return old.apply(this,arguments);
  }
  
  
  

  sample: https://jscodemine.grapecity.com/share/HIygxzjElk_b3N_-RqZjsg/

  Regards,
  
  Avinash