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; }