Handsontable dropdowns with multiple selections

Question

I am trying to extend the handsontable plugin to support multiple selections in its dropdown list. I have already tried extending the base Editor built into the library by modifying the 'dropdownEditor' as suggested https://github.com/trebuchetty/Handsontable-select2-editor/issues/7. I spent hours reading and searching through the source for keywords but I am not coming up with anything of real use.

I do not mind if this is answered using the Angular extension or another native ECMA5 or 6 way of extending the https://github.com/handsontable/handsontable plugin.

So far my only thoughts were to actually extend the framework with this bit of code following the patterns that exist. I added all LOC below that are pointing to: multiselect or Handsontable.MultiselectDropdownCell copied the dropdown method, called the new name and everything works, however still cannot see where I could begin to find what I am looking for.

Handsontable.MultiselectDropdownCell = {
  editor: getEditorConstructor('multiselectdropdown'),
  renderer: getRenderer('autocomplete')
};

Handsontable.cellTypes = {
  text: Handsontable.TextCell,
  date: Handsontable.DateCell,
  numeric: Handsontable.NumericCell,
  checkbox: Handsontable.CheckboxCell,
  autocomplete: Handsontable.AutocompleteCell,
  handsontable: Handsontable.HandsontableCell,
  password: Handsontable.PasswordCell,
  dropdown: Handsontable.DropdownCell,
  multiselect: Handsontable.MultiselectDropdownCell
};

Handsontable.cellLookup = { validator: {
    numeric: Handsontable.NumericValidator,
    autocomplete: Handsontable.AutocompleteValidator
}};

I have at a modified version of dropdown editor in place that looks like:

import {getEditor, registerEditor} from './../editors.js';
import {AutocompleteEditor} from './autocompleteEditor.js';

/**
 * @private
 * @editor MultiSelectDropdownEditor
 * @class MultiSelectDropdownEditor
 * @dependencies AutocompleteEditor
 */
class MultiSelectDropdownEditor extends AutocompleteEditor {
  prepare(row, col, prop, td, originalValue, cellProperties) {
    super.prepare(row, col, prop, td, originalValue, cellProperties);
    this.cellProperties.filter = false;
    this.cellProperties.strict = true;
  }
}

export {MultiSelectDropdownEditor};

registerEditor('multiselectdropdown', MultiSelectDropdownEditor);

At this point I have no clue where the click event is happening when the user selects an item from the dropdown list. Debugging has been painful for me because it is through Traceur. I tried setting a click event after the module is ready and the DOM is as well however I cannot get even an alert to fire based off of a click on one of the select dropdown cells. The 'normal' cells I can get a click with a simple:

$('body').on('click','#handsontable td', someAlert)

However, not so for the menu contents. Right clicking to inspect the dropdown menu means first disabling the context menu like the one on http://handsontable.com/. Then you will notice that right clicking to inspect anything will fire an event that closes the dropdown menu you are trying to inspect.

I've put breakpoints all through the libraries source code, I cannot figure this one out.

The only thing I want to do is to figure out where the part of the code that highlights the menu item and sets it to an active selection, turn that into a method that accepts multiple selections (up to the entire array of options available, clicking an active item will disable it lets just say).

Then ensuring that those selections are actually in the Handsontable 'data scope'.

Thats it, I don't need it to even render in the cell what things have been chosen, although any help there would be great because unfortunately, I am yet to find the spot when the options in the dropdown are rendered either.

I have also tried using the Select2Editor made for handsontable as seen http://jsfiddle.net/4mpyhbjw/40/ and https://github.com/trebuchetty/Handsontable-select2-editor/issues/3 , however it does not help my cause much. Here is what the dropdown cell in handsontable looks like:

http://docs.handsontable.com/0.15.1/demo-dropdown.html

Finally, heres a fiddle: http://jsfiddle.net/tjrygch6/

I would be super appreciative if someone could help me out here. Thanks SO!

UPDATE

I have managed to parse the values in the cell and turn the type into an array containing the values (so typing red blue will turn an array containing ['red','blue']) . I have run this array through the internal sort algorithm which parses the options and returns an index of a matching item. I get this working fine and I now am passing the array into the highlight method. This method passes the values the the core library WalkOnTable. I do not see where I can alter the logic to select more than one value instead of unhighlighting the first option.

 this.selectCell = function(row, col, endRow, endCol, scrollToCell, changeListener) {
var coords;
changeListener = typeof changeListener === 'undefined' || changeListener === true;
if (typeof row !== 'number' && !Array.isArray(row) || row < 0 || row >= instance.countRows()) {
  return false;
}
if (typeof col !== 'number' || col < 0 || col >= instance.countCols()) {
  return false;
}
if (typeof endRow !== 'undefined') {
  if (typeof endRow !== 'number' || endRow < 0 || endRow >= instance.countRows()) {
    return false;
  }
  if (typeof endCol !== 'number' || endCol < 0 || endCol >= instance.countCols()) {
    return false;
  }
}
// Normal number value, one item typed in
if (!Array.isArray(row) && typeof row === 'number'){
  coords = new WalkontableCellCoords(row, col);

  walkSelection(coords);
}

This is the spot where I think I need WalkontableCellCoords to be modified to accept an array and then highlight and select both values when the dropdown is opened and closed. I also need to be able to select multiple options via touch or click event.

else {
  // Array found, apply to each value
  new WalkontableCellCoords(row[0], col);
  new WalkontableCellCoords(row[1], col);
}

function walkSelection(coords){
  priv.selRange = new WalkontableCellRange(coords, coords, coords);
  if (document.activeElement && document.activeElement !== document.documentElement && document.activeElement !== document.body) {
    document.activeElement.blur();
  }
  if (changeListener) {
    instance.listen();
  }
  if (typeof endRow === 'undefined') {
    selection.setRangeEnd(priv.selRange.from, scrollToCell);
  } else {
    selection.setRangeEnd(new WalkontableCellCoords(endRow, endCol), scrollToCell);
  }
  instance.selection.finish();
}

return true;

};

Update 2

I have gotten the internal methods to recognize and partially select both values in the DOM however it is far from correct still.

Here is the console output generated by the method WalkOnTableCellCords to be called which seems to be what highlights the dropdown selection in the case where the cell contains only 1 value (default functionality). This output is from typing black blue into a dropdown cell containing both blue and black as individual options in the list.

extended_hot_v15-01.js:5041 DropdownEditor {
            "highlight": {
                    "row": 6,
                    "col": 0
            },
            "from":
                   {
                    "row": 4,
                    "col": 0
                   },
             "to": {
                    "row": 6,
                    "col": 0
                    }
            }

UPDATE If anyone solves this, I will personally fly to wherever you are in person, and shake your hand. TWICE.

Solution 1

Wow. So much effort. Now, more than a year later, it is much easier.

I used the Chosen jQuery plugin successfully. It was pretty easy.

Here's one person's example: https://github.com/mydea/handsontable-chosen-editor

Chosen is beautiful. I am using autocomplete with a multiselect. Here's the renderer:

function customDropdownRenderer(instance, td, row, col, prop, value, cellProperties) {

    var selectedId;
    var optionsList = cellProperties.chosenOptions.data;

    if(typeof optionsList === "undefined" || typeof optionsList.length === "undefined" || !optionsList.length) {
        Handsontable.TextCell.renderer(instance, td, row, col, prop, value, cellProperties);
        return td;
    }

    var values = (value + "").split(",");
    value = [];
    for (var index = 0; index < optionsList.length; index++) {

        if (values.indexOf(optionsList[index].id + "") > -1) {
            selectedId = optionsList[index].id;
            value.push(optionsList[index].label);
        }
    }
    value = value.join(", ");

    Handsontable.TextCell.renderer(instance, td, row, col, prop, value, cellProperties);
    return td;
}

and then I just set up the particular column like this:

columns: [
    {},
    {},
    {type: 'numeric'},
    {type: 'dropdown', source: ['', 'NAME', 'FNB']},
    {},
    {},
    {},
    {},
    {},
    {},
    {},
    {type: 'dropdown', source: ['', 'S', 'M']},
    {},
    {},
    {
        renderer: customDropdownRenderer,
        editor: "chosen",
        width: 150,
        chosenOptions: {
            multiple: true,
            data: productData
        }
    },
    {},
    {editor: false, readOnly: true, width: 1}, 
    {editor: false, readOnly: true, width: 1}
],

Solution 2

Ok, I hope it will help you. It took me time to read the api and customize the code :)

I took sample code from Handsontable library (last version) and made little changes.

There might be some bugs with it but it is only a prototype so you can edit and makes that look better of course.

For some reason I didn't success to make the dropdownlist to be clickable. It seems like z-index issue or other css properties games. I trust on you to find how to fix it. Anyway for now, you can use the keyboard to select by holding shift for multiple selection.

The output is a collection of joined selected options by comma separated.

for example:

To make that work add this code after you load handsontable libary. It will extend your Handsontable cell types.

(function(Handsontable) {
    var SelectEditor = Handsontable.editors.BaseEditor.prototype.extend();

    SelectEditor.prototype.init = function() {
        // Create detached node, add CSS class and make sure its not visible
        this.select = document.createElement('SELECT');
        Handsontable.Dom.addClass(this.select, 'htSelectEditor');
        this.select.style.display = 'none';

        // Attach node to DOM, by appending it to the container holding the table
        this.instance.rootElement.appendChild(this.select);
    };
    // Create options in prepare() method
    SelectEditor.prototype.prepare = function() {
        // Remember to invoke parent's method
        Handsontable.editors.BaseEditor.prototype.prepare.apply(this, arguments);
        this.isMultiple = !!this.cellProperties.multiple;
        if (this.isMultiple) this.select.multiple = true;
        var selectOptions = this.cellProperties.selectOptions;
        var options;

        if (typeof selectOptions == 'function') {
            options = this.prepareOptions(selectOptions(this.row,
                this.col, this.prop))
        } else {
            options = this.prepareOptions(selectOptions);
        }
        Handsontable.Dom.empty(this.select);

        for (var option in options) {
            if (options.hasOwnProperty(option)) {
                var optionElement = document.createElement('OPTION');
                optionElement.value = option;
                Handsontable.Dom.fastInnerHTML(optionElement, options[option]);
                this.select.appendChild(optionElement);
            }
        }
    };
    SelectEditor.prototype.prepareOptions = function(optionsToPrepare) {
        var preparedOptions = {};

        if (Array.isArray(optionsToPrepare)) {
            for (var i = 0, len = optionsToPrepare.length; i < len; i++) {
                preparedOptions[optionsToPrepare[i]] = optionsToPrepare[i];
            }
        } else if (typeof optionsToPrepare == 'object') {
            preparedOptions = optionsToPrepare;
        }

        return preparedOptions;
    };
    SelectEditor.prototype.getValue = function() {
        var result = [];
        var options = this.select && this.select.options;
        var opt;

        for (var i = 0, iLen = options.length; i < iLen; i++) {
            opt = options[i];

            if (opt.selected) {
                result.push(opt.value || opt.text);
            }
        }

        return result.join();
    };

    SelectEditor.prototype.setValue = function(value) {
        this.select.value = value;
    };

    SelectEditor.prototype.open = function() {
        var width = Handsontable.Dom.outerWidth(this.TD);
        // important - group layout reads together for better performance
        var height = Handsontable.Dom.outerHeight(this.TD);
        var rootOffset = Handsontable.Dom.offset(this.instance.rootElement);
        var tdOffset = Handsontable.Dom.offset(this.TD);
        var editorSection = this.checkEditorSection();
        var cssTransformOffset;

        if (this.select && this.select.options && this.isMultiple) {
            var height = 0;
            for (var i = 0; i < this.select.options.length - 1; i++) {
                height += Handsontable.Dom.outerHeight(this.TD);
            }
        }

        switch (editorSection) {
            case 'top':
                cssTransformOffset = Handsontable.Dom.getCssTransform(this.instance.view.wt.wtScrollbars.vertical.clone.wtTable.holder.parentNode);
                break;
            case 'left':
                cssTransformOffset = Handsontable.Dom.getCssTransform(this.instance.view.wt.wtScrollbars.horizontal.clone.wtTable.holder.parentNode);
                break;
            case 'corner':
                cssTransformOffset = Handsontable.Dom.getCssTransform(this.instance.view.wt.wtScrollbars.corner.clone.wtTable.holder.parentNode);
                break;
        }
        var selectStyle = this.select.style;

        if (cssTransformOffset && cssTransformOffset !== -1) {
            selectStyle[cssTransformOffset[0]] = cssTransformOffset[1];
        } else {
            Handsontable.Dom.resetCssTransform(this.select);
        }

        selectStyle.height = height + 'px';
        selectStyle.minWidth = width + 'px';
        selectStyle.top = tdOffset.top - rootOffset.top + 'px';
        selectStyle.left = tdOffset.left - rootOffset.left + 'px';
        selectStyle.margin = '0px';
        selectStyle.display = '';

    };

    SelectEditor.prototype.checkEditorSection = function() {
        if (this.row < this.instance.getSettings().fixedRowsTop) {
            if (this.col < this.instance.getSettings().fixedColumnsLeft) {
                return 'corner';
            } else {
                return 'top';
            }
        } else {
            if (this.col < this.instance.getSettings().fixedColumnsLeft) {
                return 'left';
            }
        }
    };

    SelectEditor.prototype.close = function() {
        this.select.style.display = 'none';
    };

    Handsontable.editors.registerEditor('dvirH', SelectEditor);

})(Handsontable);

The way to use it:

var container = document.getElementById("example1");
var hot1;

hot1 = new Handsontable(container, {
    data: [
        ['2008', 'Nissan', 11],
        ['2009', 'Honda', 11],
        ['2010', 'Kia', 15]
    ],
    colHeaders: true,
    contextMenu: false,
    columns: [{}, {
        editor: 'select',
        selectOptions: ['Kia', 'Nissan', 'Toyota', 'Honda'],
        //  notice that attribute. You can remove it to get a regular select
        multiple: true
    } {}]
});

Live demo in here

To make it easy on you. If you want to edit the code there are 2 methods you may want to change.

1. prepare - Will be called every time the user triggered an editor open event. For configurations and manipulations.
2. init - That method will be called every time you click on a cell. It creates the html code so you can change it to checkboxes for example.

Another thing relates to your questions about where things in the code.

Handsontable split any cell type to editor and renders. All the html code of the editor probably exists in the init in case that you want to change one of them. The value which is the html content that appears in the cell when you are not in edit mode exists in getValue method.

I hope it helps, and I hope it fits to your current version.

Comments

[AlphaG33k]

Dvir, firstly let me say thank you very much for the effort put forth so far. Second, could you please provide the above solution in a demo? I was unable to get the code provided working due to a number of errors showing. I even tried debugging the one after setValue() however then it turns out the SelectEditor is undefined. How are you providing access to SelectEditor, you only pass in Handsontable? Thanks again I will take it the rest of the way if you can reproduce those results in a demo.

[AlphaG33k]

Dvir I am unable to get the multiple selections on my MacBook Air will check tomorrow chrome/windows. I am pressing shirt and down or shift and tab - am I pressing the correct pattern? Thanks again!

[Dvir]

Yes. Try with the keyboard.

[AlphaG33k]

I have tried every combination of keypress I can think of, unfortunately I am still unable to select 2 items at once. My apologies if I am doing something wrong. Could you please let me know what browser and os + ver you are on? I have tried in multiple browsers.

[Dvir]

Windows 10, and chrome last version. Double click on the cell, and the dialog will be open. Then click on the scroll, and use your keyboard to navigate between options. Hold the shift and press down key or up key to select more than once option. Click out of the dialog and you'll see your selections.