Split: Detecting a datavalidation event on pasting data

Split: Detecting a datavalidation event on pasting data

Posted by: wsantoso on 8 September 2017, 1:01 am EST

• 

  wsantoso

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  Posted 8 September 2017, 1:01 am EST

  Hi Reeva,

  This is still an issue in spread js 9.40. The $.wijmo.wijspread.Events.ValidationError is only fired when leaving the cell on normal edit not if we copy and pasted in a whole cell (or multiple cells). Any update on suggested workaround?

  Regards,
  
  Willson

• 

  wsantoso

  • Post Options:
  • Link
    Copy

  Posted 8 September 2017, 1:01 am EST

  Another related question. Is there a way to check whether all the validators on the sheet or the whole spread object pass their test?

• 

  slawyer

  • Post Options:
  • Link
    Copy

  Posted 8 September 2017, 1:01 am EST

  Hi,

  I have submitted a bug about ValidationError not firing on paste of invalid data (#149516). I agree, it should fire on paste of invalid data in the cell with data validation.

  There is no API for checking whether all the cells with validators on the sheet or workbook contain valid values.

  You can check whether a particular cell has a data validator using getDataValidator:
  
  http://sphelp.grapecity.com/webhelp/SpreadJSWeb/webframe.html#JavascriptLibrary~GcSpread.Sheets.Sheet~getDataValidator.html

  And if the cell contains a data validator, you can check whether the value in the cell is valid using isValid:
  
  http://sphelp.grapecity.com/webhelp/SpreadJSWeb/webframe.html#JavascriptLibrary~GcSpread.Sheets.Sheet~isValid.html

  But there is no API for enumerating the cells with data validation set (you would need to loop through all the possible cells and check each one).

  Regards,
  
  -Sean

• 

  wsantoso

  • Post Options:
  • Link
    Copy

  Posted 8 September 2017, 1:01 am EST

  Thanks Sean,

  I’ll work my way around it then with a combination of the existing pasting/pasted events and looping the affected cells and tracking the failed validations.

  Regards,
  
  Willson

• 

  stephen.young

  • Post Options:
  • Link
    Copy

  Posted 15 May 2018, 12:24 am EST

  Hi,

  Has this issue been fixed? I am getting the same bug in SpreadJS 11.1.0

  Is there a workaround to detect the paste event and fire validation?

  Thanks,

  Steve

• 

  deepak.sharma

  

  • Post Options:
  • Link
    Copy

  Posted 18 May 2018, 12:34 am EST

  Hello,

  As a workaround, you can handle the clipboardchanging event and assign the validator again, for example:

  activeSheet.bind(GC.Spread.Sheets.Events.ClipboardPasted, function (sender, args) {
  
  var nCondition = new GC.Spread.Sheets.ConditionalFormatting.Condition(GC.Spread.Sheets.ConditionalFormatting.ConditionType.textCondition);
  
  nCondition.compareType(GC.Spread.Sheets.ConditionalFormatting.TextCompareType.notEqualsTo);
  
  nCondition.expected(“”);
  
  // nCondition.treatNullValueAsZero(false);
  
  var validator = new GC.Spread.Sheets.DataValidation.DefaultDataValidator(nCondition);
  
  validator.type(GC.Spread.Sheets.DataValidation.CriteriaType.custom);
  
  validator.ignoreBlank(false);
  
  activeSheet.getCell(-1, -1, GC.Spread.Sheets.SheetArea.viewport).validator(validator);
  
  spread.options.highlightInvalidData = true;
  
  spread.refresh();
  
  });

  Thanks,
  
  Deepak Sharma

• 

  shaiagra

  • Post Options:
  • Link
    Copy

  Posted 22 March 2021, 4:57 am EST

  This is not a good solution if I have 1000 of lines and I have already applied different types of validation on them. Then I have to again loop them and set a validation.

• 

  shaiagra

  • Post Options:
  • Link
    Copy

  Posted 22 March 2021, 5:13 am EST

  I also have this requirement that values in the cell should be validated on all actions such as copy paste or drag drop. I have used GC.Spread.Sheets.DataValidation.createFormulaValidator to apply validations on columns I required and GC.Spread.Sheets.Events.ValidationError to discard the invalid value.
  
  But this event is not triggered when copy paste or drag and fill in a sheet.

• 

  avinash.pathak

  

  • Post Options:
  • Link
    Copy

  Posted 22 March 2021, 9:53 pm EST

  Hi Shaily,

  It is by design, actually, we followed the Excel DataValidation policy.

  1. When copy a cell from one to another, not only cell’s value but also cell’s options: formula, style, data validator… will also paste on the target cell. hence, customer need to set clipBoardOptions to only paste value to the cell, otherwise, the target cell’s data validator will be overwritten by the source cell.

  It is the same with Excel.

  2. When paste value to the cell which have data validator, it will not execute the data validation, because paste is a range behavior, if some of the cell in the range are valid but some are not, the data validation could not handle this case.hence, the same with Excel, in that case, we don’t fire validationError event. but, the value will be validated internally and show a red circle if it is incorrect.

  For #2 You could use ClipBoardPasted Event or DragFillBlockCompleted and Varify the range validation. Please refer to the following code snippet and attached sample that demonstrate the same.

  
  spread.options.defaultDragFillType =
      GC.Spread.Sheets.Fill.AutoFillType.fillWithoutFormatting;
    sheet.options.clipBoardOptions =
      GC.Spread.Sheets.ClipboardPasteOptions.values;
    sheet.bind(GC.Spread.Sheets.Events.ClipboardPasted, (e, args) => {
      isValidrange(args.sheet, args.cellRange);
    });
    sheet.bind(GC.Spread.Sheets.Events.DragFillBlockCompleted, (e, args) => {
      isValidrange(args.sheet, args.fillRange);
    });
  }
  function isValidrange(sheet, range) {
    sheet.suspendPaint();
    for (let row = range.row; row < range.row + range.rowCount; row++) {
      for (let col = range.col; col < range.col + range.colCount; col++) {
        let validator = sheet.getDataValidator(row, col);
        let NewValue = sheet.getValue(row, col);
  
        if (validator && validator.getValidList().indexOf(NewValue) === -1)
          //set the Value if the pasted value is not inside the valid List
          // you may also alert the user here
          console.log("CellValue  is Invalid reseting the Cell");
        sheet.setValue(row, col, validator.getValidList()[0]);
      }
    }
    sheet.resumePaint();
  }
  
  
  

  sample: https://codesandbox.io/s/spread-js-starter-forked-992hy

  Regards
  
  Avinash