Number validation

Number validation

Posted by: datta on 4 June 2019, 3:40 am EST

• 

  datta

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  Posted 4 June 2019, 3:40 am EST

  Hi ,

  I have a requirement that only numbers(Integer or decimal) should be allowed as input. If user enters character then we should not accept input and set value back to zero. I checked examples for DataValidation object. But there are only examples to show number range validation but not number validation. I know I can add a cell value change event listener and achieve this. But was wondering whether it can be achieved through DataValidation Object

• 

  sharad.tomer

  

  • Post Options:
  • Link
    Copy

  Posted 6 June 2019, 2:46 am EST

  Hi,

  You could use the FormulaValidator to check if the value is number type or not and handle the validationError event to revert the invalid values back to the original cell value. Please refer to the following code snippet and the attached sample:

  function setValidator(spread) {
              var sheet = spread.getActiveSheet();
  
              spread.options.highlightInvalidData = true;
  
              var dv1 = new spreadNS.DataValidation.createFormulaValidator("=ISNUMBER(INDIRECT(ADDRESS(ROW(), COLUMN(), 4)))");
              dv1.errorMessage("Value should be number");
              
              sheet.bind(spreadNS.Events.ValidationError, (s, args) => {
                  if(args.validator.errorMessage() == "Value should be number"){
                      // if, invalid value and restore original value
                      args.validationResult = GC.Spread.Sheets.DataValidation.DataValidationResult.discard;
                  }
              });
  
              sheet.setDataValidator(5, 3, 6, 1, dv1);
          }
  

  ~sharad
  
  basicDataValidator.zip

• 

  datta

  • Post Options:
  • Link
    Copy

  Posted 25 July 2019, 8:10 am EST

  Hi , this solution is not working for below cases

  1. When cell is kept empty
  2. When a cell is copied from excel file and pasted into spreadJS.
• 

  sharad.tomer

  

  • Post Options:
  • Link
    Copy

  Posted 26 July 2019, 2:42 am EST

  Hi,

  1). By default, blank values are excluded from the validation logic. To include blank values in validation, you need to use the ignoreBlank() method. Please refer to the following code snippet and sample demonstrating the same.

  var dv1 = new GC.Spread.Sheets.DataValidation.createFormulaValidator(
      "=ISNUMBER(INDIRECT(ADDRESS(ROW(), COLUMN(), 4)))"
    );
    dv1.ignoreBlank(false);
    dv1.errorMessage("Value should be number");
  

  https://codesandbox.io/s/spread-js-starter-bswtg
  
  2). This is the intended design behaviour same as excel, when we copy/paste cell ranges then call validators are also copy/pasted due to which the existing validator is removed. If you address this scenario as well then you may handle the clipboardPasting event and validate the value before paste.

  Regards