LinqToDataTable

在某些需要讲数据导出到Excel中然后下载到本地的过程中，使用的开源类库是NPOI，不要太强大，不需要在服务端安装任何office软件。
遇到的第一个问题便是，如何获得DataTable（鄙人用的是LinqToEF来操作数据库），很是头大，幸好有前人栽树，现给出方法：

/// <summary>
/// 在自己的公共类库中，加入LinqToDataTable
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="varlist"></param>
/// <param name="fn"></param>
/// <returns></returns>
public static DataTable ToDataTable<T>(this IEnumerable<T> varlist, CreateRowDelegate<T> fn)
{
    DataTable dtReturn = new DataTable();
    
    // column names
    PropertyInfo[] oProps = null;

    // Could add a check to verify that there is an element 0
    foreach (T rec in varlist)
    {
        // Use reflection to get property names, to create table, Only first time, others will follow
        if (oProps == null)
        {
            oProps = ((Type)rec.GetType()).GetProperties();
            foreach (PropertyInfo pi in oProps)
            {

                Type colType = pi.PropertyType; 
                if ((colType.IsGenericType) && (colType.GetGenericTypeDefinition() == typeof(Nullable<>)))
                {
                    colType = colType.GetGenericArguments()[0];
                }
                dtReturn.Columns.Add(new DataColumn(pi.Name, colType));
            }
        }
        DataRow dr = dtReturn.NewRow(); foreach (PropertyInfo pi in oProps)
        {
            dr[pi.Name] = pi.GetValue(rec, null) == null ? DBNull.Value : pi.GetValue(rec, null);
        }
        dtReturn.Rows.Add(dr);
    }
    return (dtReturn);
}
public delegate object[] CreateRowDelegate<T>(T t);

使用方法：

/// <summary>
/// 在linqtoEF中直接返回DataTable
/// </summary>
/// <returns></returns>
public DataTable getallDataTable()
{
    using(TPOSEntity entity=new TPOSEntity())
    {
        var l = from t in entity.IsOutMerchant
                orderby t.Id ascending
                select new
                {
                    商户编号 = t.MerchantNumber,
                    注册省份 = t.RegisterProvince,
                    注册城市 = t.RegisterCity,
                    注册时间 = t.RegisterTime,
                    商户认证状态 = t.MerchantAuthenticationState,
                    商户表代理商名 = t.AgentName,
                    交易表代理商名 = t.TransactionAgentName,
                    商户名称 = t.MerchantName,
                    交易金额 = t.OrderMoney
                };
        DataTable dt = l.ToDataTable(rre => new object[] { l });
        return dt;
    }
}