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.
