Check kendo spreadsheet for validation errors

[serge]

could you please  answer the question from SO here https://stackoverflow.com/questions/76809963/check-kendo-spreadsheet-for-validation-errors

Using a (ASP.NET Core) Kendo spreadsheet, how to know if on the sheet are or not validation errors and what kind of if any.

I used this code to add validation

myCell.Validation(v => v
    .ComparerType("custom")
    .DataType("custom")
    .From("R[0]C[0] > 0")
    .Type("warning")
    .ShowButton(true)
    .AllowNulls(true)
    .TitleTemplate("Alerte de validation")
    .MessageTemplate("Attention, cette valeur est négative !"));

[Alexander]

Hi Serge,

Thank you for reaching out and for taking the time to share the currently utilized cell validation held within the boundaries of the component. I really appreciate it.

Upon further examination of the referenced StackOverflow thread, I can confirm that the approach mentioned by "G_P" is indeed a valid one. However, I would recommend altering the incorporated client-side logic in the following manner so that the problematic cells are retrieved accordingly:

.Events(events => events
     .Render("onRender")
)

<script>
    function onRender(e) {
      var spreadsheet = e.sender;
      var totalErrors = 0;
      spreadsheet.sheets().forEach(function(sheet, index){
        // Assert whether the range value is not null.
        if(sheet.range('A3:E8').value() != null)
        {
            sheet.range('A3:E8').forEachCell(function (row, column, cell) {
              // Log the given cell's validation value.
              console.log(cell.validation.value)
              if(cell.validation && !cell.validation.value) {
                // Log error details.
                console.log('sheet ', sheet.name(), ", row ", row, ", col ", column, ', raw value: ', cell.validation.value);
                totalErrors += 1;
              }
            });
        }
      });
      
      $('#total-errors').text(totalErrors);
    }
</script>

For the purposes of the demonstration, the aforementioned logic has an additional layer used for supplementing the number of errors within a separate DOM element for further visualization purposes.

Here is a more visual representation of the produced result:

Whilst outputting the exact location of the cells which have an error:

For your convenience, here is a Telerik REPL example that further depicts the mentioned above:

• Telerik REPL for ASP.NET Core Example

I hope this helps.

Kind Regards,
Alexander
Progress Telerik

[serge]

Thanks for sharing this example and answering my question.

From the answer I understand that there is any built-in mechanism to know on a sheet, if

a) there are validation errors on the sheet (any)

b) how many errors there are and of what kind

In your example I write something on the 9th line, under Hana, the behavior is inconsistent: when I edit by hand, it doesn't allow the value, if I paste it, it allows it... 

when I write something wrong on the 9th line, it does not count as error, because  is not in the range, however put the entire column in range would mean check all the empty cells till the bottom of sheet, that has no big sense, for the performance as well...

Is there any event from sheet if the validation fails for a cell? to count when an event arise, and not check all values.

[Alexander]

Hi Serge,

Generally, the aforementioned approach is considered a customary one due to the fact that at this stage, there is no event that currently would show the validated cell and any meta-information associated with it.

In regard to the aforementioned example, providing a dynamic range would prove more intricate in order to mitigate any performance alters.

A possible recommendation would be to wire to the Changing event of the component which will be triggered when a value or validation in the Spreadsheet is about to be changed upon user interaction and use the meta information there to validate the required cell based on an additional layer of validation logic as per your requirements to extract the problematic cell.

Here is exemplary meta client-side information that would be included once the event is fired:

In addition, I have further found the following public item where such a feature has been requested:

https://feedback.telerik.com/kendo-jquery-ui/1427167-add-event-to-the-spreadsheet-which-triggers-on-a-failed-validation

I have added an additional vote on your behalf in order to increase the item's popularity. For further bringing this to our attention, I have also updated your Telerik points as a token of appreciation.