The concept of CellTypes is one of the major features of SpreadJS. It defines the type of information that appears in a cell, how that information is displayed, and how the user can interact with it.

SpreadJS allows you to create different types of cells such as Button, ComboBox, CheckBox and Hyperlink. You may refer to this documentation link that elaborates the different cell types. This list also contains a cell of Custom type i.e. CustomCellType. This cell type lets you create several new types of cells in order to have different types of functionality in SpreadJS. For example, you can create FivePointedStarCellType, FullNameCellType, etc. and can refer to this link describing the same.

This blog discusses how we can use the same concept to create AutoCompleteCellType in SpreadJS. As the name suggests, AutoCompleteCellType provides the user with a list of values to select from. However, along with the list, it even provides the user an option to type in the cell and, based on the character typed, it displays matching values from the drop-down list as input suggestions. The user can select one of these options to set it as the cell value.

image1

Let's see how we can accomplish the same.

  1. Create an array for the AutoComplete cell type.
var availableTags = [
                "ActionScript",
                "Asp",
                "BASIC",
                "C",
                "C++",
                "COBOL",
                "ColdFusion",
                "Erlang",
                "Fortran",
                "Groovy",
                "Java",
                "JavaScript",
                "Perl",
                "PHP",
                "Ruby"
            ];
  1. Derive the CustomCellType to create the AutoCompleteCellType.
function AutoCompleteCellType() 
{   }
AutoCompleteCellType.prototype = new GC.Spread.Sheets.CellTypes.Base();
  1. Add custom editor to the cell, so as to display the drop down list.
AutoCompleteCellType.prototype.createEditorElement = function (context) {

        var firstElementText;
        var editor = document.createElement("input");
        $( editor ).autocomplete({
               source: availableTags,
                     minLength: 1,
                     focus: function() { return false; },
                     open: function( event, ui ) 
                     {
                     firstElement = $(this).data("uiAutocomplete").menu.element[0].children[0],
                     inpt = $(editor),
                     original = inpt.val(),
                     firstElementText = $(firstElement).text();
                     if (firstElementText.toLowerCase().indexOf(original.toLowerCase()) === 0) 
                     {
                     inpt.val(firstElementText); //change the input to the first match
                     inpt[0].selectionStart = original.length; //highlight from end of input
                     inpt[0].selectionEnd = firstElementText.length; //highlight to the end
                     console.log(inpt);
                     console.log(original);
                     console.log(firstElementText);
                     }
                     },
                    close:function(event,ui)
                    {
                        context.sheet.setValue(context.row,context.col,firstElementText);
                    },
                     autoFocus:true
            });
            return editor;

             }
  1. Write the following code to handle the up and down keys in the drop down list.
AutoCompleteCellType.prototype.isReservedKey = function (e, context) {
// reserve up/down key to select items
if (context.isEditing && (e.keyCode == 40 || e.keyCode == 38)) 
{ 
       return true;
}
return GC.Spread.Sheets.CellTypes.Text.prototype.isReservedKey.apply(this, arguments);
}
  1. Add the following code to assign the AutoCompleteCellType to spread sheet.
var spread = new GC.Spread.Sheets.Workbook($("#ss")[0]);
var sheet = spread.getActiveSheet();
sheet.suspendPaint(true);
sheet.setValue(0, 1, "AutoComplete", GC.Spread.Sheets.SheetArea.colHeader);
sheet.getRange(-1, 1, -1, 1).cellType(new AutoCompleteCellType()).width(100);
sheet.resumePaint(false);
  1. Execute the application and you can see the AutocompleteCellType working.

That's it! You can download the sample code here. Thanks for following along.

If you have any questions or comments, be sure to leave them below. Happy Coding!

Work with CellType in SpreadJS Today

Download SpreadJS Now!

Download Now!