Multiple search results from the Search API?

Posted by: ampires9 on 6 November 2018, 7:09 am EST

    • Post Options:
    • Link

    Posted 6 November 2018, 7:09 am EST

    Hi,

    Is it possible for the search API to return multiple SearchResult instances for every match that’s found across a sheet?

    I’ve had a look through the documentation and the examples demonstrate how to use a SearchCondition to run a search but only for one result: http://help.grapecity.com/spread/SpreadSheets11/webframe.html#SpreadJS~GC.Spread.Sheets.Search.SearchCondition.html.

    Is it possible to have the search API return a SearchResult instance for every match that’s found across the entire sheet?

    Thanks

  • Posted 7 November 2018, 11:50 pm EST

    Hello,

    You can find all instances of searched text by row or column one by one. Please refer to the attached sample application.

    Thanks,

    Deepak SharmabasicSearch.zip

  • Posted 19 February 2019, 5:34 pm EST

    Hi deepak,

    But like in excel how we can retrieve all search instances by row or columns. Not one by one. Imagine if we want to find and replace words in larger file like 10MB’s. How we can do that one by one? . Kindly reply me with the sample code of returning multiple search instances only.

  • Posted 20 February 2019, 7:23 pm EST

    Hi Dinesh,

    In that case, you can traverse through the cells to find the cells with searched text. For example:

    you can use the code the code as follows:

    
      $(document).ready(function () {
                 var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"));
                var activeSheet = spread.getActiveSheet();
                activeSheet.getCell(0, 0).text("testSearch");
                activeSheet.getCell(5, 4).text("testSearch2");
                activeSheet.getCell(5, 2).text("testSearch");
                activeSheet.getCell(8, 4).text("testSearch");
              
                $("#btnSearch").click(function () {
                    var searchString = "testSearch2";
                    spread.suspendPaint();
                    for (var i = 0; i < activeSheet.getRowCount() ; i++) {
                        for (var j = 0; j < activeSheet.getColumnCount() ; j++)
                        {
                            var text = activeSheet.getText(i,j);
                            if (text == searchString) {
                                activeSheet.getCell(i, j).backColor("lightgreen");
                            }
                            else if(text!=searchString )
                            {
                                activeSheet.getCell(i, j).backColor(undefined);
                            }
    
                            }
                          
                       }
                    spread.resumePaint();
         
                });
    
    

    I hope it helps.

    Thanks,

    Deepak Sharma

  • Posted 30 January 2024, 5:14 am EST

    Is this still the best way to search multiple terms in a sheet ?

  • Posted 31 January 2024, 4:29 pm EST

    Hi,

    There is no direct method available for this requirement, but you could enhance the provided solution with optimization. Use the “getUsedRange” method to narrow down the search region. This method provides the data range used in the sheet, reducing the need to traverse through all cells. Refer to the attached snippet and sample.

    const range = activeSheet.getUsedRange(GC.Spread.Sheets.UsedRangeType.data);
            if(!range){
                return;
            }
    
            spread.suspendPaint();
    
            for (var i = range.row; i < range.row + range.rowCount; i++) {
                for (var j = range.col; j < range.col + range.colCount; j++) {
                    const text = activeSheet.getText(i, j);
                    if (text == searchString) {
                        activeSheet.getCell(i, j).backColor("lightgreen");
                    }
                    else if (text != searchString) {
                        activeSheet.getCell(i, j).backColor(undefined);
                    }
    
                }
    
            }
            spread.resumePaint();

    Sample: https://jscodemine.grapecity.com/share/ygKBUMpEsk2ZHFUcvbKyZw/?IsEmbed=false&Theme=Unset&PreviewDirection=0&IsEditorShow=true&IsExplorerShow=true&IsPreviewShow=true&IsConsoleShow=true&IsRunBTNShow=false&IsResetBTNShow=false&IsOpenInCodemineBTNShow=false&PanelWidth=20&PanelWidth=50&PanelWidth=30&defaultOpen={"OpenedFileName"%3A["%2Findex.html"%2C"%2Fsrc%2Fapp.js"%2C"%2Fpackage.json"]%2C"ActiveFile"%3A"%2Fsrc%2Fapp.js"}

    References:

    usedRange: https://developer.mescius.com/spreadjs/api/classes/GC.Spread.Sheets.Worksheet#getusedrange

    Best regards,

    Ankit

Need extra support?

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

Learn More

Forum Channels