Split: Detecting a datavalidation event on pasting data

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

  • 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
  • Replied 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?
  • Replied 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
  • Replied 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
  • Replied 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
  • Replied 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
  • Replied 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.
  • Replied 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.
  • Marked as Answer

    Replied 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
Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels