Freigeben über

Inner Join of Two Excel Tables

  • Artikel

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.

This blog is inactive.
New blog: EricWhite.com/blog

Blog TOCThe 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.

Program.cs

Comments

  • Anonymous
    November 24, 2008
    Comme à l'accoutumé, voici une brochette de liens de la semaine sur Open XML. Posts techniques en vrac

  • Anonymous
    October 26, 2013
    Simple Way of Excel Joins Visit My Blog http://exceljoins.blogspot.in/ to see all kinds Joins that can be performed on Excel Data. It has Inner Join, Left Outer Join, Right Outer Join and Full Outer Join Solutions Pros 1) No Formuales writing 2) No Excel Macros preparation 3) Simply Download the Files, Input the Data, Run the Script and Check the Result Cons 1) At present i have limited the Joins only for 2 Tables