How to implement excel alt+down key to display the drop down of a List Validator

Question

One of the requirements for my app is to be able to navigate the sheet completely without using the mouse. It seems that for some reason the excel keybind Alt+Down does not work in SpreadJS.

I've already implemented a workaround which is basically activating the sheet edit mode on the cell bound to Alt+Down, this allows you to cycle through the list values using the up and down keys, but you're not able to see all values from the list (the list doesn't "drop down").

Any chance there's a nice way to implement this using SpreadJS functions or is my only option to do "hacky" things like forcing a click event in some x,y coordinates relative to the cell, in an attempt to catch the dropdown icon?

Answer 1

You can create a custom command add it to the Spread's commandManager.

var commandManager = spread.commandManager()
        // register command
        commandManager.register('showList', {
                canUndo: false,
                execute: function(workbook) {
                    var sheet = workbook.getActiveSheet();
                    var r = sheet.getActiveRowIndex();
                    var c = sheet.getActiveColumnIndex();
                    var validator = sheet.getCell(r, c).validator();
                    if (validator) {
                        // if cell has data validator, show the list.
                        workbook.getHost().getElementsByTagName('input')[0].click();   
                    }                     
                }
            }, 
        40, false, false, true, false);

Full Code for completeness:

var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"));
        var sheet = spread.getActiveSheet();
        var dv1 = new GC.Spread.Sheets.DataValidation.createListValidator('Fruit,Vegetable,Food');
        dv1.inputTitle('Please choose a category:');
        dv1.inputMessage('Fruit, Vegetable, Food');
        sheet.setDataValidator(0, 1, dv1);
        sheet.setDataValidator(1, 1, dv1);

        var commandManager = spread.commandManager()
        // register command
        commandManager.register('showList', {
                canUndo: false,
                execute: function(workbook) {
                    var sheet = workbook.getActiveSheet();
                    var r = sheet.getActiveRowIndex();
                    var c = sheet.getActiveColumnIndex();
                    var validator = sheet.getCell(r, c).validator();
                    if (validator) {
                        // if cell has data validator, show the list.
                        workbook.getHost().getElementsByTagName('input')[0].click();   
                    }                     
                }
            }, 
        40, false, false, true, false);

Navigate to cell B1 or B2 and press Alt+Down.