Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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)

like image 892
Ankesh Avatar asked Oct 21 '12 08:10

Ankesh


People also ask

Which is better xlsx or xls?

For compatibility, XLS has higher compatibility than XLSX. XLS is readable by all Microsoft Excel versions while XLSX is only readable by Excel 2007 and later versions. besides, XLS is able to hold the spreadsheets either including Macros or not, while XLSX isn't capable to support Macros.

What is the proper app for viewing xlsx files?

Xls viewer can be downloaded on every android device there are no specifications that are necessary, Xlsx File Reader with Xls Viewer can be your default xls reader and directly open the xls file from the file manager, email container or web in this xls viewer without opening the application.


1 Answers

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.

like image 53
Enigmativity Avatar answered Sep 20 '22 08:09

Enigmativity