This project has moved. For the latest updates, please go here.

Proposed Feature: Read data into list of objects

Oct 9, 2013 at 5:42 PM
One of the greatest features of ClosedXML is that we can take an IEnumerable list of objects and insert them into an Excel worksheet and ClosedXML will use reflection to figure out the properties and use those as the column names. So that with one method call an entire set of data can be added to the Excel sheet like this example from the documentation:
ws.Cell(7, 6).InsertTable(people.AsEnumerable())
that adds a set of people objects to the worksheet beginning at Cell(7,6).

For quite some time, I have been wishing for a function that would do the reverse. Starting with an Excel sheet that has data in it with column headings, and read that data into objects that have properties corresponding to the column headings. The code might look like this:
ws.CellsUsed().ReadData<people>();
Which would return an enumerable set of people objects filled with the data from the Excel sheet.

I know that in this discussion https://closedxml.codeplex.com/discussions/251095 you mention that you were thinking about implementing something like
range.CopyTo(DataTable dt)
That would definitely be a great addition to the library and would move us in the right direction of making reading Excel files almost as easy as creating them.

If you want to see some proof of concept, there is a great little project on GitHub called LINQ To Excel [https://github.com/paulyoder/LinqToExcel(https://github.com/paulyoder/LinqToExcel)

It lets you write code like this,
var companies = from c in excel.Worksheet<Company>()
                       select c;
that populates a set of Company objects from the Excel worksheet where the column names match the properties in the Company object.

Unfortunately, that project is built on top of the ACE and Jet Excel data readers with all their limitations, and nowhere near as useful as ClosedXML. But it certainly proves that it is possible to write generic code that could map Excel data into arbitrary objects.

Thanks. What do you think?