Inner Join of Two Excel Tables

 

Inner Join of Two Excel Tables

I recently posted some code that allows you to use LINQ to query Excel tables. The source for these queries is the Open XML document – you don’t need to involve the Excel application to query the data in these tables. In that post, I presented a few examples of queries of various types. This post shows a join of two tables using the extension methods and classes presented in that post.

The source for this query is a spreadsheet where I imported two columns of the Northwind data into two worksheets.

var q = from c in spreadsheet.Table("Customer").TableRows()

join o in spreadsheet.Table("Order").TableRows() on

(string)c["CustomerID"] equals (string)o["CustomerID"]

select new

{

OrderID = o["OrderID"],

CustomerID = o["CustomerID"],

CompanyName = c["CompanyName"]

};

Here is a bit of code to print the results of the query:

// print the results of the query

int[] tabs = new[] { 8, 11, 35 };

Console.WriteLine("{0}{1}{2}",

"OrderID".PadRight(tabs[0]),

"CustomerID".PadRight(tabs[1]),

"CompanyName".PadRight(tabs[2]));

Console.WriteLine("{0} {1} {2} ", new string('-', tabs[0] - 1),

new string('-', tabs[1] - 1), new string('-', tabs[2] - 1));

foreach (var v in q)

{

Console.WriteLine("{0}{1}{2}",

v.OrderID.Value.PadRight(tabs[0]),

v.CustomerID.Value.PadRight(tabs[1]),

v.CompanyName.Value.PadRight(tabs[2]));

}

When you run this code, you see:

OrderID CustomerID CompanyName

------- ---------- ----------------------------------

10643 ALFKI Alfreds Futterkiste

10692 ALFKI Alfreds Futterkiste

10702 ALFKI Alfreds Futterkiste

10835 ALFKI Alfreds Futterkiste

10952 ALFKI Alfreds Futterkiste

11011 ALFKI Alfreds Futterkiste

10308 ANATR Ana Trujillo Emparedados y helados

10625 ANATR Ana Trujillo Emparedados y helados

10759 ANATR Ana Trujillo Emparedados y helados

10926 ANATR Ana Trujillo Emparedados y helados

10365 ANTON Antonio Moreno TaquerA-a

10507 ANTON Antonio Moreno TaquerA-a

10535 ANTON Antonio Moreno TaquerA-a

10573 ANTON Antonio Moreno TaquerA-a

10677 ANTON Antonio Moreno TaquerA-a

...

The query is attached to this post.  See the original post for the classes and extension methods that enable querying Excel tables in this way.  I've updated the code in the original post to include this query.

Posted: Thursday, November 20, 2008 2:14 PM by EricWhite

Attachment(s): Program.cs

Eric White's Blog : Inner Join of Two Excel Tables