Optimal way to Read an Excel file (.xls/.xlsx)

c#, excel, excel-interop, oledb, openxml-sdk

Solution

Take a look at Linq-to-Excel. It's pretty neat.

var book = new LinqToExcel.ExcelQueryFactory(@"File.xlsx");

var query =
    from row in book.Worksheet("Stock Entry")
    let item = new
    {
        Code = row["Code"].Cast<string>(),
        Supplier = row["Supplier"].Cast<string>(),
        Ref = row["Ref"].Cast<string>(),
    }
    where item.Supplier == "Walmart"
    select item;

It also allows for strongly-typed row access too.

Problem

I know that there are different ways to read an Excel file: - `Iterop` - `Oledb` - `Open Xml SDK` Compatibility is not a question because the program will be executed in a controlled environment. My Requirement : Read a file to a `DataTable` / `CUstom Entitie`s (I don't know how to make dynamic properties/fields to an object[column names will be variating in an Excel file]) Use `DataTable/Custom Entities` to perform some operations using its data. Update `DataTable` with the results of the operations Write it back to `excel file`. Which would be simpler. Also if possible advice me on custom Entities (adding properties/fields to an object dynamically)

Original source

Related problems