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?

score 2 · Accepted Answer · answered May 03 '19 at 14:13

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.

Thanks, it worked perfectly, although I had to make a couple of changes as I'm using the angular wrapper for your library. Nothing serious, just removing the new keyword and change the vars for consts due to Typescript preference. — Narshe, May 08 '19 at 08:03

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

1 Answers1