Autocomplete in validation list in spreadsheet

[Dawid Szustak]

Is it possible to have automatic completion in the spreadsheet? I would like the list to narrow down automatically as I enter values into a field that has a list of available options.

[Babu Periyasamy]

Hi Dawid Szustak,

We have checked your reported query. And we suspect that, you want to show the available values in a dropdown in the cell. And it can be achieved by using the list validation support in our Spreadsheet component. And another way is to render the autoComplete component in our Spreadsheet cell using the template support of Spreadsheet. Below mentioned the clear details of the above mentioned,

List validation in Spreadsheet:

In our Spreadsheet component, we have list type datavalidation support in which we can show the values given in the list validation as a dropdown in the Spreadsheet cell. For your convenience, we have prepared a sample in which we have applied the list validation using the addDataValidation method in the created event of our Spreadsheet component and attached below for your reference,

Code snippet:

created() {

        // Add a cell range as a data source to the list validation dropdown.

        this.spreadsheetObj.addDataValidation({ type: 'List', inCellDropDown: true, value1: '=Sheet2!A2:A10'}, 'A2:A10');

    }

Stackblitz sample: https://stackblitz.com/edit/angular-qrnek4-kkxgtf?file=src%2Fapp.component.ts

In the above sample, we have render 2 sheets initially with first sheet as empty and second sheet with data. And then, we have applied the list validation in Sheet1 by giving the values of list validation from Sheet2.

For more information regarding this, please refer the below documentation,

Documentation link: https://ej2.syncfusion.com/angular/documentation/spreadsheet/cell-range#data-validation

And also, attached the video demonstration for the above mentioned information,

Video link: https://www.syncfusion.com/downloads/support/directtrac/general/ze/List_validation344052459

Kindly, check the above code snippet, video and sample in your end and get back to us for further validation.

AutoComplete component inside Spreadsheet cell:

In our Spreadsheet component, we have template support in which we can render the other components inside the Spreadsheet cell. For your convenience, we have prepared the sample in which we have rendered the Autocomplete component inside the Spreadsheet cell using the ng-template tag and attached the sample below for your reference,

Code snippet:

app.component.html:

<ejs-spreadsheet #default (created)="created()">

        <e-sheets>

          <e-sheet name="Car Sales Report">

            <e-ranges>

              <e-range [dataSource]="data"></e-range>

              <e-range address="A2:A6">

                <ng-template #template>

                  <ejs-autocomplete

                    #remote

                    [dataSource]="autoCompleteData"

                    filterType="StartsWith"

                    [fields]="remoteFields"

                    [query]="query"

                    [placeholder]="remoteWaterMark"

                    sortOrder="Ascending"

                    (change)="onChange($event)"

                  ></ejs-autocomplete>

                </ng-template>

              </e-range>

            </e-ranges>

          </e-sheet>

        </e-sheets>

      </ejs-spreadsheet>

app.component.ts:

public autoCompleteData: DataManager = new DataManager({

      url: 'https://services.syncfusion.com/angular/production/api/Employees',

      adaptor: new WebApiAdaptor(),

      crossDomain: true,

    });

    public query: Query = new Query()

      .select(['FirstName', 'EmployeeID'])

      .take(10)

      .requiresCount();

    // Map the remote data column to fields property.

    public remoteFields: Object = { value: 'FirstName' };

    // Set the placeholder to AutoComplete input.

    public remoteWaterMark: string = 'e.g. Andrew Fuller';

    created() {

      this.spreadsheetObj.cellFormat(

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

        'A1:F1'

      );

      // Set the row height to the cells.

      this.spreadsheetObj.setRowsHeight(38, ['2:6']);

    }

 

    // Bind the change event.

    onChange(args: ChangeEventArgs): void {

      let value: string = args.value.toString();

      // Get the active sheet model.

      let activeSheet: SheetModel = this.spreadsheetObj.getActiveSheet();

      let activeCell: string = activeSheet.activeCell;

      // Get the cell indexes.

      let range: number[] = getRangeIndexes(activeCell);

      // Update the cell model.

      setCell(range[0], range[1], activeSheet, { value: value }, true);

    }

Stackblitz sample: https://stackblitz.com/edit/angular-mfdw9t-nkesc5?file=src%2Fapp.component.ts

For more information regarding this, please refer the below documentation,

Documentation link: https://ej2.syncfusion.com/angular/documentation/spreadsheet/template

KB link: https://support.syncfusion.com/kb/article/14818/how-to-bind-remote-data-for-autocomplete-rendered-cells-in-an-angular-spreadsheet

And also, attached the video demonstration for the above mentioned information,

Video link: https://www.syncfusion.com/downloads/support/directtrac/general/ze/Autocomplete-613593973

Kindly, check the above code snippet, sample, video, and information in your end and get back to us for further validation.

[Dawid Szustak]

Thank you very much for such a detailed answer. I have one more question, Can I implement AutoComplete component inside Spreadsheet cell, when my html look like this:

<div id="spreadsheet"></div>

All logic is in my component, so i would like to do something like that there. In component i build sheets, rows and columns and inject it to spreadsheet. Can I do template for AutoComplete in my component, or do it in my html without refactor all logic. Is it possible?

[Babu Periyasamy]

Hi Dawid Szustak,

We have checked your reported query. Based on the screenshot provided, we suspect that you are using the Javascript EJ2 components in your application. So, we have prepared a typescript sample in which we have added the input element in the template property of ranges of Spreadsheet along with cell address and rendered the AutoComplete component inside the Spreadsheet cell using the beforeCellRender event of our Spreadsheet component. This event will trigger before the cell is rendered into the Spreadsheet. In the beforeCellRender event, we have added the Autocomplete component by checking the input element present inside the Spreadsheet cell. For your convenience, we have prepared the typescript sample and attached below along with the code snippet for your reference,

Code snippet:

beforeCellRender(args: CellRenderEventArgs) {

            if (args.colIndex != null && args.rowIndex != null && args.colIndex === 0 && args.rowIndex > 0 && args.rowIndex <= 4) {

               //Check the input element to add the Autocomplete component.

              let target: HTMLInputElement = args.element

              .firstElementChild as HTMLInputElement;

              if (target) {

                new AutoComplete(

                  {

                    dataSource: ['Romona Heaslip','Clare Batterton','Eamon Traise', 'Julius Gorner', 'Jenna Schoolfield', 'Marylynne Harring', 'Vilhelmina Leipelt', 'Barby Heisler'],

                    filterType: "StartsWith",

                    placeholder: "Enter the name",

                    sortOrder:"Ascending",

                    value: args.cell ? args.cell.value : '',

                    change: (args: ChangeEventArgs) => {

                        let value: string = args.value ? args.value.toString() : '';

                        // Get the active sheet model.

                        let activeSheet: SheetModel = spreadsheet.getActiveSheet();

                        let activeCell: string = activeSheet.activeCell;

                        // Get the cell indexes.

                        let range: number[] = getRangeIndexes(activeCell);

                        // Update the cell model.

                        setCell(range[0], range[1], activeSheet, { value: value }, true);

                    },

                  },

                  target

                );

              }

            }

Stackblitz sample: https://stackblitz.com/edit/4reeqx-xofy6e?file=index.ts

For more information regarding the cell template in Spreadsheet, please refer the below links,

Documentation link: https://ej2.syncfusion.com/documentation/spreadsheet/template

Demo link: https://ej2.syncfusion.com/demos/#/material3/spreadsheet/cell-template.html

Autocomplete documentation: https://ej2.syncfusion.com/documentation/auto-complete/getting-started

And also, attached the video demonstration below for your reference,

Video link: https://www.syncfusion.com/downloads/support/directtrac/general/ze/Autocomplete_template-1643398073

Kindly, check the above code snippet, sample, video, and information in your end. If you are using the Typescript in your application, you can utilize the above attached sample and code snippet to add Autocomplete components and if you are using Angular in your application, you can utilize the previously shared sample in which we have added the Autocomplete component using the ng-template of Angular which is helpful to add templates properly in Angular application.

If we misunderstood your requirement, please share the below details,

1. Share the clear details of which platform you are using, based on that we will modify the sample.
2. If possible, share the detailed description of your requirement along with screenshot, video, etc.,
3. If possible, please share the code snippets of rendering the Spreadsheet component in your application.

Please share the above requested information from your end. Based on that, we will validate and provide you the better solution quickly.