Inner Join of Two Excel Tables
[Blog Map]
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.
