November, 2008

  • Eric White's Blog

    Inner Join of Two Excel Tables

    • 2 Comments

    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.

Page 2 of 10 (10 items) 12345»
Page 1 of 1 (10 items)