Search value into excel document

Posted on 2009-02-23
Medium Priority
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
  • 2
  • 2
LVL 21

Expert Comment

ID: 23716102
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

ID: 23721246
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

developmentguru earned 2000 total points
ID: 23725378
 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

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

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
Creating an auto free TStringList The TStringList is a basic and frequently used object in Delphi. On many occasions, you may want to create a temporary list, process some items in the list and be done with the list. In such cases, you have to…
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.
Suggested Courses
Course of the Month15 days, 18 hours left to enroll

850 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