Embedding a control into a spreadsheet control's cell:

[Jacob Knight]

Is it possible to embed a SyncFusion text input that utilizes the auto-complete feature like in this demo: https://js.syncfusion.com/demos/web/#!/bootstrap/autocomplete/defaultfunctionalities from an MS SQL database into a single cell of a spreadsheet. I have a requirement where one cell of a spreadsheet needs that type of functionality.

I'm guessing I'd need to add this, too?

$('#Spreadsheet').ejSpreadsheet({   
    allowAutoFill: true
});  

[Vinoth Kumar Sundara Moorthy]

Hi Jacob, 

 

Thank you for contacting Syncfusion support. 

 

We have checked your requirement “To show autocomplete suggestion list in Spreadsheet cell while editing” and It can be achieved by using “keyUp”, “loadComplete” and “cellSave” client-side events with custom script Autocomplete_Plugin.min.js. Please find the below KB link to achieve your requirement. 

 

https://www.syncfusion.com/kb/8433/how-to-customize-the-spreadsheet-cell-to-show-autocomplete-suggestion-list 

 

We have prepared the sample to show autocomplete suggestion list in Spreadsheet cell while editing 

from accessing value from database. Please check the modified code from above KB link, 

 

Spreadsheet.html 

 

//.. 

 

$.ajaxSetup({ async: false }); 

$.getJSON("api/Spreadsheet/GetDataFromDB", {}, function (data) { 

    dataSource = data; 

}); 

$.ajaxSetup({ async: true }); 

 

function renderAutoComplete(xlObj, elem) { 

    elem.ejAutocomplete({ 

        id: 'SpreadCell', 

        dataSource: dataSource, 

        width: "100%", 

        fields: {text:"ItemName"}, /// To specify the field name from database 

        delaySuggestionTimeout: 10, 

        minCharacter: 2, 

        popupWidth: "150px", 

        popupHeight: "150px", 

        showEmptyResultText: false, 

        select: $.proxy(selectText, xlObj) 

    }); 

} 

 

//.. 

 

 

SpreadsheetController.cs 

namespace SpreadsheetWebAPI.Controllers 

{ 

     

    /// </summary> 

    [ServiceContract(Namespace = "")] 

    [XmlSerializerFormat] 

    public class SpreadSheetController : ApiController 

    { 

        private static string connetionString = ConfigurationManager.ConnectionStrings["SpreadDBConnectionString"].ConnectionString; 

 

        [AcceptVerbs("Get")] 

        [OperationContract] 

        [WebGet(BodyStyle = WebMessageBodyStyle.Bare)] 

        public HttpResponseMessage GetDataFromDB() 

        { 

            List<Order> orders = new List<Order>(); 

            SqlConnection sqlCon = new SqlConnection(connetionString); 

            SqlCommand sqlComm = new SqlCommand("SELECT * FROM [dbo].[Table]", sqlCon); 

            sqlCon.Open(); 

            SqlDataReader sqlDR = sqlComm.ExecuteReader(); 

            while (sqlDR.Read()) 

            { 

                Order order = new Order(); 

                order.OrderID = (int)sqlDR.GetValue(0); 

                //.. 

                orders.Add(order); 

            } 

            sqlCon.Close(); 

 

            JavaScriptSerializer serializer = new JavaScriptSerializer(); 

            string str = serializer.Serialize(orders); 

            return new HttpResponseMessage() { Content = new StringContent(str, Encoding.UTF8, "text/plain") }; 

        } 

    } 

 

    public class Order 

    { 

        public int OrderID { get; set; } 

        //.. 

    } 

} 

 

Sample Link: http://www.syncfusion.com/downloads/support/forum/141777/ze/SpreadsheetSample1985883805 

 

Could you please check the above sample and get back to us with more information if you need any further assistance on this? 

 

Regards, 

Vinoth Kumar S 

[Jacob Knight]

This is a great sample. Thanks for providing it. Is there a way to apply this to only one cell instead of it triggering for every cell?

[Silambarasan I]

Hi Jacob, 

 

Thank you for your update. 

 

Yes, we can achieve this requirement “To show autocomplete suggestion list for singe cell” using ‘KeyUp’ client-side event by allowing only for your required cell to show the suggestion list. Please refer the below modified code example. 

 

Spreadsheet.html  

<body>  

    <label>Select AutoComplete Cell</label>  

    <select id="cellName">  

        <option value="A1">A1</option>  

        <option value="A2">A2</option>  

        <option value="A3">A3</option>  

    </select>  

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

</body>  

  

<script type="text/javascript">  

      

    //…  

    function cellEditing(args) {  

        var e = args.event, editElem = this.element.find("#" + spreadId + "_Edit")[0], acElem, actCell = this.getActiveCell(), cellName = $("#cellName").val();  

        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 Autocomplete_Plugin.min.js  

                    return;  

                }  

            }  

            if (this.getAlphaRange(actCell.rowIndex, actCell.colIndex, actCell.rowIndex, actCell.colIndex) == cellName) {  

                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 });  

            }  

                 

        }  

    }  

    //…  

   

</script>  

  

We have modified the provided sample and it can be downloaded from the below link. In that, we have demonstrated to show the AutoComplete suggestion list only for the selected cell value from the DropDownList option.  

 

Modified sample: 

http://www.syncfusion.com/downloads/support/directtrac/141777/ze/SpreadsheetSample1503737061 

 

Could you please check the above sample and get back to us if you need further assistance? 

 

Regards, 

Silambarasan