Search value into excel document

Hi
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
begin
    For 1 to 20000 do
    begin
         Search inside memo2.lines if text exists....
    end
end
~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
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.
CodedK
LVL 16
CodedKAsked:
Who is Participating?
 
developmentguruPresidentCommented:
 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.
0
 
developmentguruPresidentCommented:
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.
0
 
CodedKAuthor Commented:
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...
0
 
CodedKAuthor Commented:
Thank you Developmentguru, i'll award you the points as soon as i test it. :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.