Spreadsheet Regex Validation

3 posts, 0 answers
  1. Michael
    Michael avatar
    4 posts
    Member since:
    Oct 2016

    Posted 05 Apr 2017 Link to this post

    Is there a way to apply a regex to a range validation? Here's the gist of what I'm looking for:

    var spreadsheet = $("#spreadsheet").kendoSpreadsheet({
      rows:10,
      columns: 1,
      toolbar: false,
      sheetsbar: false,
      sheets: [
        {
          rows: [
            {
              height: 30,
              cells: [
                { value: "Decive Password", background: "#001D42", color: "#fff" }
              ]
            }
          ],
          columns: [
            { width: 130 }
            ]
        }
      ]
    }).data("kendoSpreadsheet");
     
    var range = spreadsheet.activeSheet().range("1:1");
    range.enable(false);
     
    var columnSens = spreadsheet.activeSheet().range("A2:A10");
    columnSens.validation({
      dataType: "custom",
      from: 'REGEX("^(()|((?=.*[a-z])(?=.*[A-Z])(?=.*\d)(?=.*[^\da-zA-Z]).{8,60}))$")',
      type: "warning",
      allowNulls: true,
      titleTemplate: "Invalid Password",
      messageTemplate: "Passwords must be between 8 - 60 characters long and contain the following: 1 number, 1 uppercase letter, 1 lowercase letter, and 1 special (non letter or number) character."
    });

     

    If this is not possible, is there any way to use javascript to validate the value with a regex in the onChange event and manually set the field to have an error if it doesn't match? (See the commented area of the code). 

    Dojo: http://dojo.telerik.com/AHefE

      var regex = new RegExp("^(()|((?=.*[a-z])(?=.*[A-Z])(?=.*\\d)(?=.*[^\\da-zA-Z]).{8,60}))$");
      arg.range.forEachCell(function(row, col, cell){
        debugger;
        if(!regex.test(cell.value)){             
          //How to manually show an error in the cell?!?!?
          console.log("failed!");
        }
      });
    }
     
    var spreadsheet = $("#spreadsheet").kendoSpreadsheet({
      change: onChange,
      rows:10,
      columns: 1,
      toolbar: false,
      sheetsbar: false,
      sheets: [
        {
          rows: [
            {
              height: 30,
              cells: [
                { value: "Decive Password", background: "#001D42", color: "#fff" }
              ]
            }
          ],
          columns: [
            { width: 130 }
            ]
        }
      ]
    }).data("kendoSpreadsheet");
     
    var range = spreadsheet.activeSheet().range("1:1");
    range.enable(false);

     

     

     

  2. Michael
    Michael avatar
    4 posts
    Member since:
    Oct 2016

    Posted 06 Apr 2017 in reply to Michael Link to this post

    I came up with a way that feels like a hack, but works thankfully. I would still like to see this possible in the future out of the box.

    Here is what I came up with: http://dojo.telerik.com/AHefE/5

  3. Ivan Danchev
    Admin
    Ivan Danchev avatar
    2061 posts

    Posted 07 Apr 2017 Link to this post

    Hello Michael,

    Thank you for sharing your approach.

    Indeed currently there is not built-in regex support for the Spreadsheet range validation. If you want you could log a feature request in our Feedback Portal. Many of the logged features are implemented in subsequent releases.

    Regards,
    Ivan Danchev
    Telerik by Progress
    Try our brand new, jQuery-free Angular 2 components built from ground-up which deliver the business app essential building blocks - a grid component, data visualization (charts) and form elements.
Back to Top