public ExcelFieldInfo(string propertyName, ExcelColumnAttribute excelAttribute = null, string formatString = null)
{
PropertyName = propertyName;
ExcelAttribute = excelAttribute;
FormatString = formatString;
Header = IsExcelHeaderDefined ? ExcelAttribute.Header : propertyName;
}
public ExcelFieldInfo(string propertyName, ExcelColumnAttribute excelAttribute = null, string formatString = null)
{
PropertyName = propertyName;
ExcelAttribute = excelAttribute;
FormatString = formatString;
Header = IsExcelHeaderDefined ? ExcelAttribute.Header : propertyName;
}
public static List <KeyValuePair <PropertyInfo, ExcelColumnAttribute> > ToColumnDic(this Type @type)
{
Dictionary <PropertyInfo, ExcelColumnAttribute> mainDic = new Dictionary <PropertyInfo, ExcelColumnAttribute>();
int order = 1;
@type.GetProperties().ToList().ForEach(o =>
{
var attribute = o.GetCustomAttribute <ExcelColumnAttribute>();
if (attribute == null)
{
if (mainDic.Count > 0)
{
order = mainDic.ElementAt(mainDic.Count - 1).Value.Order + 1;
}
attribute = new ExcelColumnAttribute(o.Name, order);
mainDic.Add(o, attribute);
}
else if (!attribute.Ignore)
{
if (mainDic.Count > 0 && attribute.Order == 0)
{
order = mainDic.ElementAt(mainDic.Count - 1).Value.Order + 1;
attribute.Order = order;
}
mainDic.Add(o, attribute);
}
});
var mainPropertieList = mainDic.OrderBy(o => o.Value.Order).ToList();
return(mainPropertieList);
}
private void TestExcelAccess()
{
Position position = new Position();
Dictionary <string, string> column = ExcelColumnAttribute.ColumnNamesMapping(position);
string url = @"C:\Users\TUAN-LINH\Desktop\TestData.xlsx";
//string url = @"C:\Users\VuLin\Desktop\TestData.xlsx";
ExcelDataAccess reader = ExcelDataAccess.GetInstance();
Dictionary <string, Construction> positions = reader.Read <Construction>(url);
// reader.Read(url);
}
private void SetAggregationFunction(ExcelColumnAttribute columnAttr, ExcelDynamicColumn column)
{
if (columnAttr.NoAggregate)
{
column.AggregateFunction = AggregateFunction.NoAggregation;
}
else if (columnAttr.AggregateFunction != AggregateFunction.NoAggregation)
{
column.AggregateFunction = columnAttr.AggregateFunction;
}
}
private void SetDisplayFormat(ExcelColumnAttribute columnAttr, ExcelDynamicColumn column)
{
if (columnAttr.IgnoreDisplayFormat)
{
column.DisplayFormat = null;
}
else if (!string.IsNullOrWhiteSpace(columnAttr.DisplayFormat))
{
column.DisplayFormat = columnAttr.DisplayFormat;
}
}
// This constructor is called by users, create a new IQueryExecutor.
internal ExcelQueryable(ExcelQueryArgs args, ILogManagerFactory logManagerFactory)
: base(CreateExecutor(args, logManagerFactory))
{
foreach (var property in typeof(T).GetProperties())
{
ExcelColumnAttribute att = (ExcelColumnAttribute)Attribute.GetCustomAttribute(property, typeof(ExcelColumnAttribute));
if (att != null && !args.ColumnMappings.ContainsKey(property.Name))
{
args.ColumnMappings.Add(property.Name, att.ColumnName);
}
}
}
public static void Register(this ExcelColumnAttribute att, ExcelQueryArgs args,
PropertyInfo property)
{
if (att != null && !args.ColumnMappings.ContainsKey(property.Name))
{
args.ColumnMappings.Add(property.Name, att.ColumnName);
Func <string, object> transOper = att.Transformation;
if (transOper != null)
{
args.Transformations.Add(property.Name, transOper);
}
}
}
// This constructor is called by users, create a new IQueryExecutor.
internal ExcelQueryable(ExcelQueryArgs args, ILogManagerFactory logManagerFactory)
: base(QueryParser.CreateDefault(), CreateExecutor(args, logManagerFactory))
{
foreach (var property in typeof(T).GetProperties())
{
ExcelColumnAttribute att = (ExcelColumnAttribute)Attribute.GetCustomAttribute(property, typeof(ExcelColumnAttribute));
if (att != null && !args.ColumnMappings.ContainsKey(property.Name))
{
var columnNames = ExcelUtilities.GetColumnNames(args);
args.ColumnMappings.Add(property.Name, !att.IsForced ?
att.ColumnName :
columnNames.ToList().Find(x => att.HasSimilarColumn(x)) ?? att.ColumnName);
}
}
}
public void ToListTest()
{
IList <SysProduct> products = new WorkbookDataAdapter(new HSSFWorkbook(new FileStream(@"C:\npoitest.xls", FileMode.Open, FileAccess.Read)))
.SetColumnNameMapping(ExcelColumnAttribute.GetColumnNameMapping(typeof(SysProduct)))
.AddOnRowAdapt(c =>
{
bool result = true;
var record = (SysProduct)c.DataRow;
return(result);
}).ToList <SysProduct>();
foreach (var item in products)
{
Console.WriteLine(item.ProductCode + "|" + item.ProductName);
}
}
string GetColumn(PropertyInfo type)
{
object[] attributes = type.GetCustomAttributes(typeof(ExcelColumnAttribute), false);
string result = string.Empty;
if (attributes.Length != 0)
{
foreach (object attribute in attributes)
{
ExcelColumnAttribute column = attribute as ExcelColumnAttribute;
if (column != null)
{
result = column.ColumnString;
break;
}
}
}
return(result);
}
/// <summary>
/// get headers
/// </summary>
/// <param name="type"></param>
/// <returns></returns>
public List <string> GetPropertyDisplayNames(List <string> titleList = null, ResourceManager rm = null)
{
titleList = (titleList == null ? new List <string>() : titleList);
foreach (var propertyInfo in ExcelColumns)
{
object[] attrs = propertyInfo.GetCustomAttributes(true);
foreach (object attr in attrs)
{
///if this column need to export
ExcelColumnAttribute authAttr = attr as ExcelColumnAttribute;
if (authAttr != null)
{
var titleName = propertyInfo.GetDisplayName();
var displayAttrInfo = propertyInfo.GetCustomAttributes(true).Where(it => it.GetType() == typeof(DisplayAttribute)).SingleOrDefault();
if (displayAttrInfo != null)
{
var displayAttr = displayAttrInfo as DisplayAttribute;
var resourceType = displayAttr.ResourceType;
if (resourceType != null && rm != null)
{
//var rm = new ResourceManager(resourceType);
titleName = rm.GetString(titleName);
}
}
titleList.Add(titleName);
}
///if this object of collection need to export
var childDataAttr = attr as ChildDataAttribute;
if (childDataAttr != null)
{
var childHelper = new ExcelColumnHelper(childDataAttr.type);
childHelper.GetPropertyDisplayNames(titleList);
}
}
}
return(titleList);
}
public List <PropertyInfo> GetExcelColumns(Type type)
{
var model = new List <PropertyInfo>();
var propertyInfos = type.GetProperties();
foreach (var propertyInfo in propertyInfos)
{
object[] attrs = propertyInfo.GetCustomAttributes(true);
foreach (object attr in attrs)
{
///if this column need to export
ExcelColumnAttribute authAttr = attr as ExcelColumnAttribute;
if (authAttr != null)
{
model.Add(propertyInfo);
}
}
}
/// order by excel column order prop
model = model.OrderBy(it =>
(it.GetCustomAttributes(true).Where(t => t.GetType() == typeof(ExcelColumnAttribute)).Single() as ExcelColumnAttribute).Order).ToList();
return(model);
}
private IEnumerable <TExcelType> ReadExcelSheetData <TExcelType>(WorkBook workBook, int sheetNum, ref JsonObjectList errorList) where TExcelType : ExcelData
{
if (workBook == null)
{
return(new List <TExcelType>());
}
if (sheetNum < 0)
{
return(new List <TExcelType>());
}
var tempDataList = new List <TExcelType>();
workBook.Sheet = sheetNum;
var lastRow = workBook.LastRow;
var lastCol = workBook.LastCol;
var myObject = CreateGeneric(typeof(TExcelType)) as ExcelData;
// get properties of the ExcelMemberData only once
PropertyInfo[] pps = myObject.GetType().GetProperties();
ExcelGenericAttribute xlAttr = ExcelGenericAttribute.GetAttribute(myObject.GetType());
for (var i = 1; i <= lastRow; i++)
{
var mbrLine = CreateGeneric(typeof(TExcelType)) as ExcelData;
mbrLine.ExcelLine = i + 1;
if (xlAttr != null && xlAttr.AllowBlank && IsLineEmpty(workBook, i, pps.Length))
{
mbrLine.BlankLineFound = true;
continue;
}
foreach (PropertyInfo p in pps)
{
ExcelColumnAttribute xlColAttr = ExcelColumnAttribute.GetAttribute(p);
if (xlColAttr == null || xlColAttr.OutputOnly)
{
continue;
}
var xlValue = workBook.getText(i, xlColAttr.Column);
try
{
ExcelColumnAttribute.Validate(p, xlValue, mbrLine);
mbrLine.CustomPropertyValidation(p, xlValue, mbrLine);
}catch (Exception er)
{
var importStats = BaseImportWorker.GetImportStatus("GroupProperty", BaseUserContext.Instance.UserName);
importStats.Status = new { Msg = "Errors while validating" };
importStats.ImportProgres = 100;
mbrLine.IsValid = false;
mbrLine.ErrorMessage = p.Name + ":" + er.Message;
}
if (mbrLine.CurrentPropertyIsBlank && xlColAttr.IgnoreLineIfBlank)
{
mbrLine.IsValid = false;
mbrLine.WasIgnored = true;
continue;
}
try
{
if (mbrLine.IsValid && !mbrLine.CurrentPropertyIsBlank)
{
p.SetValue(mbrLine, mbrLine.ValidValue, null);
}
}
catch (Exception er)
{
if (mbrLine.IsValid) // to keep the first error
{
mbrLine.IsValid = false;
mbrLine.ErrorMessage = er.Message;
}
}
if (!mbrLine.IsValid)
{
break;
}
}
if (!mbrLine.IsValid && !mbrLine.WasIgnored)
{
var jso = new JsonObject();
jso.AddProperty("line", i + 1);
jso.AddProperty("sheet", sheetNum);
jso.AddProperty("error", mbrLine.ErrorMessage);
errorList.Add(jso);
}
tempDataList.Add((TExcelType)mbrLine);
}
return(tempDataList);
}
public List <TEntity> Map(ExcelData data)
{
try
{
if (data == null)
{
return(null);
}
PropertyInfo[] properties;
try
{
properties = typeof(TEntity)
.GetProperties()
.OrderBy(p => p.GetCustomAttributes().OfType <ExcelColumnAttribute>().First().Order)
.Select(p => p).ToArray();
}
catch (InvalidOperationException)
{
throw new Exception(
string.Format("{0} Data can't be import to Excel. Please check than ExcelColumn attribute is specified for each columns",
typeof(TEntity)));
}
if (!data.DataRows.Any())
{
return(new List <TEntity>());
}
var entities = new List <TEntity>();
var excelColumnProperties = new Dictionary <string, PropertyInfo>(StringComparer.OrdinalIgnoreCase);
var excelColumnAttributes =
new Dictionary <string, ExcelColumnAttribute>(StringComparer.OrdinalIgnoreCase);
for (int i = 0; i < data.DataRows.Count; i++)
{
var row = data.DataRows[i];
// skip a row if all cells are empty
if (row.All(string.IsNullOrEmpty))
{
continue;
}
var entity = (TEntity)Activator.CreateInstance(typeof(TEntity), new object[] {});
for (int j = 0; j < data.Headers.Count; j++)
{
if (i == 0 && !string.IsNullOrEmpty(data.Headers[j]))
{
ExcelColumnAttribute excelColumnAttribute = null;
var excelColumnProperty = properties
.FirstOrDefault(p =>
{
if (p.CanWrite &&
p.Name.Equals(data.Headers[j], StringComparison.CurrentCultureIgnoreCase))
{
return(true);
}
var item = p.GetCustomAttributes()
.OfType <ExcelColumnAttribute>()
.FirstOrDefault();
if (item != null && item.Name != null)
{
if (item.Name.Equals(data.Headers[j], StringComparison.CurrentCultureIgnoreCase))
{
excelColumnAttribute = item;
return(true);
}
}
return(false);
});
if (excelColumnProperty != null)
{
if (excelColumnProperties.ContainsKey(data.Headers[j]))
{
throw new InvalidDataException(string.Format("Input excel contains more than one columns with '{0}' name", data.Headers[j]));
}
excelColumnProperties.Add(data.Headers[j], excelColumnProperty);
}
if (excelColumnAttribute != null)
{
excelColumnAttributes.Add(data.Headers[j], excelColumnAttribute);
}
}
if (j >= row.Count)
{
continue;
}
var cell = row[j];
// don't write values of a column if a header is empty
if (!excelColumnProperties.ContainsKey(data.Headers[j]))
{
continue;
}
var property = excelColumnProperties[data.Headers[j]];
ExcelColumnAttribute excelAttribute;
excelColumnAttributes.TryGetValue(data.Headers[j], out excelAttribute);
Type propertyType = property.PropertyType;
object propertyValue;
try
{
propertyValue = _excelDataFormatConvertor.ConvertStringToValue(cell, propertyType, excelAttribute?.Format ?? ExcelColumnFormat.None);
Type t = Nullable.GetUnderlyingType(property.PropertyType) ?? property.PropertyType;
object safeValue = (propertyValue == null) ? null : Convert.ChangeType(propertyValue, t);
property.SetValue(entity, safeValue, null);
}
catch (Exception ex) when(ex is InvalidCastException || ex is FormatException)
{
var columnName = data.Headers[j];
var rowNumber = i + 2;
throw new InvalidFormatOfExcelCellException(data.SheetName, columnName, rowNumber, ex);
}
}
entities.Add(entity);
}
return(entities);
}
catch (InvalidFormatOfExcelCellException)
{
throw;
}
catch (Exception ex)
{
throw new Exception("An exception occurs during Excel data reading", ex);
}
}
public static List <T> CreateListFromSheet <T>(ScriptableObject parentObj, string filename, string sheetname) where T : ScriptableObject
{
Debug.Log(string.Format(
"Creating a list of type {0} from file {1} and sheet {2}", typeof(T).ToString(), filename, sheetname));
List <ColumnSpec> specs = new List <ColumnSpec>();
FieldInfo[] fields = typeof(T).GetFields(BindingFlags.NonPublic | BindingFlags.Instance | BindingFlags.Public);
if (fields.Length == 0)
{
Debug.LogError("Could not find any fields in class " + typeof(T).ToString());
}
foreach (var fieldInfo in fields)
{
Debug.Log(fieldInfo.ToString());
//System.Attribute[] attrs = propInfo.GetCustomAttributes(true);
object[] attrs = fieldInfo.GetCustomAttributes(true);
if (attrs.Length == 0)
{
Debug.LogError("Could not find any attributes in class " + typeof(T).ToString());
}
int found = 0;
foreach (var att in attrs)
{
ExcelColumnAttribute e = att as ExcelColumnAttribute;
if (e != null)
{
found += 1;
Debug.Log(att.ToString());
ColumnSpec spec = new ColumnSpec(
fieldInfo.FieldType,
e.startCol,
e.endCol,
fieldInfo.Name,
e.name != null ? e.name : fieldInfo.Name
);
specs.Add(spec);
}
}
if (found == 0)
{
Debug.LogError("Could not find any ExcelColumn attributes in class " + typeof(T).ToString());
}
}
// Now we have a column spec, we can go through the excel file and extract a bunch of objects
DataTable dt = GetDataTableFromExcel(filename, sheetname);
// Check that header has all of our expected fields
foreach (ColumnSpec spec in specs)
{
DataColumn found = null;
string colName = FormatColumnName(spec.columnName);
foreach (DataColumn col in dt.Columns)
{
if (FormatColumnName(col.ColumnName) == colName)
{
found = col;
break;
}
}
if (found == null)
{
Debug.LogError(string.Format(
"Spec error: File {0}, Sheet {1}: Couldn't find column named {2} (munged to {3})",
filename, sheetname, spec.columnName, colName));
}
else
{
spec.startCol = found.Ordinal;
spec.endCol = found.Ordinal;
}
}
// Create objects!
var dataObjects = new List <T>();
for (int r = 0; r < dt.Rows.Count; r++)
{
// All of the objects within our ScriptableObject must be ScriptableObjects themselves
// because of Unity's serialization and inheritance bug
T rowObj = (T)ScriptableObject.CreateInstance(typeof(T).Name);
foreach (ColumnSpec spec in specs)
{
for (int c = spec.startCol; c <= spec.endCol; c++)
{
Debug.Log(dt.Rows[r][c]);
Debug.Log(dt.Rows[r][c].GetType().ToString());
if (spec.isReq)
{
if (string.IsNullOrEmpty(dt.Rows[r][c].ToString()))
{
Debug.LogError(string.Format(
"Spec error: File {0}, Sheet {1}, Row {2}, Column {3} (#{4}): Cannot be empty.",
filename, sheetname, spec.startCol, r, spec.fieldName, c + 1));
}
}
FieldInfo rowObjFieldInfo = typeof(T).GetField(spec.fieldName, BindingFlags.NonPublic | BindingFlags.Instance | BindingFlags.Public);
if (rowObj == null)
{
Debug.Log("RowObj null");
}
if (rowObjFieldInfo == null)
{
Debug.Log("rowObjFieldInfo null for " + spec.fieldName + " for type " + typeof(T).ToString());
}
// Automagically try to cast to the target datatype
rowObjFieldInfo.SetValue(rowObj, Convert.ChangeType(dt.Rows[r][c], rowObjFieldInfo.FieldType));
}
}
dataObjects.Add(rowObj);
// To save this child ScriptableObject asset, we have to add it to the parent one
AssetDatabase.AddObjectToAsset(rowObj, parentObj);
// Hide these child assets so the container is the only selectable one
rowObj.hideFlags = HideFlags.HideAndDontSave;
}
if (dataObjects.Count == 0)
{
Debug.LogError("Didn't load anything");
}
return(dataObjects);
}
/// <summary>
/// Get the <c>ExcelColumnInfo</c> for all members of a class.
/// </summary>
/// <param name="itemType">Type of item being serialised.</param>
/// <param name="data">The collection of values being serialised. (Not used, provided for use by derived
/// types.)</param>
public virtual ExcelColumnInfoCollection GetExcelColumnInfo(Type itemType, object data, string namePrefix = "", bool isComplexColumn = false)
{
var fieldInfo = new ExcelColumnInfoCollection();
if (itemType.Name.StartsWith("Dictionary"))
{
var prefix = namePrefix + "_Dict_";
fieldInfo.Add(new ExcelColumnInfo(prefix, null, new ExcelColumnAttribute(), null));
return(fieldInfo);
}
var fields = GetSerialisableMemberNames(itemType, data);
var properties = GetSerialisablePropertyInfo(itemType, data);
// Instantiate field names and fieldInfo lists with serialisable members.
foreach (var field in fields)
{
var prop = properties.FirstOrDefault(p => p.Name == field);
if (prop == null)
{
continue;
}
Type propertyType = prop.PropertyType;
if (propertyType.IsGenericType &&
propertyType.GetGenericTypeDefinition() == typeof(Nullable <>))
{
propertyType = propertyType.GetGenericArguments()[0];
}
ExcelColumnAttribute attribute = FormatterUtils.GetAttribute <ExcelColumnAttribute>(prop);
if (attribute != null)
{
string prefix = string.IsNullOrEmpty(namePrefix) == false ? $"{namePrefix}:{prop.Name}" : prop.Name;
if (propertyType.Name.StartsWith("List"))
{
Type typeOfList = FormatterUtils.GetEnumerableItemType(propertyType);
//if (FormatterUtils.IsSimpleType(typeOfList))
//{
// fieldInfo.Add(new ExcelColumnInfo(prefix, typeOfList, attribute, null));
//}
//else
if (typeOfList.FullName.EndsWith("CustomFieldModel") || typeOfList.Name.StartsWith("OverrideProperty"))
{
prefix += "_CustomField_";
fieldInfo.Add(new ExcelColumnInfo(prefix, null, attribute, null));
}
else
{
prefix += "_List_";
fieldInfo.Add(new ExcelColumnInfo(prefix, null, attribute, null));
//ExcelColumnInfoCollection columnCollection = GetExcelColumnInfo(typeOfList, null, prefix, true);
//foreach (var subcolumn in columnCollection)
// fieldInfo.Add(subcolumn);
}
}
else if (propertyType.Name.EndsWith("CustomFieldModel") || propertyType.Name.StartsWith("OverrideProperty"))
{
prefix += "_CustomField_Single_";
fieldInfo.Add(new ExcelColumnInfo(prefix, null, attribute, null));
}
else if (propertyType.Name.StartsWith("Dictionary"))
{
prefix += "_Dict_";
fieldInfo.Add(new ExcelColumnInfo(prefix, null, attribute, null));
}
else if (!FormatterUtils.IsSimpleType(propertyType))
{
ExcelColumnInfoCollection columnCollection = GetExcelColumnInfo(propertyType, null, prefix, true);
foreach (var subcolumn in columnCollection)
{
fieldInfo.Add(subcolumn);
}
}
else
{
string propertyName = isComplexColumn ? $"{namePrefix}:{field}" : field;
string displayName = propertyName;
if (attribute.DoNotUsePropertyName)
{
attribute.Header = namePrefix;
}
bool columnAlreadyadded = fieldInfo.Any(a => a.PropertyName == propertyName);
if (!columnAlreadyadded)
{
if (FormatterUtils.IsExcelSupportedType(propertyType))
{
fieldInfo.Add(new ExcelColumnInfo(propertyName, propertyType, attribute, null));
}
else
{
fieldInfo.Add(new ExcelColumnInfo(propertyName, typeof(string), attribute, null));
}
}
}
}
}
PopulateFieldInfoFromMetadata(fieldInfo, itemType, data);
return(fieldInfo);
}
public PropertyAttributeItem(PropertyInfo property, ExcelColumnAttribute attribute)
{
Property = property;
Attribute = attribute;
}