searchRange.find(...) hangs after lastly found cell

Posted by: anthony.adewumi on 4 April 2020, 2:34 am EST

  • Posted 4 April 2020, 2:34 am EST

    I'm getting an error while looking for all cells with a specific keyword in a sheet. Once the algorithm reaches lastly found cell the process hangs after line number 47.
    range = searchRange.find("GETCELL", range, fo);


    It is precisely shown on the video:
    https://youtu.be/auffeLB_C1U

    Here is a Java method containing the algorithm based on docs published by Grapecity:
    https://www.grapecity.com/documents-api-excel-java/docs/online/FindandReplaceData.html
    	@Override
    public Set<String> extractParams(String json) {
    final Map<String, String> results = new HashMap<String, String>();

    // create a new workbook
    final Workbook workbook = new Workbook(Licenses.GC_DOCUMENTS_LICENSE);
    workbook.fromJson(json);

    for (final IWorksheet worksheet : workbook.getWorksheets()) {

    final FindOptions fo = new FindOptions();
    fo.setLookIn(FindLookIn.Formulas);

    IRange range = null;
    final IRange searchRange = worksheet.getCells();

    do {
    System.out.println("pre find range: " + range);
    range = searchRange.find("GETCELL", range, fo);
    System.out.println("newly found range: " + range);

    if (range != null) {
    results.put(range.toString(), range.getFormula());
    // System.out.println("extractParams() results: " + results);
    }
    } while (range != null);

    System.out.println(searchRange);
    }

    System.out.println("extractParams() results: " + results);
    return null;
    }


    I tested and repeated this behavior with versions 3.0.4 and 3.1.0.
    Please help to resolve this issue.
  • Replied 5 April 2020, 10:42 pm EST

    Hello,

    Unfortunately, I am unable to run your sample because of missing attributes like json. Hence, can you please send us your working sample replicating the issue so that we can investigate this issue further.

    You can send us your sample through SupportOne issue 430017 for security reasons.
    Please send us the same. We will look into this thereon.

    Thanks,
    Reeva
  • Marked as Answer

    Replied 12 April 2020, 8:29 pm EST

    Hello,

    The performance issue is caused by your code.
    final IRange searchRange = worksheet.getCells();
    should be
    final IRange searchRange = worksheet.getUsedRange();


    Hence, you need to change the search range because formulas are always null outside of the used range.

    Please have a look at this documentation link for the same:-
    https://www.grapecity.com/documents-api-excel-java/docs/online/WorkWithUsedRange.html

    Hope it helps.

    Thanks,
    Reeva
Need extra support?

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

Learn More

Forum Channels