1. Home
  2. Forum
  3. ASP.NET Core
  4. Embedding a control into a spreadsheet control's cell:

Embedding a control into a spreadsheet control's cell:

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





3 Replies
VK Vinoth Kumar Sundara Moorthy Syncfusion Team 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 


JK Jacob Knight 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?

SI Silambarasan I Syncfusion Team 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 


Loader.
Up arrow icon