Autocomplete with SpreadJS

Question

I'm trying to add autocomplete functionality to a column in SpreadJS, such that as the user types into a cell a dropdown list will appear with matching items retrieved from the server. The SpreadJS documentation states:

SpreadJS supports combo box, check box, button, text, hyperlink, Wijmo edit cell (AutoCompleteTextBox), and custom cell types.

http://helpcentral.componentone.com/NetHelp/SpreadHClientUG/celltypes.html

AutoCompleteTextBox sounds like it may do the trick; however, I can't find any documentation or samples that demonstrate how this can be achieved. I could create a custom cell type, but if I can leverage functionality that is already there it would be much better!

Does anyone know how I can implement this?

Thanks, Scottie

Answer 1

I too had the same problem, but I tweaked the TextCellType to use jQueryUI autocomplete.

  <div>
    <input type='hidden' id="dropDownCellInfo" />
    <div id="ss" style="height:500px;border:solid gray 1px;"/>
  </div>

You can refer the jQuery UI documentation to know more about using the autocomplete widget. The below code creates a TextCellType and overriders its create editor method to create a text element and initialize it using the jQuery autocomplete.

I had to use a hidden text element to capture the row and column in which I have to update the value after you select an option from the list. There might be a better way, but this worked for.

  var cellType = new $.wijmo.wijspread.TextCellType();
  cellType.createEditorElement = function(context)
  {
    var obj = jQuery('#dropDownCellInfo');
    obj.data('sheet' , context.sheet);
    obj.data('row', context.row);
    obj.data('col', context.col);

    console.log(context);
    var $textarea = $('<input type="text" id="txtSearch" style="visible:false" />');
    var editor = document.createElement("div");
    $(editor).append($textarea);
    $textarea.autocomplete({
      minLength: 2,
      autoFocus : true,
      source: 'http://localhost/spreadjs/index.php',
      focus: function( event, ui ) {
        $( "#txtSearch" ).val( ui.item.inst_name );
        return false;
      },
      select: function( event, ui ) {
        $( "#txtSearch" ).val( ui.item.inst_name );              
        var obj = jQuery('#dropDownCellInfo');
        var spd = jQuery("#ss").wijspread("spread").getActiveSheet().setActiveCell(obj.data('row'),obj.data('col'));

        // We have to explicitly remove the list element from the DOM because some 
        // how the method creates a new list for each autocomplete request and does not deletes the list after onSelect event.
        jQuery('ul.ui-autocomplete').remove();
        return false;
      }
    })
    .autocomplete( "instance" )._renderItem = function( ul, item ) {
      return $( "<li>" )
        .append( "<a>" + item.inst_name + "</a>" )
        .appendTo( ul );
    };
    return editor
  };
  sheet.getColumn(3).cellType(cellType);