Linq To Excel In Action

public class Product 
{   
    public int ProductId 
    {   
        get;   
        set;   
    }   
    public string ProductName 
    {   
        get;   
        set;   
    }   
    public string CategoryName 
    {   
        get;   
        set;   
    }   
}   

string pathToExcelFile = @"D:\ExcelData.xlsx";   
ConnexionExcel ConxObject = new ConnexionExcel(pathToExcelFile);   
// Query a worksheet with a header row   
var query1 = from a in ConxObject.UrlConnexion.Worksheet<Product>()   
             select a;   
foreach(var result in query1) {   
    string products = "ProductId : {0}, ProductName: {1}";   
    Console.WriteLine(string.Format(products, result.ProductId, result.ProductName));   
}   
Console.ReadKey();   

var query2 = from a in ConxObject.UrlConnexion.Worksheet<Product>("Products") // Products worksheet
             select a;
foreach(var result in query2) {
    string products = "ProductId : {0}, ProductName: {1}";
    Console.WriteLine(string.Format(products, result.ProductId, result.ProductName));
}
Console.ReadKey();

Result

Another query, if we want to get products which their productName start with the J letter

Query 3

var query3 = from p in ConxObject.UrlConnexion.Worksheet<Product>("Products")
             where p.ProductName.StartsWith("J")
             select new {
                 p.ProductName
             };
foreach(var result in query3) {
    string products = "ProductName : {0}";
    Console.WriteLine(string.Format(products, result.ProductName));
}

Result

Now, we want to create a query that selects all products which lie either in the category of “Cars” or “Clothing”.

Query 4

var query4 = from p in ConxObject.UrlConnexion.Worksheet<Product>("Products")
             where p.CategoryName.Equals("Cars") || p.CategoryName.Equals("Clothing")
             select new {
                 p.ProductName,
                 p.CategoryName
             };
foreach(var result in query4) {
    string products = "ProductName : {0}, CategoryName : {1}";
    Console.WriteLine(string.Format(products, result.ProductName, result.CategoryName));
}
Console.ReadKey();

Result

Property to column mapping

To map column names of the worksheet, we have two possibilities, either using the AddMapping() method or using the ExcelColumn attribute on the properties.

So, let’s see an example.

Query 5. AddMapping() Method

ConxObject.UrlConnexion.AddMapping<Product>(x => x.CategoryName, "CategoryName");
var query5 = from p in ConxObject.UrlConnexion.Worksheet<Product>("Products")
             where p.CategoryName.Equals("Electronics")
             select p;
foreach(var result in query5) {
    string products = "ProductName : {0}, CategoryName : {1}";
    Console.WriteLine(string.Format(products, result.ProductName, result.CategoryName));
}
Console.ReadKey();

As you can see above, we have mapped the CategoryName property of the product class with the Category Name column of the Products worksheet. The query returns all products that have Electronics as the category name.

ExcelColumn Attribute

We can also decorate the CategoryName property with the ExcelColumn attribute and pass the column name that we want to map as an argument.

[ExcelColumn("CategoryName")]
public string CategoryName {
    get;
    set;
}

Result

Query a worksheet without a header row

A worksheet that does not have a header row can also be queried using the WorksheetNoHeader() method.

Note that the cell values will be referenced by the index.

Query 6

var query6 = from p in ConxObject.UrlConnexion.WorksheetNoHeader()
             where p[2].Equals("Clothing")
             select p[1];
foreach (var result in query6) {
    Console.WriteLine(result);
}
Console.ReadKey();

Result

Query a specific range within a worksheet

Here, we can select data between ranges by using WorksheetRange(startRange, endRange) method

Query 7

var query7 = from c in ConxObject.UrlConnexion.WorksheetRange<Product>("B1", "C8")
             where c.CategoryName == "Clothing"
             select new { c.ProductName, c.CategoryName };
foreach (var result in query7) {
    string products = "ProductName : {0}, CategoryName : {1}";
    Console.WriteLine(string.Format(products, result.ProductName, result.CategoryName));
}
Console.ReadKey();

Result

Query a specific worksheet by index

The worksheets index order is based on their name alphabetically, which means if a spreadsheet contains two worksheets (Product, Category). Even though Category is the second worksheet in Excel, it is considered the first index.

Query 8

var query8 = from c in ConxObject.UrlConnexion.Worksheet<Product>(1)
             where c.ProductId > 5
             select c;
foreach (var result in query8) {
    string products = "Product Id : {0}, Product Name : {1}";
    Console.WriteLine(string.Format(products, result.ProductId, result.ProductName));
}
Console.ReadKey();

Result

Query Worksheet Names

To get all worksheet names in a spreadsheet, we will use the GetWorksheetNames() method

var worksheetNames = ConxObject.UrlConnexion.GetWorksheetNames();
foreach (var result in worksheetNames) {
    Console.WriteLine(result);
}
Console.ReadKey();

Result

Query column names

Select all column names in a worksheet. We will use the GetColumnNames() method.

var columnNames = ConxObject.UrlConnexion.GetColumnNames("Products");  
foreach(var result in columnNames) {  
    Console.WriteLine(result);  
}  

Result

Persistent connection

If you want to use the same connection on all queries performed by the IExcelQueryFactory, then set the UsePersistentConnection property to true.

Make sure to dispose of the ExcelQueryFactory if you use a persistent connection.

Query 9

ConxObject.UrlConnexion.UsePersistentConnection = true;
try {
    var query9 = from c in ConxObject.UrlConnexion.Worksheet<Product>(1)
                 where c.ProductId > 5
                 select c;
} catch (Exception) {
    throw;
} finally {
    ConxObject.UrlConnexion.Dispose();
}

That’s all. Please share your feedback and queries in the comments box.