public LoadFromCollection(ExcelRangeBase range, IEnumerable <T> items, LoadFromCollectionParams parameters) : base(range, parameters)
{
_items = items;
_members = parameters.Members;
_bindingFlags = parameters.BindingFlags;
_headerParsingType = parameters.HeaderParsingType;
var type = typeof(T);
if (_members == null)
{
_members = type.GetProperties(_bindingFlags);
}
else
{
if (_members.Length == 0) //Fixes issue 15555
{
throw (new ArgumentException("Parameter Members must have at least one property. Length is zero"));
}
foreach (var t in _members)
{
if (t.DeclaringType != null && t.DeclaringType != type)
{
_isSameType = false;
}
//Fixing inverted check for IsSubclassOf / Pullrequest from tomdam
if (t.DeclaringType != null && t.DeclaringType != type && !TypeCompat.IsSubclassOf(type, t.DeclaringType) && !TypeCompat.IsSubclassOf(t.DeclaringType, type))
{
throw new InvalidCastException("Supplied properties in parameter Properties must be of the same type as T (or an assignable type from T)");
}
}
}
}
/// <summary>
/// Indexer
/// </summary>
/// <param name="Name">Name</param>
/// <returns></returns>
public T this[string Name]
{
get
{
return(_list.Find((f) => TypeCompat.GetPropertyValue(f, "Name").ToString().Equals(Name, StringComparison.OrdinalIgnoreCase)));
}
}
private void SetValuesAndFormulas(object[,] values, Dictionary <int, FormulaCell> formulaCells, ref int col, ref int row)
{
var nMembers = GetNumberOfColumns();
foreach (var item in _items)
{
if (item == null)
{
col = GetNumberOfColumns();
}
else
{
col = 0;
if (item is string || item is decimal || item is DateTime || TypeCompat.IsPrimitive(item))
{
values[row, col++] = item;
}
else
{
foreach (var colInfo in _columns)
{
if (colInfo.MemberInfo != null)
{
var member = colInfo.MemberInfo;
if (_isSameType == false && item.GetType().GetMember(member.Name, _bindingFlags).Length == 0)
{
col++;
continue; //Check if the property exists if and inherited class is used
}
else if (member is PropertyInfo)
{
values[row, col++] = ((PropertyInfo)member).GetValue(item, null);
}
else if (member is FieldInfo)
{
values[row, col++] = ((FieldInfo)member).GetValue(item);
}
else if (member is MethodInfo)
{
values[row, col++] = ((MethodInfo)member).Invoke(item, null);
}
}
else if (!string.IsNullOrEmpty(colInfo.Formula))
{
formulaCells[colInfo.Index] = new FormulaCell {
Formula = colInfo.Formula
};
}
else if (!string.IsNullOrEmpty(colInfo.FormulaR1C1))
{
formulaCells[colInfo.Index] = new FormulaCell {
FormulaR1C1 = colInfo.FormulaR1C1
};
}
}
}
}
row++;
}
}
private void SetNameElement(ExcelNamedRange name, XmlElement elem)
{
if (name.IsName)
{
if (string.IsNullOrEmpty(name.NameFormula))
{
if ((TypeCompat.IsPrimitive(name.NameValue) || name.NameValue is double || name.NameValue is decimal))
{
elem.InnerText = Convert.ToDouble(name.NameValue, CultureInfo.InvariantCulture).ToString("R15", CultureInfo.InvariantCulture);
}
else if (name.NameValue is DateTime)
{
elem.InnerText = ((DateTime)name.NameValue).ToOADate().ToString(CultureInfo.InvariantCulture);
}
else
{
elem.InnerText = "\"" + name.NameValue.ToString() + "\"";
}
}
else
{
elem.InnerText = name.NameFormula;
}
}
else
{
elem.InnerText = name.FullAddressAbsolute;
}
}
public static bool IsNumeric(this object obj)
{
if (obj == null)
{
return(false);
}
return(TypeCompat.IsPrimitive(obj) || obj is double || obj is decimal || obj is System.DateTime || obj is TimeSpan);
}
protected bool IsNumeric(object val)
{
if (val == null)
{
return(false);
}
return(TypeCompat.IsPrimitive(val) || val is double || val is decimal || val is System.DateTime || val is TimeSpan);
}
internal static bool IsNumeric(object candidate)
{
if (candidate == null)
{
return(false);
}
return(TypeCompat.IsPrimitive(candidate) || candidate is double || candidate is decimal || candidate is DateTime || candidate is TimeSpan || candidate is long);
}
internal static bool IsNumeric(object candidate)
{
double doubleValue;
if (candidate == null)
{
return(false);
}
return(TypeCompat.IsPrimitive(candidate) || double.TryParse(candidate.ToString(), out doubleValue) || candidate is double || candidate is decimal || candidate is DateTime || candidate is TimeSpan || candidate is long);
}
internal static bool IsNumeric(object candidate)
{
if (candidate == null)
{
return(false);
}
if (TypeCompat.IsPrimitive(candidate))
{
return(true);
}
var t = candidate.GetType();
return(t == typeof(double) || t == typeof(decimal) || t == typeof(long));
}
private static void Convert(Type fromType, Type toType, ILGenerator gen)
{
if (fromType == toType || fromType.IsSubclassOf(toType))
{
return;
}
var converted = TypeCompat.TryConvert(gen, fromType, toType);
if (!converted)
{
throw new InvalidCastException($"Cannot convert {fromType.Name} to {toType.Name}.");
}
}
internal static string FormatValue(object v, ExcelNumberFormatXml.ExcelFormatTranslator nf, string format, string textFormat)
{
if (v is decimal || TypeCompat.IsPrimitive(v))
{
double d;
try
{
d = Convert.ToDouble(v);
}
catch
{
return("");
}
if (nf.DataType == ExcelNumberFormatXml.eFormatType.Number)
{
if (string.IsNullOrEmpty(nf.FractionFormat))
{
return(d.ToString(format, nf.Culture));
}
else
{
return(nf.FormatFraction(d));
}
}
else if (nf.DataType == ExcelNumberFormatXml.eFormatType.DateTime)
{
var date = DateTime.FromOADate(d);
return(GetDateText(date, format, nf));
}
}
else if (v is DateTime)
{
if (nf.DataType == ExcelNumberFormatXml.eFormatType.DateTime)
{
return(GetDateText((DateTime)v, format, nf));
}
else
{
double d = ((DateTime)v).ToOADate();
if (string.IsNullOrEmpty(nf.FractionFormat))
{
return(d.ToString(format, nf.Culture));
}
else
{
return(nf.FormatFraction(d));
}
}
}
else if (v is TimeSpan)
{
if (nf.DataType == ExcelNumberFormatXml.eFormatType.DateTime)
{
return(GetDateText(new DateTime(((TimeSpan)v).Ticks), format, nf));
}
else
{
double d = new DateTime(0).Add((TimeSpan)v).ToOADate();
if (string.IsNullOrEmpty(nf.FractionFormat))
{
return(d.ToString(format, nf.Culture));
}
else
{
return(nf.FormatFraction(d));
}
}
}
else
{
if (textFormat == "")
{
return(v.ToString());
}
else
{
return(string.Format(textFormat, v));
}
}
return(v.ToString());
}
/// <summary>
/// Convert cell value to desired type, including nullable structs.
/// When converting blank string to nullable struct (e.g. ' ' to int?) null is returned.
/// When attempted conversion fails exception is passed through.
/// </summary>
/// <typeparam name="T">
/// The type to convert to.
/// </typeparam>
/// <returns>
/// The <paramref name="value"/> converted to <typeparamref name="T"/>.
/// </returns>
/// <remarks>
/// If input is string, parsing is performed for output types of DateTime and TimeSpan, which if fails throws <see cref="FormatException"/>.
/// Another special case for output types of DateTime and TimeSpan is when input is double, in which case <see cref="DateTime.FromOADate"/>
/// is used for conversion. This special case does not work through other types convertible to double (e.g. integer or string with number).
/// In all other cases 'direct' conversion <see cref="Convert.ChangeType(object, Type)"/> is performed.
/// </remarks>
/// <exception cref="FormatException">
/// <paramref name="value"/> is string and its format is invalid for conversion (parsing fails)
/// </exception>
/// <exception cref="InvalidCastException">
/// <paramref name="value"/> is not string and direct conversion fails
/// </exception>
public static T GetTypedCellValue <T>(object value)
{
if (value == null)
{
return(default(T));
}
var fromType = value.GetType();
var toType = typeof(T);
var toNullableUnderlyingType = (TypeCompat.IsGenericType(toType) && toType.GetGenericTypeDefinition() == typeof(Nullable <>))
? Nullable.GetUnderlyingType(toType)
: null;
if (fromType == toType || fromType == toNullableUnderlyingType)
{
return((T)value);
}
// if converting to nullable struct and input is blank string, return null
if (toNullableUnderlyingType != null && fromType == typeof(string) && ((string)value).Trim() == string.Empty)
{
return(default(T));
}
toType = toNullableUnderlyingType ?? toType;
if (toType == typeof(DateTime))
{
if (value is double)
{
return((T)(object)(DateTime.FromOADate((double)value)));
}
if (fromType == typeof(TimeSpan))
{
return((T)(object)(new DateTime(((TimeSpan)value).Ticks)));
}
if (fromType == typeof(string))
{
return((T)(object)DateTime.Parse(value.ToString()));
}
}
else if (toType == typeof(TimeSpan))
{
if (value is double)
{
return((T)(object)(new TimeSpan(DateTime.FromOADate((double)value).Ticks)));
}
if (fromType == typeof(DateTime))
{
return((T)(object)(new TimeSpan(((DateTime)value).Ticks)));
}
if (fromType == typeof(string))
{
return((T)(object)TimeSpan.Parse(value.ToString()));
}
}
return((T)Convert.ChangeType(value, toType));
}
/// <summary>
/// If a specific name exists in the collection
/// </summary>
/// <param name="Name">The name</param>
/// <returns>True if the name exists</returns>
public bool Exists(string Name)
{
return(_list.Exists((f) => TypeCompat.GetPropertyValue(f, "Name").ToString().Equals(Name, StringComparison.OrdinalIgnoreCase)));
}
// previous implementation
internal T GetTypedValue <T>(object v)
{
if (v == null)
{
return(default(T));
}
Type fromType = v.GetType();
Type toType = typeof(T);
Type toType2 = (TypeCompat.IsGenericType(toType) && toType.GetGenericTypeDefinition().Equals(typeof(Nullable <>)))
? Nullable.GetUnderlyingType(toType)
: null;
if (fromType == toType || fromType == toType2)
{
return((T)v);
}
var cnv = TypeDescriptor.GetConverter(fromType);
if (toType == typeof(DateTime) || toType2 == typeof(DateTime)) //Handle dates
{
if (fromType == typeof(TimeSpan))
{
return((T)(object)(new DateTime(((TimeSpan)v).Ticks)));
}
else if (fromType == typeof(string))
{
DateTime dt;
if (DateTime.TryParse(v.ToString(), out dt))
{
return((T)(object)(dt));
}
else
{
return(default(T));
}
}
else
{
if (cnv.CanConvertTo(typeof(double)))
{
return((T)(object)(DateTime.FromOADate((double)cnv.ConvertTo(v, typeof(double)))));
}
else
{
return(default(T));
}
}
}
else if (toType == typeof(TimeSpan) || toType2 == typeof(TimeSpan)) //Handle timespan
{
if (fromType == typeof(DateTime))
{
return((T)(object)(new TimeSpan(((DateTime)v).Ticks)));
}
else if (fromType == typeof(string))
{
TimeSpan ts;
if (TimeSpan.TryParse(v.ToString(), out ts))
{
return((T)(object)(ts));
}
else
{
return(default(T));
}
}
else
{
if (cnv.CanConvertTo(typeof(double)))
{
return((T)(object)(new TimeSpan(DateTime.FromOADate((double)cnv.ConvertTo(v, typeof(double))).Ticks)));
}
else
{
try
{
// Issue 14682 -- "GetValue<decimal>() won't convert strings"
// As suggested, after all special cases, all .NET to do it's
// preferred conversion rather than simply returning the default
return((T)Convert.ChangeType(v, typeof(T)));
}
catch (Exception)
{
// This was the previous behaviour -- no conversion is available.
return(default(T));
}
}
}
}
else
{
if (cnv.CanConvertTo(toType))
{
return((T)cnv.ConvertTo(v, typeof(T)));
}
else
{
if (toType2 != null)
{
toType = toType2;
if (cnv.CanConvertTo(toType))
{
return((T)cnv.ConvertTo(v, toType)); //Fixes issue 15377
}
}
if (fromType == typeof(double) && toType == typeof(decimal))
{
return((T)(object)Convert.ToDecimal(v));
}
else if (fromType == typeof(decimal) && toType == typeof(double))
{
return((T)(object)Convert.ToDouble(v));
}
else
{
return(default(T));
}
}
}
}
protected override void LoadInternal(object[,] values)
{
int col = 0, row = 0;
if (_members.Length > 0 && PrintHeaders)
{
foreach (var t in _members)
{
var descriptionAttribute = t.GetCustomAttributes(typeof(DescriptionAttribute), false).FirstOrDefault() as DescriptionAttribute;
var header = string.Empty;
if (descriptionAttribute != null)
{
header = descriptionAttribute.Description;
}
else
{
var displayNameAttribute =
t.GetCustomAttributes(typeof(DisplayNameAttribute), false).FirstOrDefault() as
DisplayNameAttribute;
if (displayNameAttribute != null)
{
header = displayNameAttribute.DisplayName;
}
else
{
header = ParseHeader(t.Name);
}
}
//_worksheet.SetValueInner(row, col++, header);
values[row, col++] = header;
}
row++;
}
if (!_items.Any() && (_members.Length == 0 || PrintHeaders == false))
{
return;
}
var nMembers = GetNumberOfColumns();
foreach (var item in _items)
{
if (item == null)
{
col = GetNumberOfColumns();
}
else
{
col = 0;
if (item is string || item is decimal || item is DateTime || TypeCompat.IsPrimitive(item))
{
values[row, col++] = item;
}
else
{
foreach (var t in _members)
{
if (_isSameType == false && item.GetType().GetMember(t.Name, _bindingFlags).Length == 0)
{
col++;
continue; //Check if the property exists if and inherited class is used
}
else if (t is PropertyInfo)
{
values[row, col++] = ((PropertyInfo)t).GetValue(item, null);
}
else if (t is FieldInfo)
{
values[row, col++] = ((FieldInfo)t).GetValue(item);
}
else if (t is MethodInfo)
{
values[row, col++] = ((MethodInfo)t).Invoke(item, null);
}
}
}
}
row++;
}
}
/// <summary>
/// Load a collection into the worksheet starting from the top left row of the range.
/// </summary>
/// <typeparam name="T">The datatype in the collection</typeparam>
/// <param name="Collection">The collection to load</param>
/// <param name="PrintHeaders">Print the property names on the first row. Any underscore in the property name will be converted to a space. If the property is decorated with a <see cref="DisplayNameAttribute"/> or a <see cref="DescriptionAttribute"/> that attribute will be used instead of the reflected member name.</param>
/// <param name="TableStyle">Will create a table with this style. If set to TableStyles.None no table will be created</param>
/// <param name="memberFlags">Property flags to use</param>
/// <param name="Members">The properties to output. Must be of type T</param>
/// <returns>The filled range</returns>
public ExcelRangeBase LoadFromCollection <T>(IEnumerable <T> Collection, bool PrintHeaders, TableStyles TableStyle, BindingFlags memberFlags, MemberInfo[] Members)
{
var type = typeof(T);
bool isSameType = true;
if (Members == null)
{
Members = type.GetProperties(memberFlags);
}
else
{
if (Members.Length == 0) //Fixes issue 15555
{
throw (new ArgumentException("Parameter Members must have at least one property. Length is zero"));
}
foreach (var t in Members)
{
if (t.DeclaringType != null && t.DeclaringType != type)
{
isSameType = false;
}
//Fixing inverted check for IsSubclassOf / Pullrequest from tomdam
if (t.DeclaringType != null && t.DeclaringType != type && !TypeCompat.IsSubclassOf(type, t.DeclaringType) && !TypeCompat.IsSubclassOf(t.DeclaringType, type))
{
throw new InvalidCastException("Supplied properties in parameter Properties must be of the same type as T (or an assignable type from T)");
}
}
}
var members = Members.Length == 0 ? 1 : Members.Length;
// create buffer
object[,] values = new object[(PrintHeaders ? Collection.Count() + 1 : Collection.Count()), members];
int col = 0, row = 0;
if (Members.Length > 0 && PrintHeaders)
{
foreach (var t in Members)
{
var descriptionAttribute = t.GetCustomAttributes(typeof(DescriptionAttribute), false).FirstOrDefault() as DescriptionAttribute;
var header = string.Empty;
if (descriptionAttribute != null)
{
header = descriptionAttribute.Description;
}
else
{
var displayNameAttribute =
t.GetCustomAttributes(typeof(DisplayNameAttribute), false).FirstOrDefault() as
DisplayNameAttribute;
if (displayNameAttribute != null)
{
header = displayNameAttribute.DisplayName;
}
else
{
header = t.Name.Replace('_', ' ');
}
}
//_worksheet.SetValueInner(row, col++, header);
values[row, col++] = header;
}
row++;
}
if (!Collection.Any() && (Members.Length == 0 || PrintHeaders == false))
{
return(null);
}
foreach (var item in Collection)
{
if (item == null)
{
col = members;
}
else
{
col = 0;
if (members == 1 || item is string || item is decimal || item is DateTime || TypeCompat.IsPrimitive(item))
{
values[row, col++] = item;
}
else
{
foreach (var t in Members)
{
if (isSameType == false && item.GetType().GetMember(t.Name, memberFlags).Length == 0)
{
col++;
continue; //Check if the property exists if and inherited class is used
}
else if (t is PropertyInfo)
{
values[row, col++] = ((PropertyInfo)t).GetValue(item, null);
}
else if (t is FieldInfo)
{
values[row, col++] = ((FieldInfo)t).GetValue(item);
}
else if (t is MethodInfo)
{
values[row, col++] = ((MethodInfo)t).Invoke(item, null);
}
}
}
}
row++;
}
_worksheet.SetRangeValueInner(_fromRow, _fromCol, _fromRow + row - 1, _fromCol + col - 1, values);
//Must have at least 1 row, if header is showen
if (row == 1 && PrintHeaders)
{
row++;
}
var r = _worksheet.Cells[_fromRow, _fromCol, _fromRow + row - 1, _fromCol + col - 1];
if (TableStyle != TableStyles.None)
{
var tbl = _worksheet.Tables.Add(r, "");
tbl.ShowHeader = PrintHeaders;
tbl.TableStyle = TableStyle;
}
return(r);
}
public LoadFromCollection(ExcelRangeBase range, IEnumerable <T> items, LoadFromCollectionParams parameters) : base(range, parameters)
{
_items = items;
_bindingFlags = parameters.BindingFlags;
_headerParsingType = parameters.HeaderParsingType;
var type = typeof(T);
var tableAttr = type.GetFirstAttributeOfType <EpplusTableAttribute>();
if (tableAttr != null)
{
ShowFirstColumn = tableAttr.ShowFirstColumn;
ShowLastColumn = tableAttr.ShowLastColumn;
ShowTotal = tableAttr.ShowTotal;
}
if (parameters.Members == null)
{
var columns = SetupColumns();
_columns = columns.ToArray();
}
else
{
_columns = parameters.Members.Select(x => new ColumnInfo {
MemberInfo = x
}).ToArray();
if (_columns.Length == 0) //Fixes issue 15555
{
throw (new ArgumentException("Parameter Members must have at least one property. Length is zero"));
}
foreach (var columnInfo in _columns)
{
if (columnInfo.MemberInfo == null)
{
continue;
}
var member = columnInfo.MemberInfo;
if (member.DeclaringType != null && member.DeclaringType != type)
{
_isSameType = false;
}
//Fixing inverted check for IsSubclassOf / Pullrequest from tomdam
if (member.DeclaringType != null && member.DeclaringType != type && !TypeCompat.IsSubclassOf(type, member.DeclaringType) && !TypeCompat.IsSubclassOf(member.DeclaringType, type))
{
throw new InvalidCastException("Supplied properties in parameter Properties must be of the same type as T (or an assignable type from T)");
}
}
}
}
