Spreadsheet Regex Validation

[Michael]

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);

 

 

 

[Michael]

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

[Aaron]

Does anyone have a way to do this in latest kendo builds? This same mechanism doesn't appear to work for me anymore.

[Neli]

Hi Aaron,

I have just replied to the support ticket regarding the Spreadsheet custom validation. For convenience, I will paste my reply here below as well. I would suggest you continue the conversation in a single thread. 

 

Regarding the date validation, I would suggest you take as an example the date column in the Spreadsheet validation demo. 

- https://demos.telerik.com/kendo-ui/spreadsheet/validation

For convenience here you will find an example of validating a date in a single cell. You could use the sheets.rows.cells.format to set the format of the cell. As you could see in the example the from and to options can be used in order to set the range that a date can be selected. 

Regarding using a Regex for validating a cell I would recommend you to use the approach suggested in the How-To article linked below:

- https://docs.telerik.com/kendo-ui/controls/data-management/spreadsheet/how-to/regexp-validation

You could use the described approach and configure a Regex according to the requirements of the project. 

I hope you will find the provided information helpful for resolving the issue.

 

Regards,

Neli

[Ivan Danchev]

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