Return a list of data from Excel

Question

I have a data in Excel and I have written a method to return the data but I don't know how to return them in a list. Here is my method :

 public List<MyJoin> Excel(string sort, string sortDir, int rowsPerPage, int page, out int count, out string sortCriteria) {

        count = 0;
        sortCriteria = "";
        var book = new ExcelQueryFactory("/App_Data/Northwind.xsl");
        var result = from x in book.Worksheet("Orders")
                     select new
                     {
                         OrderID = x["OrderID"],
                         OrderDate = x["OrderDate"],
                         ShipCountry = x["ShipCountry"],
                         CompanyName = x["CustomerID"],
                         ContactName = x["CustomerID"],
                         EmployeeName = x["EmployeeID"],
                     };

       var result2 = result.ToList() ;

        return result2;

        //return new List<MyJoin>();  
    }

And here are my classes :

public class MyJoin {
    public int OrderID { get; set; }
    public DateTime OrderDate { get; set; }
    public string ShipCountry { get; set; }
    public string CompanyName { get; set; }
    public string ContactName { get; set; }
    public string EmployeeName { get; set; }
}

result2 cannot return LINQ and I don't know how to fix it.

Is this a typo? You should use `select new MyJoin` instead of `select new`. — Dennis, Oct 16 '15 at 06:29

score 2 · Accepted Answer · answered Oct 16 '15 at 07:12

Row[columnName] returns Cell object. And you cannot assign cell object to integer/string/DateTime fields. You just need to use Cast<T> method of Cell with appropriate type parameters:

var result = from r in book.Worksheet("Orders")
             select new MyJoin
             {
                 OrderID = r["OrderID"].Cast<int>(),
                 OrderDate = r["OrderDate"].Cast<DateTime>(),
                 ShipCountry = r["ShipCountry"].Cast<string>(),
                 CompanyName = r["CustomerID"].Cast<string>(),
                 ContactName = r["CustomerID"].Cast<string>(),
                 EmployeeName = r["EmployeeID"].Cast<string>()
             };

Rahul Singh · Answer 2 · 2015-10-16T07:10:09.143

1

Based on your comment, You can directly map the columns to the properties of the class (provided they both have the same name), like this:-

 List<MyJoin> result = (from b in book.Worksheet<MyJoin>("Orders")
                       select b).ToList();

They way you are doing it currently i.e. x["OrderID"] is required if you don't have a class to map. x["OrderID"] returns Linq-to-Excel rowobject which you will have to cast like this:-

x["OrderID"].Cast<int>

edited Oct 16 '15 at 07:10

answered Oct 16 '15 at 06:28

Rahul Singh

21,585
6
41
56

I have the same...It is a typo up – Beslinda N. Oct 16 '15 at 06:39
It does not accept OrderID = x["OrderID"],OrderDate = x["OrderDate"] since it cannot convert type Linqtoexcel to int – Beslinda N. Oct 16 '15 at 06:41
@BesaNeziri - Please check my edit you can directly map the excel columns to your class. Use just the first query. – Rahul Singh Oct 16 '15 at 07:10

Return a list of data from Excel

2 Answers2