Taking content from two different excel sources at the same time with Oledb

My problem is that,

There are two excel files. And i should take the first one's content than do a search operation depending on the first search. I mean,

Assume excel1 has this content,

Column1

mike

john

elvis

and excel 2 has that,

Column1 Column2

mike 1999

elvis 2000

jeremy 1988

john 1957

eric 1944

I want to take the birthdays of names which appears in excel1. So, the result of this search is,

Column1 Column2

mike 1999

elvis 2000

john 1957

Now, the question is coming, how can i take two excel开发者_JAVA百科 files' content at the same time with oledb? I appreciate any help, thank you.

When using OLEDB to access two documents, you will have to create two different connections. This means, you cannot use a single SQL join to get the data from both documents into a combined table. What you can do:

• read the content of doc 1 into some data structure (for example, a List<string>)
• read the content of doc 2 into another data structure (for example, a Dictionary<string,string>)
• loop over all elements of the list and assign each of them the values from the dictionary.

As an alternative, you could use COM interop to copy both sheets into a third excel document and do the SQL query there, but that solution needs Excel to be installed on the production machine, and IMHO it seems not to be worth the effort for the requirements you described.