Category / Section
How to customize the Spreadsheet cell to show autocomplete suggestion list
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
