Tag Archives: linq

Linq over Excel

I was writing a tool and needed to quickly query an excel sheet. I’m not diving into linq but you get general idea of how to query/sort over the datatable and once you have an enumerable object you can pretty easily run a linq query over it.

public static DataTable GetData(string filename){    string fullPath = Path.GetFullPath(filename);    String conn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties="Excel 12.0 Xml;HDR=YES"";    OleDbConnection cn = new OleDbConnection(string.Format(conn, fullPath));    OleDbCommand cmd = new OleDbCommand(@"SELECT * FROM [Sheet1$]", cn);    OleDbDataAdapter da = new OleDbDataAdapter(cmd);    cn.Open();    DataTable dt = new DataTable();    da.Fill(dt);    return dt;}static IEnumerable<T> GetData<T>(DataTable dt) where T : new(){    PropertyInfo[] properties = typeof(T).GetProperties();    foreach (DataRow item in dt.Rows)    {        T o = default(T);        foreach (var property in properties)        {            if (dt.Columns.Contains(property.Name))            {                if (o == null)                {                    o = new T();                }                property.SetValue(o, item[property.Name], null);            }        }        if (o == null)            continue;        yield return o;    }}

Here is an intersting project – http://code.google.com/p/linqtoexcel/

If you want to use an excel project you could do something like this.