Log in to start a topic

Searchable Drop down/ Combo box list in spreadjs

Posted by: ashok.purty on 6 March 2019, 6:38 am EST

  • ashok.purty

    • Post Options:
    • Link
      Copy

    Posted 6 March 2019, 6:38 am EST

    Hi,

    I am trying to implement the searchable Drop down list/ ComboBox in spreadJS with angular 5 application.

    Drop down list should display the suggested items based on user input in the cell associated with that combo box list. search should perform for each key press.

    So is that any in-build option for searchable drop down list in spread JS ?

    If yes, please shared some code-snippet.

    If searchable option is not available, then can i implement the custom searchable inside the spread-sheet cell ? please guide me if that is possible.

    So my idea is to implement the searchable drop down list inside one particular cell in spread sheet.

    For your information, i am using spreadJS with Angular 5

    Thanks,

    Ashok purty

  • sharad.tomer

    Posted 7 March 2019, 11:48 am EST

    Hi,

    Please refer to the attached sample which demonstrates how we could add an autocomplete in spreadJS cell and let us know if you face any issues.

    Regards

    SpreadJS_AutoCompleteCellType_V11 (1).zip

  • ashok.purty

    Posted 14 March 2019, 12:20 am EST

    Thanks its working :slight_smile:

    Do you have any other sample without using select2() jquery lib to implement autocomplete dropdown list ?

  • sharad.tomer

    Posted 15 March 2019, 8:43 am EST

    Autocomplete’s are complex control and it is generally better to use an existing one rather than creating a new control. If you have any other library that you would like to use, you may do so in the same way as in the sample. Please let us know if you would like us to prepare a sample for you using some specific library or create a basic autocomplete using JS without any dependency.

  • ashok.purty

    Posted 17 March 2019, 1:09 am EST

    No Thanks :slight_smile:

    Its working well.

  • ashok.purty

    Posted 19 March 2019, 1:11 pm EST

    i am not able to get the value set by select2() jquery lib for searchable drodownlist suggested by you

    this is my code

    var value = recordSheet.getCell(rowIndex, colIndex).text().trim();

    its returns value contains [object object]

    can you please help

  • ashok.purty

    Posted 19 March 2019, 1:24 pm EST

    just got answer :slight_smile:

    recordSheet.getCell(rowIndex, colIndex).value();

    thanks

  • ashok.purty

    Posted 24 March 2019, 6:21 am EST

    Hi Team,

    Searchable Drop down list/ ComboBox cell is not working properly if value for that cell is coming from from api.

    i am setting the api data as mention:

    sheetData.setText(rowNumber, columnNumber,comboBoxValue, GC.Spread.Sheets.SheetArea.viewport);

    Is that code correct or do i have to follow different approach to set Drop down list/ ComboBox value from api. ?

  • ashok.purty

    Posted 25 March 2019, 3:40 am EST

    I have identified the changes and applied the solution. No need to answer this query :slight_smile:

    Thanks,

    Ashok Purty

  • ashok.purty

    Posted 30 March 2020, 5:26 am EST

    Hi,

    I am using select2 lib for searchable drop down list as you suggested me for my application.

    Could you please suggest if select2 lib can be localized(en, fi, sv) with customized message when auto-search returns empty result ?

    Thanks,

    Ashok Purty

  • sharad.tomer

    Posted 31 March 2020, 12:58 am EST

    Hi Ashok,

    You may refer to the official docs of select2 for internationalization here:

    https://select2.org/i18n

    Regards

    Sharad

  • ashok.purty

    Posted 31 March 2020, 6:19 am EST

    Thanks sharad.

    I will check if it can be implemented in my project.

    One more query related to the searchable Drop down/ Combo box list.

    As i am using select2 lib for the same. I am getting issue while copy paste operation.

    When user copies any value from searchable combox box list and tries to paste in any cell in the spreadsheet, it paste data as [object Object]. It should not copy the object from the combox list cell , but only text part.

    Do you have some quick fix for this issue.

    Searchable combo box implementation has been referred from the sample which spreadjs team have provided me in this topic.

    https://www.grapecity.com/forums/spread-sheets/searchable-drop-down-combo#hi-please-refer-to-the-att

  • sharad.tomer

    Posted 1 April 2020, 2:56 am EST

    In this case, you may add a getText function on our custom cell autocomplete cell type to properly convert the object to string and then override the GC.Spread.Sheets.Worksheet.prototype.getText method to use the getText method from our custom cell type. Please refer to the following code snippet and the sample demonstrating the same:

    /* properly convert object values to text */
AutoCompleteCellType.prototype.getText = function(value) {
  if (value) {
    return value.id + ": " + value.value;
  }

  return "";
};

/* override worksheet's getText method to use the getText of the custom cell type */
let oldGetTextFn = GC.Spread.Sheets.Worksheet.prototype.getText;
GC.Spread.Sheets.Worksheet.prototype.getText = function(row, col) {
  let cellType = this.getCellType(row, col);
  if (cellType && cellType.getText) {
    return cellType.getText(this.getValue(row, col));
  }

  return oldGetTextFn.call(this, row, col);
};

    https://codesandbox.io/s/spread-js-starter-fr9zl

  • ashok.purty

    Posted 3 April 2020, 3:21 am EST

    Thanks for quick response :slight_smile:

  • ashok.purty

    Posted 7 April 2020, 7:20 am EST - Updated 3 October 2022, 9:28 am EST

    https://www.grapecity.com/forums/spread-sheets/searchable-drop-down-combo#in-this-case-you-may-add-a

    Its working in some case.

    If user copy the autocomplete type cell value from ContextMenu option and paste to any blank cell with ContextMenu-> paste option → values then its renders the [object Object] in the selected cell. Note that cell should be paste directly, not in cell edit mode. It can be reproducible in the sample code which your team has suggested.

    1883×293 51.4 KB


    Is that some other changes need to be handle in this situation ?

    Thanks,

    Ashok Purty

  • sharad.tomer

    Posted 8 April 2020, 3:42 am EST

    Hi Ashok,

    In this case, the observed behavior is expected because the pasted value is actual cell value which in this case is an object, and our custom cell type in not set on the target cell to properly format the object type value.

    To workaround, one possible solution is we override the toString() method of the object value set in the cell like:

    sheet
    .getCell(3, 2, GC.Spread.Sheets.SheetArea.viewport)
    .cellType(new AutoCompleteCellType())
    .value({
      id: 123,
      value: "someval",
      toString: function() {
        return this.id + ": " + this.value;
      }
    });
AutoCompleteCellType.prototype.getEditorValue = function(eContext) {
  var data = $(eContext)
    .find("select")
    .select2("data");
  if (!data || !data.length) {
    return null;
  }
  return Object.assign(
    {
      toString: function() {
        return this.id + ": " + this.value;
      }
    },
    {
      id: data[0].id,
      value: data[0].text
    }
  );
};

    And the underlying value will remain to be the object.

    If it is fine to store only the text value in cells and not the actual underlying object then a better solution would be to store the actual cell as the tags and store only the formatted text part as cell value. Using this approach we won’t have to override the getText() method and text value would also be available in the formulas too.

    Please refer to the following updated sample demonstrating the same:

    https://codesandbox.io/s/spread-js-starter-sztlx

  • ashok.purty

    Posted 9 April 2020, 8:07 am EST

    Thanks Team :slight_smile:

    Its working with my application

  • vivek.mahajan

    Posted 14 October 2020, 2:01 am EST

    Hi Team,

    For the example given above as zip file, i need to change the height of the search box which comes inside the dropdown when we double click on the cell with given cell type in spread.

    Can you please show how can we update the size of search box of select2 control used inside the spread.

  • manish.gupta

    Posted 15 October 2020, 5:47 am EST

    Hi Vivek,

    For increasing the AutoComplete search box height, the height should be set for select2-search__field class.

    Please refer to the following demo sample:

    https://codesandbox.io/s/spread-js-starter-forked-fm4fw?file=/src/styles.css:92-113

    Regards,

    Manish Gupta

  • adeeb.thayyil

    Posted 23 January 2022, 1:07 pm EST

    Can someone please post a angular sample code for this, or help me integrate select2 in angular

    Thanks in advance

  • avinash.pathak

    Posted 23 January 2022, 11:49 pm EST

    Hi,

    Please refer to the attached sample and let me know if you face any issues.

    Regards,

    Avinash

    angular-sample.zip

  • adeeb.thayyil

    Posted 24 January 2022, 12:48 am EST - Updated 3 October 2022, 9:23 am EST

    Thank you, let me check

Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels

  • © 2023 MESCIUS inc. All Rights Reserved.  ·  1.800.858.2739

    All product and company names herein may be trademarks of their respective owners.