Blog - Title

Inner Join of Two Excel Tables

Inner Join of Two Excel Tables

  • Comments 2

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 TOC
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.

Attachment: Program.cs
Leave a Comment
  • Please add 6 and 3 and type the answer here:
  • Post
  • Comme à l'accoutumé, voici une brochette de liens de la semaine sur Open XML. Posts techniques en vrac

  • 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

Page 1 of 1 (2 items)