public static List <T> ConvertToList <T>(this DataTable dt)
{
var columnNames = dt.Columns.Cast <DataColumn>()
.Select(c => c.ColumnName)
.ToList();
//To Do Get DisplayName attributes
var properties = TypeDescriptor.GetProperties(typeof(T))
.Cast <PropertyDescriptor>()
.Where(propertyInfo => propertyInfo.PropertyType.Namespace.Equals("System"))
.ToArray();
return(dt.Rows.Cast <DataRow>().Select(row =>
{
var objT = Activator.CreateInstance <T>();
foreach (var pro in properties)
{
var p = pro.Name.ToUpper();
var pType = pro.PropertyType;
if (columnNames.Any(aa => aa.ToUpper() == p))
{
var val = row[p];
if (val != DBNull.Value)
{
var targetType = TypeEx.IsNullableType(pro.PropertyType) ? Nullable.GetUnderlyingType(pro.PropertyType) : pro.PropertyType;
var propertyVal = Convert.ChangeType(row[pro.Name], targetType);
pro.SetValue(objT, propertyVal);
}
}
}
return objT;
}).ToList());
}
private static Expression GetExpression <T, U>(ParameterExpression param, GenericFilter filter, params string[] nestedPropety)
{
PropertyInfo pro = null;
Type propertyType = null;
Expression left = null;
MemberExpression member = null;
//ParameterExpression navParam = null;
var nested = nestedPropety.Where(aa => aa == filter.PropertyName).FirstOrDefault();
if (nested != null)
{
//navParam = Expression.Parameter(typeof(U), "ex");
left = Expression.Property(param, typeof(T).GetProperty(typeof(U).Name));
left = Expression.Property(left, typeof(U).GetProperty(nested));
pro = typeof(U).GetProperty(nested);
propertyType = pro.PropertyType;
// member = Expression.MakeMemberAccess(navParam, pro);
}
else
{
pro = typeof(T).GetProperty(filter.PropertyName);
propertyType = pro.PropertyType;
member = Expression.MakeMemberAccess(param, pro);
}
var targetType = TypeEx.IsNullableType(pro.PropertyType) ? Nullable.GetUnderlyingType(pro.PropertyType) : pro.PropertyType;
var propertyVal = Convert.ChangeType(filter.Value, targetType);
ConstantExpression constant = Expression.Constant(propertyVal, targetType);
if (nested != null)
{
switch (filter.Operation)
{
case Op.Equals:
return(Expression.Equal(left, constant));
case Op.GreaterThan:
return(Expression.GreaterThan(left, constant));
case Op.GreaterThanOrEqual:
return(Expression.GreaterThanOrEqual(left, constant));
case Op.LessThan:
return(Expression.LessThan(left, constant));
case Op.LessThanOrEqual:
return(Expression.LessThanOrEqual(left, constant));
case Op.Contains:
return(Expression.Call(left, containsMethod, constant));
case Op.StartsWith:
return(Expression.Call(left, startsWithMethod, constant));
case Op.EndsWith:
return(Expression.Call(left, endsWithMethod, constant));
case Op.NotEqual:
return(Expression.NotEqual(left, constant));
}
}
else
{
switch (filter.Operation)
{
case Op.Equals:
return(Expression.Equal(member, constant));
case Op.GreaterThan:
return(Expression.GreaterThan(member, constant));
case Op.GreaterThanOrEqual:
return(Expression.GreaterThanOrEqual(member, constant));
case Op.LessThan:
return(Expression.LessThan(member, constant));
case Op.LessThanOrEqual:
return(Expression.LessThanOrEqual(member, constant));
case Op.Contains:
return(Expression.Call(member, containsMethod, constant));
case Op.StartsWith:
return(Expression.Call(member, startsWithMethod, constant));
case Op.EndsWith:
return(Expression.Call(member, endsWithMethod, constant));
case Op.NotEqual:
return(Expression.NotEqual(member, constant));
}
}
return(null);
}
private static Expression GetExpression <T>(ParameterExpression param, GenericFilter filter)
{
#region oldCode
var pro = typeof(T).GetProperty(filter.PropertyName);
var propertyType = pro.PropertyType;
MemberExpression member = Expression.MakeMemberAccess(param, pro);
// var constant = Expression.Convert(Expression.Constant(filter.Value), propertyType);
#endregion
#region newCode
//var fieldAccess = Expression.PropertyOrField(param, filter.PropertyName);
//var value = Expression.Constant(filter.Value, filter.Value.GetType());
//var constant = value.Type != fieldAccess.Type ?
// (Expression)Expression.Convert(value, fieldAccess.Type) :
// (Expression)value;
#endregion
#region olderCode
Expression constant;
//var targetType = TypeEx.IsNullableType(pro.PropertyType) ? Nullable.GetUnderlyingType(pro.PropertyType) : pro.PropertyType;
if (TypeEx.IsNullableType(propertyType))
{
constant = Expression.Convert(Expression.Constant(filter.Value), propertyType);
}
else
{
var propertyVal = Convert.ChangeType(filter.Value, propertyType);
constant = Expression.Constant(propertyVal) as ConstantExpression;
}
// var constanta = Expression.Convert(Expression.Constant(filter.Value), propertyType);
// var propertyVal = Convert.ChangeType(filter.Value, propertyType);
// ConstantExpression constant = Expression.Constant(propertyVal);
#endregion
#region Switches
switch (filter.Operation)
{
case Op.Equals:
return(Expression.Equal(member, constant));
case Op.GreaterThan:
return(Expression.GreaterThan(member, constant));
case Op.GreaterThanOrEqual:
return(Expression.GreaterThanOrEqual(member, constant));
case Op.LessThan:
return(Expression.LessThan(member, constant));
case Op.LessThanOrEqual:
return(Expression.LessThanOrEqual(member, constant));
case Op.Contains:
return(Expression.Call(member, containsMethod, constant));
case Op.StartsWith:
return(Expression.Call(member, startsWithMethod, constant));
case Op.EndsWith:
return(Expression.Call(member, endsWithMethod, constant));
case Op.NotEqual:
return(Expression.NotEqual(member, constant));
}
#endregion
return(null);
}
public static Result <MemoryStream> ExportToExcel <T>(this IList <T> list)
{
MemoryStream excelStream = new MemoryStream();
var properties = TypeDescriptor.GetProperties(typeof(T))
.Cast <PropertyDescriptor>()
.Where(propertyInfo => propertyInfo.PropertyType.Namespace.Equals("System"))
.ToArray();
var totalItems = list.Count;
try
{
using (ExcelPackage excel = new ExcelPackage())
{
var wk = excel.Workbook.Worksheets.Add("Sheet1");
wk.DefaultColWidth = 10;
wk.Cells.Style.WrapText = true;
var totalColumns = properties.Count();
for (var col = 0; col < totalColumns; col++)
{
wk.Cells[1, col + 1].Value = properties[col].Name;
}
using (var range = wk.Cells[1, 1, 1, totalColumns])
{
range.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
range.Style.Fill.BackgroundColor.SetColor(color: Color.Gray);
range.Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thick;
range.Style.Border.Bottom.Color.SetColor(Color.Black);
range.Style.Font.Color.SetColor(Color.Black);
range.Style.Font.SetFromFont(new System.Drawing.Font("Arial", 10));
range.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.CenterContinuous;
}
for (var index = 0; index < totalItems; index++)
{
var currentRecord = list[index];
for (var propIndex = 0; propIndex < totalColumns; propIndex++)
{
var val = currentRecord.GetType().GetProperty(properties[propIndex].Name).GetValue(currentRecord);
if (val != null)
{
var targetType = TypeEx.IsNullableType(properties[propIndex].PropertyType) ? Nullable.GetUnderlyingType(properties[propIndex].PropertyType) : properties[propIndex].PropertyType;
var propertyVal = Convert.ChangeType(val, targetType);
wk.Cells[index + 2, propIndex + 1].Value = propertyVal;
if (targetType == typeof(DateTime))
{
wk.Cells[index + 2, propIndex + 1].Style.Numberformat.Format = "mmm/dd/yyyy";
wk.Cells[index + 2, propIndex + 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
}
}
}
}
excel.Save();
excelStream = excel.Stream as MemoryStream;
}
}
catch (Exception ex)
{
return(new Result <MemoryStream>
{
Data = null,
Message = ex.Message,
ResultType = ResultType.Exception
});
}
return(new Result <MemoryStream>
{
Data = new MemoryStream(excelStream.ToArray()),
Message = ResultType.Success.ToString(),
ResultType = ResultType.Success
});
}