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

Question

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 Entities (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)

Answer 1

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.