IXLTable instance casting into System.Generic.List<T>

Jan 26, 2012 at 8:25 AM
Edited Jan 26, 2012 at 1:39 PM


i'm using ClosedXML liabrary for dealing with Excel Sheet. While fetching Table from worksheet i can't be able to cast it in system.generic.list. i'm using a class AQuestion.cs to create Table in Excel Sheet,. And it's Done well no need to define any mapping, using worksheet.InsertTable(List<AQuestion>, "XYZ", true),.


When i get the table from excel using worksheet.Table("XYZ"), i can't be able to cast it in List<AQuestion>, either as a whole Table or as per record. Here is my code,.

 XLWorkbook wb = new XLWorkbook("E:/QuestionExample4.xlsx");
            IXLWorksheet choicesSheet = wb.Worksheet("Choices");
            IXLTable cTable = choicesSheet.Table("Choices");
     	    List<AQuestion> qList = new List<AQuestion>();
	//either whole,
		qList = cTable; //unable to cast
	//or either as 
            IXLTableRows Rows = cTable.Rows(1, cTable.RowCount());
	    int count = Rows.Count();
            AQuestion q= new AQuestion();
            foreach (var r in Rows) {
                 q = r.CastTo<AQuestion>(); //unable to cast

Is there any way to map IXLTable to List<T> directly,,. ? ??
Jan 26, 2012 at 3:32 PM

Nope. You'll have to do something like:

foreach(var row in cTable.Rows())
		new AQuestion {
			Property1 = row.Field("Property1"),
			Property2 = row.Field("Property2"),
			Property3 = row.Field("Property3")


Jan 27, 2012 at 8:06 AM

OK Thanks, Mdeleon

Jan 27, 2012 at 1:52 PM

Interesting question. Manuel is correct that there would be no cast or conversion from an IXLTable to List<AQuestion>. Neither would there be a cast or conversion from IXLRow to the AQuestion object. But that doesn't mean you couldn't create the implicit conversion yourself. Here are some ideas to get you started and a link to a good article about writing your own implicit conversions at the end of this posting.

Have fun.

To start with, you could define an AQuestion constructor that would take an IXLRow as its argument and return a new AQuestion object. Inside your AQuestion class the constructor would look like this:

public AQuestion (IXLRow r)
    Property1 = r.Field("Property1");
    Property2 = r.Field("Property2");
    Property3 = r.Field("Property3");

You could then define a method inside your AQuestion class called ToList that takes an IXLTable object as its argument and looks like this:

public static List<AQuestion> ToList(IXLTable t)
    List<AQuestion> qList = new List<AQuestion>;
    foreach (IXLRow row in t.Rows())
        qList.Add(new AQuestion(row));
    return qList;

Note that ToList() is a static method because it is available in the AQuestion class but doesn't operate on any AQuestion instance object.

Putting all this code inside the AQuestion class is very good Object Oriented programming practice because if you ever change the properties of AQuestion you only have one place to update.

The code in your application area is a simple one line of code:

List<AQuestion> qList = AQuestion.ToList(cTable);

Now if you really want to do this as an implicit conversion and write

 qList = cTable;

you can define your own implicit conversion routine as described in the following link.


Have lots of fun coding!

Jan 30, 2012 at 9:27 AM

Great idea roberttanenbaum. That's really simplify my code.