Skip to main content Skip to footer

Implementing AutoSearch in MultiColumnComboBoxCellType

Spread for ASP.Net control provides various editing options for its users as per their requirements. These editing options come in the form of various Cell types which can be set in a sheet to customize how the user interacts with the information in that cell. For more details on the different available CellTypes, you can refer to this documentation link. Out of the default CellTypes, Spread provides MultiColumnComboBoxCellType Class. The MultiColumnComboBoxCellType lets you create a combo box cell with multiple columns in the drop-down list. You can provide a drop-down list and allow the user to choose from a displayed list. You specify the list of items by binding the cell. You can also choose which column is displayed in the edit area of the cell. Now the important part about MultiColumnComboBoxCellType is that this is not editable i.e. the user cannot type in this cell; the only way to enter the value in the cell is to select from the drop down list being displayed. However, in some scenarios there may be chances that the drop down list has many items and it would be difficult to select an item by scrolling the list. In such a scenario the customer would look for an AutoSearch feature wherein the user can type into the cell. Based on the user input, the values are filtered and displayed in the drop down list with the most appropriate value being highlighted and from there user can select the desired value. As of now, the above functionality is not supported in MultiColumnComboboxCellType and this blog looks to provide a simple solution to implement AutoSearch in the cell. The basic logic behind the implementation handles the Key events in the javascript. All you need is to make the cell editable and show the drop down button when the user double clicks the cell and makes it enter edit mode firing the onEditStart event. In this event you can add the KeyDown handler to which will further be used to implement the main AutoSearch functionality. Here is the code for the same:


function onEditStart(e)  
{  
  if (e.cell.getAttribute("FpCellType") != "MultiColumnComboBoxCellType") return;  
  var editor = document.getElementById(e.cell.getAttribute('FpEditorID'));  
  if (editor == null) return;  
  var textbox = document.getElementById(editor.id + "_Input");  
  if (textbox == null) return;  
  e.spread.editingTextbox = textbox;  
  FarPoint.System.WebControl.MultiColumnComboBoxCellType.CheckInit(editor.id);  
  comboBoxObj = eval(editor.id + "_Obj");  
  textbox.addEventListener("keydown", onKeyDown);  
}  

In the KeyDown event, the actual logic to perform searching and highlighting the items is implemented. It’s a simple code to get the value typed in the cell by the user and use this input to search the drop down list for appropriate matches which are displayed and highlighted in the drop down list. Here is the code for the same:


function onKeyDown(e)  
{  
    if (!comboBoxObj.getIsDrop())  comboBoxObj.DropDown();  
    var innerSpread = comboBoxObj.getFpSpread();  
    var rowCount = innerSpread.GetRowCount();  
    var columnIndex = comboBoxObj.getEditColumnIndex();  
    var activeRow = innerSpread.ActiveRow || innerSpread.GetActiveRow();  

    if (e.keyCode == 13)  
    {  
        this.value = innerSpread.GetValue(activeRow, columnIndex);  
        return;  
    }  
    else if (e.keyCode == 8)  
    {  
        var str = this.value;  
        str = str.substring(0, str.length-1);  
        this.value = str;  
        var text = this.value;  
        for (var i = 0; i < rowCount; i++)  
        {  
            var value = innerSpread.GetValue(i, columnIndex);  
            if (value == text || (value.length > text.length && value.substr(0, text.length) == text))  
            {  
                innerSpread.ClearSelection();  
                innerSpread.AddSelection(i, -1, 1, -1);  
                innerSpread.SetActiveCell(i, 0);  
                this.value = text;  
                break;  
            }  
        }  
     }  
     else  
     {  
        this.value = this.value + e.char;  
        var text = this.value;  
        for (var i = 0; i < rowCount; i++)  
        {  
            var value = innerSpread.GetValue(i, columnIndex);  
            if (value == text || (value.length > text.length && value.substr(0, text.length) ==  text))  
           {  
               innerSpread.ClearSelection();  
               innerSpread.AddSelection(i, -1, 1, -1);  
               innerSpread.SetActiveCell(i, 0);  
               this.value = text;  
               break;  
           }  
       }  
    }  
}  

Refer to the attached samples for complete implementation. In these samples, the AutoSearch feature has been implemented in the first cell of the Spread i.e. Cell(0,0) which can be extended to other cells as per your requirement. Note: This workaround works only in IE9. SearchMultiColumnC# SearchMultiColumnVb

MESCIUS inc.

comments powered by Disqus