Kendo Spreadsheet Validations not working on content copy pasted from Excel sheet

[Vijayakumar]

I have a Kendo UI spreadsheet and defined validations to columns. Validations are working when I enter data manually in the sheet. But if I do a Copy paste from Excel, the validations are not working. Any help on this is highly appreciated

            $(function () {
                $("#spreadsheet").kendoSpreadsheet({
                    //                excel: {                
                    //                    // Required to enable saving files in older browsers
                    //                    proxyURL: "//demos.telerik.com/kendo-ui/service/export"
                    //                },   
                    columns: 25,
//                    headerHeight: 0,
//                    headerWidth: 0,
                    toolbar: false,
//                    formulabar: false,
                    sheetsbar: false,
                    sheets: [
                    {
                        rows: [
                            {
                                height: 30,
                                cells: [
                                    {
                                        value: "Column1", background: "rgb(204,204,204)", textAlign: "center", color: "rgb(255,0,0)", bold: "true", fontName: "Arial"
                                    },
                                    {
                                        value: "Column2", background: "rgb(204,204,204)", textAlign: "center", color: "rgb(255,0,0)", bold: "true", fontName: "Arial"
                                    },
                                    {
                                        value: "Column3", background: "rgb(204,204,204)", textAlign: "center", color: "rgb(255,0,0)", bold: "true", fontName: "Arial"
                                    }
                                ]
                            }
                        ],
                        columns: [
                            {
                                width: 80
                            },
                            {
                                width: 115
                            },
                            {
                                width: 115
                            }
                        ]
                        },
                        {
                            name: "ListValues",
                            rows: [ //A2:A1001
                                    {
                                    cells: [
                                                {
                                                    value: 'I'
                                                },
                                                {
                                                    value: 'C'
                                                }
                                            ]
                                    }
                                    ]
                        }
                ]
                    });

                    //Get the column range
                    var range = $("#spreadsheet").data("kendoSpreadsheet").activeSheet().range("A2:A1001");

                    //Apply the validation rule
                    range.validation({
                        dataType: "list",
                        from: "ListValues!A$1:B$1",
                        allowNulls: false,
                        type: "reject",
                        titleTemplate: "Invalid Value",
                        messageTemplate: "Column1 valid values are 'C' and 'I'."
                    });
            });

[Stefan]

Hello Vijayakumar,

After testing the scenario, the same result can be observed in MS Excel and Google Spreadsheet.

Once a value is  in the cell,  the validation rules are cleared as the cell is being replaced.

We had a discussion on this subject before and decided that this is the expected behaviour:

https://github.com/telerik/kendo/issues/5602 

Changing it will create a difference with the other spreadsheet controls, and can create a breaking change in the widget.

Regards,
Stefan
Telerik by Progress

[Christopher]

"Once a value is pasted in the cell,  the validation rules are cleared as the cell is being replaced."

This is true, but both Google Sheets and Excel give the user the option to "Paste by value" as well, which doesn't destroy cell formatting or validation. Ideally, Kendo UI Spreadsheet should give the developer this control of how to handle "pastes", this would align with your goal to keep the same functionality as Sheets and Excel.

[Stefan]

Hello Christopher,

Thank you for the suggestion.

I can recommend submitting a feature request in our feedback portal as this is helping us to prioritise the tasks based on the community demand:

http://kendoui-feedback.telerik.com/forums/127393-kendo-ui-feedback/category/170283-spreadsheet

Thank you in advance.

Regards,
Stefan
Telerik by Progress

[Marin Bratanov]

Here is the link to the concrete feature request: http://kendoui-feedback.telerik.com/forums/127393-kendo-ui-feedback/suggestions/18240025-configure-kendo-spreadsheet-to-paste-values-only.