Spreadsheet Row Dropdown List

[Judd]

Does the Kendo Spreadsheet have the functionality to have a drop down list in a row like Excel?

[Veselin Tsvetanov]

Hi Judd,

Yes, the Kendo Spreadsheet offers such functionality. Here you will find the Custom cell editors demo, where a drop-down is configured for the values in the B1 cell. Note, that Cell editor is specified using the cell validation:

cells: [

   { value: "Select item:", bold: true },

   { background: "#fef0cd",

     validation: {

         dataType: "list",

         showButton: true,

         comparerType: "list",

         from: '{ "Foo item 1", "Bar item 2", "Baz item 3" }',

         allowNulls: true,

         type: "reject"

     }
    }

]

Keep in mind, that the Cell editor could be configured per cell, as in the above example, or per range of cells, using the Spreadsheet.Range.validation() method.

Regards,
Veselin Tsvetanov
Progress Telerik

[Dinesh]

By implementing the validation approach in cell the list will be shown, but what if we need to allow the user to enter any other values which are not in list. 

So if list has A,B,C option, but user type D in cell it will gives validation issue. Can we allow user to enter anything in list option and cell should not be highlighted as validation. Is that possible Veselin?

[Veselin Tsvetanov]

Hi Dinesh,

You could change the validation type from reject to warning. That would allow the user to type custom input in the cells in question:

{ background: "#fef0cd",
  validation: {
      dataType: "list",
      showButton: true,
      comparerType: "list",
      from: '"Foo item 1,Bar item 2,Baz item 3"',
      allowNulls: true,
      type: "warning"
  } }

The above, however, will still display the validation warning triangle. If you need to avoid that, you should reset the validation configuration upon change. To do that you could use the Spreadsheet change event:

change: function(e) {
  var range = e.range;
  var topLeft = range.topLeft();

  if (topLeft.row === 1 && topLeft.col === 1) {
    var validation = range.validation();
    var from = validation.from;
    var textOnly = from.substring(1, from.length -1);
    var array = textOnly.split(',');
    var value = range.value();

    if (array.indexOf(value) === -1) {
      textOnly = textOnly + ',' + value;
      validation.from = '"' + textOnly + '"';

      range.validation(validation);
    }
  }
},

Here is a small sample implementing the above.

Regards,
Veselin Tsvetanov
Progress Telerik

[Nishant]

@Veselin Tsvetanov, Does same functionality can be implemented using Kendo React?? We'd need dropdowns list in columns for validations in excel using Kendo React UI.

[Veselin Tsvetanov]

Yes, the Kendo React SPreadsheet component is actually a wrapper of the jQuery Spreadsheet. Hence, all features available in the jQuery Spreadsheet are also available in the React wrapper.