We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy. (Last updated on: November 16, 2018).
Unfortunately, activation email could not send to your email. Please try again.
Syncfusion Feedback

Embedding a control into a spreadsheet control's cell:

Thread ID:

Created:

Updated:

Platform:

Replies:

141777 Jan 3,2019 07:22 PM UTC Jan 9,2019 09:35 AM UTC ASP.NET Core 3
loading
Tags: Spreadsheet
Jacob Knight
Asked On January 5, 2019 03:40 AM UTC

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 [Syncfusion]
Replied On January 5, 2019 11:54 AM UTC

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. 
 
 
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; } 
        //.. 
    } 
} 
 
 
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
Replied On January 8, 2019 02:34 PM UTC

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 [Syncfusion]
Replied On January 9, 2019 09:35 AM UTC

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: 
 
Could you please check the above sample and get back to us if you need further assistance? 
 
Regards, 
Silambarasan 


CONFIRMATION

This post will be permanently deleted. Are you sure you want to continue?

Sorry, An error occured while processing your request. Please try again later.

Please sign in to access our forum

This page will automatically be redirected to the sign-in page in 10 seconds.

Warning Icon You are using an outdated version of Internet Explorer that may not display all features of this and other websites. Upgrade to Internet Explorer 8 or newer for a better experience.Close Icon

;