This is a migrated thread and some comments may be shown as answers.

Spreadsheet Regex Validation

2 Answers 203 Views
Spreadsheet
This is a migrated thread and some comments may be shown as answers.
Michael
Top achievements
Rank 1
Michael asked on 05 Apr 2017, 09:30 PM

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 Answers, 1 is accepted

Sort by
0
Michael
Top achievements
Rank 1
answered on 06 Apr 2017, 06:15 PM

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
Top achievements
Rank 1
commented on 09 Feb 2022, 06:51 PM

Does anyone have a way to do this in latest kendo builds? This same mechanism doesn't appear to work for me anymore.
Neli
Telerik team
commented on 11 Feb 2022, 10:23 AM

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

0
Ivan Danchev
Telerik team
answered on 07 Apr 2017, 01:03 PM
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.
Tags
Spreadsheet
Asked by
Michael
Top achievements
Rank 1
Answers by
Michael
Top achievements
Rank 1
Ivan Danchev
Telerik team
Share this question
or