using System;
using System.Collections.Generic;
using System.Linq;
using System.Xml.Linq;
using Microsoft.Examples.LtxOpenXml;
using DocumentFormat.OpenXml.Packaging;

class VehicleRecord
{
    public string Vehicle { get; set; }
    public string Color { get; set; }
    public int Year { get; set; }
    public int HorsePower { get; set; }
    public decimal Cost { get; set; }
    public DateTime AcquisitionDate { get; set; }
    public bool ExecutiveUseOnly { get; set; }
}

class Program
{
    static void IterateRowsAndCells(string filename)
    {
        Console.WriteLine("Contents of Spreadsheet");
        Console.WriteLine("=======================");
        using (SpreadsheetDocument doc =
            SpreadsheetDocument.Open(filename, false))
        {
            WorksheetPart worksheet =
                (WorksheetPart)doc.WorkbookPart.GetPartById("rId1");

            foreach (var row in worksheet.Rows())
            {
                Console.WriteLine("  RowId:{0}", row.RowId);
                Console.WriteLine("  Spans:{0}", row.Spans);
                foreach (var cell in row.Cells().Where(c => c.ColumnId == "B"))
                {
                    Console.WriteLine("    Column:{0}", cell.Column);
                    Console.WriteLine("      ColumnId:{0}", cell.ColumnId);
                    if (cell.Type != null)
                        Console.WriteLine("      Type:{0}", cell.Type);
                    if (cell.Value != null)
                        Console.WriteLine("      Value:{0}", cell.Value);
                    if (cell.Formula != null)
                        Console.WriteLine("      Formula:>{0}<", cell.Formula);
                    if (cell.SharedString != null)
                        Console.WriteLine("      SharedString:>{0}<", cell.SharedString);
                }
            }
            Console.WriteLine();
        }
    }

    public static void IterateParagraphs(string filename)
    {
        Console.WriteLine("Printing Paragraphs");
        Console.WriteLine("===================");

        using (WordprocessingDocument doc =
            WordprocessingDocument.Open(filename, false))
        {
            foreach (var p in doc.MainDocumentPart.Paragraphs())
            {
                Console.WriteLine("Style: {0}   Text: >{1}<",
                    p.StyleName.PadRight(16), p.Text);
                foreach (var c in p.Comments())
                    Console.WriteLine("  Comment Author:{0}  Text:>{1}<", c.Author, c.Text);
            }
        }
        Console.WriteLine();
    }

    public static void PrintVehicles(IEnumerable<VehicleRecord> list)
    {
        int[] tabs = new[] { 12, 10, 6, 6, 10, 14, 10 };
        foreach (var z in list)
            Console.WriteLine("{0}{1}{2}{3}{4}{5}{6}",
                z.Vehicle.PadRight(tabs[0]),
                z.Color.PadRight(tabs[1]),
                z.Year.ToString().PadRight(tabs[2]),
                z.HorsePower.ToString().PadRight(tabs[3]),
                z.Cost.ToString().PadRight(tabs[4]),
                ((DateTime)z.AcquisitionDate).ToShortDateString()
                    .PadRight(tabs[5]),
                ((bool)z.ExecutiveUseOnly).ToString()
                    .PadRight(tabs[6]));
    }

    public static void QuerySimpleTable(string filename)
    {
        Console.WriteLine("Printing Vehicles");
        Console.WriteLine("=================");

        using (SpreadsheetDocument spreadsheet =
            SpreadsheetDocument.Open(filename, false))
        {
            // list all of the columns in the Vehicles table
            Console.WriteLine("Table: Vehicles");
            foreach (var c in spreadsheet.Table("Vehicles").TableColumns())
                Console.WriteLine("  {0}", c.Name);
            Console.WriteLine();

            // list all vehicles
            var q = from c in spreadsheet.Table("Vehicles").TableRows()
                    select new VehicleRecord()
                    {
                        Vehicle = (string)c["Vehicle"],
                        Color = (string)c["Color"],
                        Year = (int)c["Year"],
                        HorsePower = (int)c["HorsePower"],
                        Cost = (decimal)c["Cost"],
                        AcquisitionDate = (DateTime)c["AcquisitionDate"],
                        ExecutiveUseOnly = (bool)c["ExecutiveUseOnly"]
                    };

            Console.WriteLine("List of all vehicles");
            PrintVehicles(q);
            Console.WriteLine();

            // list all executive vehicles
            q = from c in spreadsheet.Table("Vehicles").TableRows()
                    where (bool)c["ExecutiveUseOnly"] == true
                    select new VehicleRecord()
                    {
                        Vehicle = (string)c["Vehicle"],
                        Color = (string)c["Color"],
                        Year = (int)c["Year"],
                        HorsePower = (int)c["HorsePower"],
                        Cost = (decimal)c["Cost"],
                        AcquisitionDate = (DateTime)c["AcquisitionDate"],
                        ExecutiveUseOnly = (bool)c["ExecutiveUseOnly"]
                    };

            Console.WriteLine("List of all executive vehicles");
            PrintVehicles(q);
            Console.WriteLine();

            // list all vehicles acquired after 2004
            q = from c in spreadsheet.Table("Vehicles").TableRows()
                where (DateTime)c["AcquisitionDate"] >= new DateTime(2004, 1, 1)
                select new VehicleRecord()
                {
                    Vehicle = (string)c["Vehicle"],
                    Color = (string)c["Color"],
                    Year = (int)c["Year"],
                    HorsePower = (int)c["HorsePower"],
                    Cost = (decimal)c["Cost"],
                    AcquisitionDate = (DateTime)c["AcquisitionDate"],
                    ExecutiveUseOnly = (bool)c["ExecutiveUseOnly"]
                };

            Console.WriteLine("List of all vehicles acquired after 2004");
            PrintVehicles(q);
            Console.WriteLine();

            // vehicles grouped by user
            var groups = from v in spreadsheet.Table("Vehicles").TableRows()
                         group v by v["ExecutiveUseOnly"];

            foreach (var g in groups)
            {
                Console.WriteLine("Executive Use: {0}", (bool)g.Key);
                foreach (var v in g)
                    Console.WriteLine("  Vehicle:{0}  Year:{1}",
                        v["Vehicle"], v["Year"]);
                Console.WriteLine();
            }
            Console.WriteLine();
        }
    }

    public static void QueryNorthwind(string filename)
    {
        using (SpreadsheetDocument spreadsheet =
            SpreadsheetDocument.Open(filename, false))
        {
            Console.WriteLine("Printing Northwind Tables");
            Console.WriteLine("=========================");

            // list all of the columns in the Customer table
            Console.WriteLine("Table: Customer");
            foreach (var c in spreadsheet.Table("Customer").TableColumns())
                Console.WriteLine("  {0}", c.Name);
            Console.WriteLine();

            // list all of the columns in the Order table
            Console.WriteLine("Table: Order");
            foreach (var o in spreadsheet.Table("Order").TableColumns())
                Console.WriteLine("  {0}", o.Name);
            Console.WriteLine();

            // query for all customers with city == London,
            // then select all orders for that customer
            var q = from c in spreadsheet.Table("Customer").TableRows()
                    where (string)c["City"] == "London"
                    select new
                    {
                        CustomerID = c["CustomerID"],
                        CompanyName = c["CompanyName"],
                        ContactName = c["ContactName"],
                        Orders = from o in spreadsheet.Table("Order").TableRows()
                                 where (string)o["CustomerID"] ==
                                       (string)c["CustomerID"]
                                 select new
                                     {
                                         CustomerID = o["CustomerID"],
                                         OrderID = o["OrderID"]
                                     }
                    };

            // print the results of the query
            int[] tabs = new[] { 20, 25, 30 };
            Console.WriteLine("{0}{1}{2}",
                "CustomerID".PadRight(tabs[0]),
                "CompanyName".PadRight(tabs[1]),
                "ContactName".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.CustomerID.Value.PadRight(tabs[0]),
                    v.CompanyName.Value.PadRight(tabs[1]),
                    v.ContactName.Value.PadRight(tabs[2]));
                foreach (var v2 in v.Orders)
                    Console.WriteLine("  CustomerID:{0}  OrderID:{1}",
                        v2.CustomerID, v2.OrderID);
                Console.WriteLine();
            }
        }
    }

    public static void QueryNorthwind2(string filename)
    {
        using (SpreadsheetDocument spreadsheet =
            SpreadsheetDocument.Open(filename, false))
        {
            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"]
                    };

            // 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]));
            }
        }
    }

    public static void QueryInventory(string filename)
    {
        using (SpreadsheetDocument spreadsheet = SpreadsheetDocument.Open(filename, false))
        {
            Console.WriteLine("Printing Inventory");
            Console.WriteLine("==================");

            // list all of the columns in the Inventory table
            Console.WriteLine("Table: Inventory");
            foreach (var c in spreadsheet.Table("Inventory").TableColumns())
                Console.WriteLine("  {0}", c.Name);
            Console.WriteLine();

            int[] tabs = new[] { 10, 6, 10, 12 };
            Console.WriteLine("{0}{1}{2}{3}",
                "Item".PadRight(tabs[0]),
                "Qty".PadRight(tabs[1]),
                "Price".PadRight(tabs[2]),
                "Extension".PadRight(tabs[3]));

            var query =
                from i in spreadsheet.Table("Inventory").TableRows()
                where (int)i["Qty"] > 2
                select i;

            foreach (var r in query)
            {
                Console.WriteLine("{0}{1}{2}{3}",
                    r["Item"].Value.PadRight(tabs[0]),
                    r["Qty"].Value.PadRight(tabs[1]),
                    r["Price"].Value.PadRight(tabs[2]),
                    r["Extension"].Value.PadRight(tabs[3])
                );
            }
        }
    }

    public static void Main(string[] args)
    {
        IterateParagraphs("Test.docx");
        IterateRowsAndCells("Book1.xlsx");
        QuerySimpleTable("Table.xlsx");
        QueryNorthwind("Northwind.xlsx");
        QueryNorthwind2("Northwind.xlsx");
        QueryInventory("Inventory.xlsx");
    }
}