Auto-complete in spreadsheet Component

[Remy]

Dear Syncfusion,

I am looking for a way to have an "auto-complete" system for specific columns of a spreadsheet based on a predefined list. What I am after looks like this :

I don't want to have to place an "ejs-autocomplete" component in each cell as I would lose some of the spreadsheet's functionalities. Is there a way I can achieve this in any other way?

Thank you,

Remy

[Gayathri KarunaiAnandam]

Hi Remy, 

Thank you for contacting Syncfusion support. 

We have checked your reported query.  We have prepared a Spreadsheet sample with autocomplete system by using html autocomplete as demonstrated in the below code snippet. 

App.component.html 

<ejs-spreadsheet #spreadsheet> 

    <e-sheets> 

      <e-sheet> 

        <e-ranges> 

          <e-range address="C1"> 

            <ng-template #template> 

              <!-- <ejs-textbox placeholder="Name"></ejs-textbox> --> 

              <input #auto type="text" id="text" name="email" autocomplete="on"  list="browsers" /> 

              <datalist id="browsers"> 

                <option value="[email protected]"> 

                <option value="[email protected]"> 

                <option value="[email protected]"> 

                <option value="[email protected]"> 

                <option value="[email protected]"> 

              </datalist> 

            </ng-template> 

          </e-range> 

        </e-ranges> 

        <e-rows> 

          <e-row> 

            <e-cells> 

              <e-cell [index]="1" value="Email:"></e-cell> 

            </e-cells> 

          </e-row> 

        </e-rows> 

      </e-sheet> 

    </e-sheets> 

  </ejs-spreadsheet> 

 

Sample: https://stackblitz.com/edit/angular-f99pma-44sxc8?file=app.component.html 

Please check the above link and get back to us, if you need further assistance. 

Regards, 

Gayathri K 

[Remy]

Hello Gayathri,

On second thought, a dropdown is not ideal of a spreadsheet. What I am after is something like this (typeahead instead of dropdown) which is available in many spreadsheet components :

Is there a way to achieve this natively within the actual contenteditable div ("e-spreadsheet-edit" class) that is used while typing within the spreadsheet component without using other components? My problem with using other components within the spreadsheet is that the cells end up not being treated as spreadsheet cells but rather as inputs/textareas and the spreadsheet looses some functionality.

Regards,

Remy

[Sangeetha Priya Murugan]

Hi Remy, 

 

Thank you for your patience. 

 

We have checked your reported requirement and it is not availabale in our Spreadsheet.  However it can be achievable with some customization in keyup event as like as below. 

 

  created() { 

    this.spreadsheetObj.cellFormat( 

      { fontWeight: "bold", textAlign: "center", verticalAlign: "middle" }, 

      "A1:C1" 

    ); 

    this.spreadsheetObj.element.addEventListener("keyup", event => { 

      var actSheet = this.spreadsheetObj.getActiveSheet(); 

      var actCell = actSheet.activeCell; 

      var rangeIndex = getRangeIndexes(actCell); 

      for (var i = rangeIndex[2]; i > 0; i--) { 

        var cell = getCell(i - 1, rangeIndex[1], actSheet); 

        if ( 

          event.key == "Backspace" || 

          event.key == "Delete" || 

          event.key == "Escape" 

        ) { 

          return; 

        } else if (cell && cell.value) { 

          var elem = document.getElementsByClassName("e-spreadsheet-edit")[0]; 

          var textLen = elem.innerHTML.length; 

          if ( 

            elem.innerHTML.toUpperCase() == 

            cell.value.toUpperCase().slice(0, textLen) 

          ) { 

            elem.innerText = cell.value; 

            var startNode = document.getElementsByClassName( 

              "e-spreadsheet-edit" 

            )[0].firstChild; 

            startNode.nodeValue = startNode.nodeValue.trim(); 

            var range = document.createRange(); 

            range.setStart(startNode, textLen); 

            range.setEnd(startNode, startNode.textContent.length); 

            var sel = window.getSelection(); 

            sel.removeAllRanges(); 

            sel.addRange(range); 

            return; 

          } 

        } else return; 

      } 

    }); 

  } 

 

Sample Link: https://stackblitz.com/edit/angular-tbdlnr-dghfsz?file=app.component.ts  

 

Could you please check the above link and get back to us, if you need any further assistance on this. 

 

Regards, 

Sangeetha M 

[Remy]

Hello Sangeetha,

Thank you for your response. This works only if the user types slowly enough. If the user types too fast, it doesn't work as expected. Is there any way around this?

Regards,

Remy

[Sangeetha Priya Murugan]

Hi Remy, 

 

Thank you for your update. 

 

We have checked your reported issue and we would like to let you know that we have achieved your requirement with some customization in sample level. However, we have considered this as a feature in our EJ2 Spreadsheet and it will be available in any of our upcoming releases. Because, based on the priority we have planned the features for our releases. And, we update our roadmap site once we have planned this feature for our Volume releases.  

 

You can track the status of this feature using below link from our feedback portal,  

 

Feedback Portal Link: https://www.syncfusion.com/feedback/23224/need-to-provide-support-to-show-the-auto-suggested-text-while-editing-cell  

 

Regards, 

Sangeetha M