Articles in this section
Category / Section

How to customize the Spreadsheet cell to show autocomplete suggestion list

2 mins read

Description

This knowledge base explains the way to show autocomplete suggestion list while editing in Spreadsheet.

Solution

 It can be achieved by using “keyUp”, “loadComplete” and “cellSave” client-side events.

 

HTML

 

 
<div id="Spreadsheet"></div>
 

 

JS

 

 
$("#Spreadsheet").ejSpreadsheet({
    // window.defaultData from http://js.syncfusion.com/demos/web/scripts/xljsondata.min.js file
    sheets: [{
        dataSource: window.defaultData
    }],
    loadComplete: "loadComplete",
    keyUp: "cellEditing",
    cellSave: "cellSave"
}); 
 

 

Razor

 

 
@(Html.EJ().Spreadsheet<object>("Spreadsheet")
    .Sheets(sheets =>
    {
        sheets.Datasource((IEnumerable)ViewBag.DataSource).Add();
    })
    .ClientSideEvents(events => events.LoadComplete("loadComplete").KeyUp("cellEditing").CellSave("cellSave"))
)
 

 

ASPX

 

 
<ej:spreadsheet id="Spreadsheet" runat="server">
    <Sheets>
        <ej:Sheet></ej:Sheet>
    </Sheets>
    <ClientSideEvents LoadComplete="loadComplete" KeyUp="cellEditing" CellSave="cellSave" />
</ej:spreadsheet>
 

 

 
var spreadId = "Spreadsheet";
 
function loadComplete(args) {
    var xlFormat = this.XLFormat, $acElem;
    $acElem = $('<input>').attr({ id: spreadId + "_autocomplete", display: 'none' });
    $acElem.addClass("e-hide");
    this.element.append($acElem);
    renderAutoComplete(this, $acElem);
    aComplete = $("#" + spreadId + "_autocomplete").data("ejAutocomplete");
}
 
function cellEditing(args) {
    var e = args.event, editElem = this.element.find("#" + spreadId + "_Edit")[0], acElem;
    if (e.keyCode == 13 && aComplete.showSuggestionBox) {
        this.element.find("#" + spreadId + "_Edit").text(aComplete.suggestionList.find("li.e-hover").text());
        aComplete.suggestionList.hide();
    }
    if (args.isEdit && !editElem.innerHTML.startsWith("=") && e.keyCode != 13) {
        if (e.keyCode == 38 || e.keyCode == 40) {
            if (aComplete.showSuggestionBox && aComplete.suggestionList) {
                SuggestionSelect(aComplete, e); // SuggestionSelect() from Automcomplete_Plugin.min.js
                return;
            }
        }
        acElem = aComplete.element;
        acElem.val(editElem.innerHTML);
        acElem.data("ejAutocomplete").search();
        editCell = this.getActiveCellElem();
        locate = editCell[0].getBoundingClientRect();
        leftVal = locate.left + window.pageXOffset;
        topVal = locate.top + window.pageYOffset + $(editElem).height();
        $("#" + spreadId + "_autocomplete_suggestion").css({ left: leftVal, top: topVal });
    }
}
 
function renderAutoComplete(xlObj, elem) {
    elem.ejAutocomplete({
        dataSource: ["Option 1", "Option 2", "Option 3"],
        width: "100%",
        delaySuggestionTimeout: 10,
        minCharacter: 2,
        popupWidth: "150px",
        popupHeight: "150px",
        showEmptyResultText: false,
        select: $.proxy(selectText, xlObj)
    });
}
 
function selectText(args) {
    this.element.find("#" + spreadId + "_Edit").text(args.value);
}
 
function cellSave(args) {
    if (aComplete.suggestionList.is(":visible"))
        args.cancel = true;
}
 

 

Refer the Autocomplete_Plugin.min.js script file from the below link to achieve the above requirement,

 

Autocomplete Plugin

 

You can refer the below UG Documentation,

 

https://help.syncfusion.com/api/js/ejspreadsheet#events:loadcomplete

https://help.syncfusion.com/api/js/ejspreadsheet#events:keyup

https://help.syncfusion.com/api/js/ejspreadsheet#events:cellsave

 

spreadsheet autocomplete sample preview

Did you find this information helpful?
Yes
No
Help us improve this page
Please provide feedback or comments
Comments (0)
Please sign in to leave a comment
Access denied
Access denied