How To: LINQ to Excel

I have 2 excel files with File1.Col2 = File2.Col1.  I want to use LINQ to give me a result of all data from File2 based on File1.  I found this code online and it's using IEnumerable lists, but I know that it isn't efficient.  However, I don't know how to make it better.
 
class ExcelRow
    {
        List<object> columns;

        public ExcelRow()
        {
            columns = new List<object>();
        }

        internal void AddColumn(object value)
        {
            columns.Add(value);
        }

        public object this[int index]
        {
            get { return columns[index]; }
        }

        public string GetString(int index)
        {
            if (columns[index] is DBNull)
            {
                return null;
            }
            return columns[index].ToString();
        }

        public int Count
        {
            get { return this.columns.Count; }
        }
    }

class ExcelProvider : IEnumerable<ExcelRow>
    {
        private string sheet;
        private string filePath;
        private List<ExcelRow> rows;


        public ExcelProvider()
        {
            rows = new List<ExcelRow>();
        }

        public static ExcelProvider Create(string filePath, string sheet)
        {
            ExcelProvider provider = new ExcelProvider();
            provider.sheet = sheet;
            provider.filePath = filePath;
            return provider;
        }

        private void Load()
        {
            string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=    ""Excel 8.0;HDR=YES;""";
            connectionString = string.Format(connectionString, filePath);
            rows.Clear();
            using (OleDbConnection conn = new OleDbConnection(connectionString))
            {
                conn.Open();
                using (OleDbCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "SELECT * FROM [" + sheet + "$] WHERE Name IS NOT NULL ORDER BY Name";
                    using (OleDbDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            ExcelRow newRow = new ExcelRow();
                            for (int count = 0; count < reader.FieldCount; count++)
                            {
                                newRow.AddColumn(reader[count]);
                            }
                            rows.Add(newRow);
                        }
                    }
                }
            }
        }

        public IEnumerator<ExcelRow> GetEnumerator()
        {
            Load();
            return rows.GetEnumerator();
        }

        System.Collections.IEnumerator System.Collections.IEnumerable.GetEnumerator()
        {
            Load();
            return rows.GetEnumerator();
        }

    }

class Program {
  static void Main(string[] args) {
    ExcelProvider datapullList =
            ExcelProvider.Create(@"c:\temp\data.xls", "Sheet1");
    ExcelProvider rbintlList =
            ExcelProvider.Create(@"c:\temp\data2.xls", "Sheet1");

            var myList = from d in datapullList
                         from r in rbintlList
                        where d.GetString(0) == r.GetString(1)
                        select new
                        {
                            Name = d.GetString(0),           
                            Alias = r.GetString(0)
                        };
  }
}

Open in new window


I've considered using DataTables but I'm not sure if it's worth all the effort to set it up.  Besides, I'm not convinced that DataTables are better than IEnumerable lists.  I'd like to hear your feedback on DataTables vs IEnumerable Lists.  Also please help me with my code.

Thank you in advance.
gcastongAsked:
Who is Participating?
 
gcastongConnect With a Mentor Author Commented:
I decided to stick with my reader and see where that takes me.  Unfortunately, I'm stumped at passing that "variable list" to another method.  I'm having trouble with the type casting.  Here's my code:
IEnumerable<ExcelProvider> tempList = setupList(myList, initials);

private static IEnumerable<ExcelProvider> setupList(ExcelProvider myList, string[] Inits)
        {
            var myList1 = from x in myList
                          where myList1.Alias == Inits[0]
                          select x;

            return myList1;
        }

Open in new window


I've been trying to figure this out for a long time now.  Please help!!!
0
 
Miguel OzSoftware EngineerCommented:
I do not think DataTables are better than IEnumerable lists, you still need to fill the datatable and datatable is a more general class(DataTables have more overhead than the code you show here). The reader in your code is better than datatable.
For an example on the extra overhead, check:
http://aspdotnetcode.source-of-humor.com/CodeSnippets/ImportExport/ImportMsExcelWorkSheetsDataIntoDataSetDataTables.aspx
0
 
gcastongAuthor Commented:
Thanks for the response @mas_oz2003.

So, do you believe that my code is efficient?  I feel that it isn't, but I'm just a novice programmer.  If it isn't, how can I make it more efficient, without going through the DataTables route?
0
 
gcastongAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for gcastong's comment http:/Q_27033299.html#35748418

for the following reason:

The solution that I came up was accurate and pretty efficient.
0
 
Miguel OzSoftware EngineerCommented:
Can you post your final solution?
Apologies for delay, being a bit busy lately.
Glad to see that you follow my recommendation to stick to the reader. Regarding efficiency is hard to tell without knowing Excel file contents and size, and keep in mind you can always use Excel interop if you are using this code in non-multithreaded app
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.