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,
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