I am working with excel files. I have to read tabular values stored in excel sheets and deserialize them to objects. For this, I am using OfficeOpenXml with the help of EPPlus.DataExtractor. My Excel tables have several columns, thus my classes have several properties - with different datatypes strings, ints, DateTimes, doubles and nullable ints, DateTimes, doubles. I cannot assume that more types won't come in time. For example, a class for deserializing excel row can look like this:
public class MyModel
{
[Column("A")]
public string Id { get; set; }
[Column("B")]
public string Code { get; set; }
[Column("C")]
public int Number { get; set; }
[Column("D")]
public DateTime? ValidTo { get; set; }
}
Column is my own attribute, which tells the extractor which column contains value for a given property:
public class ColumnAttribute : Attribute
{
public string Column { get; set; }
public ColumnAttribute(string column) => Column = column;
}
Thats why, i can use the EPPlus like this
public class MyModelExtractor
{
private readonly string _path;
public MyModelExtractor(string path) => _path = path;
public List<MyModel> Create()
{
using (var excelPackage = new ExcelPackage(new FileInfo(_path)))
{
var worksheet = excelPackage.Workbook.Worksheets[1];
return worksheet
.Extract<MyModel>()
.WithProperty(p => p.Id, MyModel.GetColumnAnnotation(p => p.Id))
.WithProperty(p => p.Code , MyModel.GetColumnAnnotation(p => p.Code ))
.WithProperty(p => p.Number, MyModel.GetColumnAnnotation(p => p.Number))
.WithProperty(p => p.ValidTo , MyModel.GetColumnAnnotation(p => p.ValidTo ))
.GetData(2, row => worksheet.Cells[row, 1].Value != null)
.ToList();
}
}
Now, there is something more in MyModel class, namely:
public static string GetColumnAnnotation<T>(Expression<Func<MyModel, T>> propertySelector) =>
AttributeExtractor.GetPropertyAttributeValue<MyModel, T, ColumnAttribute, string>(propertySelector, attribute => attribute.Column);
which, as can be seen, is used in WithProperty method, to get the value of Column attribute (simply a string).
For completness, I will provide AttributeExtractor, which looks like this:
public static class AttributeExtractor
{
public static TValue GetPropertyAttributeValue<T, TOut, TAttribute, TValue>(Expression<Func<T, TOut>> propertyExpression,
Func<TAttribute, TValue> valueSelector) where TAttribute : Attribute
{
var propertyInfo = (PropertyInfo)((MemberExpression)propertyExpression.Body).Member;
return propertyInfo.GetCustomAttributes(typeof(TAttribute), true).FirstOrDefault() is TAttribute attr
? valueSelector(attr)
: throw new MissingMemberException(typeof(T).Name + "." + propertyInfo.Name, typeof(TAttribute).Name);
}
}
Now, in every model class (and I have tens of them) I have to provide this static method GetPropertyAttributeValue. What is more problematic, the classes contain lots of properties, so the call to WithProperty is done many times. And, again, for every class, I have separate corresponding extractor.
I thought about creating a generic version of Extractor, like
public class Extractor<T> { ... }
where T will be a type like MyModel, and then I could write some method, like WithAllProperties() which will replace all the calls to WithProperty.
The class would then look like this
public class Extractor<T>
{
...ctor and _path, and then:
public List<T> Create()
{
using (var excelPackage = new ExcelPackage(new FileInfo(_path)))
{
var worksheet = excelPackage.Workbook.Worksheets[1];
return worksheet
.Extract<T>()
.WithAllProperties()
.GetData(2, row => worksheet.Cells[row, 1].Value != null)
.ToList();
}
}
}
Now, I am struggling with the WithAllProperties method. It should look like this:
public static ICollectionPropertyConfiguration<T> WithAllProperties<T>(
this IDataExtractor<T> extractor) where T : class, new()
{
foreach(var property in typeof(T).GetProperties())
extractor = extractor.WithProperty(/1/, /2/);
return extractor as ICollectionPropertyConfiguration<T>;
}
What is missing is /1/ which should of type
Expression<Func<T,TProperty>>
I cannot dynamically generate this value (without some tricks which seem not intelligent for me, like switching the type of property variable, and creating the needed Expression. It works, but when new types come, this switch has to be extended and I am sure it can be done dynamically using reflection). Another thing is /2/ which is a value of Column attribute for corresponding property - for this I have no idea how to obtain it.
Any help/hints/clues needed.
Aucun commentaire:
Enregistrer un commentaire