How to enumerate an excel file using http://code.google.com/p/linqtoexcel/ in c#

Question

Basically what I need is the contents of an excel file (non blank cells) copied in a structure like IEnumerable<IEnumerable<string>> contents;
The first IEnumerable would be for rows and the second one for columns.
What I've already got working is loading the file using:

var excel = new ExcelQueryFactory("file.xls");

Thanks in advance guys.

Answer 1

The answer was:

var excel = new ExcelQueryFactory("file.xls");
var result = excel.WorksheetNoHeader().ToList().Select(x => x.Select(y => y.ToString()).AsEnumerable());

Thanks for the help provided.

Answer 2

you can try something like this you can write the additional logic yourself to pull in field data not = to string.Empty replace the file path with where ever your filepath location is

You can also use the Linq to Excel open source project (code.google.com/.../linqtoexcel) to easily get data from Excel. It takes care of the OleDB connection for you and you can use the where clause. All you have to do is create a class with properties corresponding to the excel column names. Here's an example:

IExcelRepository<Client> repo = new ExcelRepository<Client>(@"C:\file.xls");

var largeClients = from c in repo.Worksheet

                  where c.Employees > 200

                  select c;

foreach (Client client in largeClients)
{
   Console.WriteLine(client.ClientName);
}

Answer 3

Put the data into something like a datatable first and then select from that using the as Enumarable?

var x = _xlData.Tables[0].Rows
           .Cast<DataRow>()
           .Where(row => !row.ItemArray.All(field => field is System.DBNull ||   
                                            string.Compare((field as string).Trim(), 
                                            string.Empty) == 0))
           .AsEnumerable();

Answer 4

According to the documentation, the solution should be as trivial as the following:

var excel = new ExcelQueryFactory("file.xls");
var result = excel.Worksheet()
                  .Select(x => x.Select(y => y.ToString()));