Search value into excel document

Posted on 2009-02-23
Last Modified: 2013-11-23
I want to search for multiple values inside an Excel document.

I have 2 excel files, i open them in Delphi and then get the values of a specific column.
Then i fill 2 memos.
I need to search if :
The memo.lines[from 1- end]  exists in Memo2...

The problem with this loop is the time.... The memo lines are almost 20000...
So For 1 to 20000 do
    For 1 to 20000 do
         Search inside memo2.lines if text exists....
This is too slow.
I'm thinking if there is a way to search using excel commands inside Delphi programmatically...?
Till now i know how to read the value from a cell but i don't know the command (Excel command) to do a fast search inside the document.

Can you think any other solution ?

Thanks in advance.
Question by:CodedK
    LVL 21

    Expert Comment

    Instead of writing to memos write to TStringList.  Make sure the StringList is sorted.  When you do a find operation on the string list it will do a biinary search which is FAR faster.  In this case you would do something like this.

    for 1 to 20000 do
      if StringList.Find(Value, Index) then

    Let me know if this works, or if you need more.
    LVL 16

    Author Comment

    Thank you very much DevelopmentGuru.
    I had in mind to use StringList and eventually i'll do it. I only use Memo for now for visual feedback of what i'm doing.
    I was just wondering if there was any command when i manipulate the excel doc...
    LVL 21

    Accepted Solution

     I don't think the accessing of the data in the excel documents is your slow down point.  I believe it is the loop itself.  In the original example you gave you had 20,000 loops on the outside loop and 20,000 loops on the inside loop.  This multiplies out so the internal code to both loops is executed 400,000,000 times!  By simply putting the search values into a list that can be searched without looping through every one every search you drop your loop back to 20,000.

      Having said that, Excel is overkill for this purpose.  I say that because the vast majority of capabilities built into excel are wasted on this type of information.  This means that the structure of storing the information is more complex as well, therefore accessing it would also be slower than a more simplified file type.  While I know your users love being able to use excel, you need to decide if it is slowing you down enough to move to something else.

      I do believe you will see all of the speed increases you need from just changing your search method though.  Let me know if you need more.
    LVL 16

    Author Comment

    Thank you Developmentguru, i'll award you the points as soon as i test it. :)

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Update (December 2011): Since this article was published, the things have changed for good for Android native developers. The Sequoyah Project ( automates most of the tasks discussed in this article. You can even fin…
    Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
    This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.
    The viewer will learn how to use and create keystrokes in Netbeans IDE 8.0 for Windows.

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now