private void Put <T>(ISheet sheet, IEnumerable <T> objects, bool overwrite) { var sheetName = sheet.SheetName; var firstRow = sheet.GetRow(sheet.FirstRowNum); var objectArray = objects as T[] ?? objects.ToArray(); var type = MapHelper.GetConcreteType(objectArray); var columns = GetTrackedColumns(sheetName, type) ?? GetColumns(firstRow ?? PopulateFirstRow(sheet, null, type), type); firstRow = sheet.GetRow(sheet.FirstRowNum) ?? PopulateFirstRow(sheet, columns, type); var rowIndex = overwrite ? HasHeader ? sheet.FirstRowNum + 1 : sheet.FirstRowNum : sheet.GetRow(sheet.LastRowNum) != null ? sheet.LastRowNum + 1 : sheet.LastRowNum; MapHelper.EnsureDefaultFormats(columns, TypeFormats); foreach (var o in objectArray) { var row = sheet.GetRow(rowIndex); if (overwrite && row != null) { sheet.RemoveRow(row); row = sheet.CreateRow(rowIndex); } row = row ?? sheet.CreateRow(rowIndex); foreach (var column in columns) { var pi = column.Attribute.Property; var value = pi?.GetValue(o); var cell = row.GetCell(column.Attribute.Index, MissingCellPolicy.CREATE_NULL_AS_BLANK); column.CurrentValue = value; if (column.Attribute.TryPut == null || column.Attribute.TryPut(column, o)) { SetCell(cell, column.CurrentValue, column, setStyle: overwrite); } } rowIndex++; } // Remove not used rows if any. while (overwrite && rowIndex <= sheet.LastRowNum) { var row = sheet.GetRow(rowIndex); if (row != null) { sheet.RemoveRow(row); } rowIndex++; } // Injects custom action for headers. if (overwrite && HasHeader && _headerAction != null) { firstRow?.Cells.ForEach(c => _headerAction(c)); } }
private IEnumerable <RowInfo <T> > Take <T>(ISheet sheet, int maxErrorRows, Func <T> objectInitializer = null) where T : class { if (sheet == null || sheet.PhysicalNumberOfRows < 1) { yield break; } var firstRowIndex = sheet.FirstRowNum; var firstRow = sheet.GetRow(firstRowIndex); var targetType = typeof(T); if (targetType == typeof(object)) // Dynamic type. { targetType = GetDynamicType(sheet); MapHelper.LoadDynamicAttributes(Attributes, DynamicAttributes, targetType); } // Scan object attributes. MapHelper.LoadAttributes(Attributes, targetType); // Read the first row to get column information. var columns = GetColumns(firstRow, targetType); // Detect column format based on the first non-null cell. Helper.LoadDataFormats(sheet, HasHeader ? firstRowIndex + 1 : firstRowIndex, columns, TypeFormats); if (TrackObjects) { Objects[sheet.SheetName] = new Dictionary <int, object>(); } // Loop rows in file. Generate one target object for each row. var errorCount = 0; foreach (IRow row in sheet) { if (maxErrorRows > 0 && errorCount >= maxErrorRows) { break; } if (HasHeader && row.RowNum == firstRowIndex) { continue; } var obj = objectInitializer == null?Activator.CreateInstance(targetType) : objectInitializer(); var rowInfo = new RowInfo <T>(row.RowNum, obj as T, -1, string.Empty); LoadRowData(columns, row, obj, rowInfo); if (rowInfo.ErrorColumnIndex >= 0) { errorCount++; rowInfo.Value = default(T); } if (TrackObjects) { Objects[sheet.SheetName][row.RowNum] = rowInfo.Value; } yield return(rowInfo); } }
private static void LoadRowData(IEnumerable <ColumnInfo> columns, IRow row, object target, IRowInfo rowInfo) { var errorIndex = -1; string errorMessage = null; void ColumnFailed(IColumnInfo column, string message) { if (errorIndex >= 0) { return; // Ensures the first error will not be overwritten. } if (column.Attribute.IgnoreErrors == true) { return; } errorIndex = column.Attribute.Index; errorMessage = message; } foreach (var column in columns) { var index = column.Attribute.Index; if (index < 0) { continue; } try { var cell = row.GetCell(index); var propertyType = column.Attribute.PropertyUnderlyingType ?? column.Attribute.Property?.PropertyType; if (!MapHelper.TryGetCellValue(cell, propertyType, out object valueObj)) { ColumnFailed(column, "CellType is not supported yet!"); continue; } valueObj = column.RefreshAndGetValue(valueObj); if (column.Attribute.TryTake != null) { if (!column.Attribute.TryTake(column, target)) { ColumnFailed(column, "Returned failure by custom cell resolver!"); } } else if (propertyType != null) { // Change types between IConvertible objects, such as double, float, int and etc. if (MapHelper.TryConvertType(valueObj, column, out object result)) { column.Attribute.Property.SetValue(target, result); } else { ColumnFailed(column, "Cannot convert value to the property type!"); } //var value = Convert.ChangeType(valueObj, column.Attribute.PropertyUnderlyingType ?? propertyType); } } catch (Exception e) { ColumnFailed(column, e.Message); } } rowInfo.ErrorColumnIndex = errorIndex; rowInfo.ErrorMessage = errorMessage; }
private IRow PopulateFirstRow(ISheet sheet, List <ColumnInfo> columns, Type type) { var row = sheet.CreateRow(sheet.FirstRowNum); // Use existing column populate the first row. if (columns != null) { foreach (var column in columns) { var cell = row.CreateCell(column.Attribute.Index); if (!HasHeader) { continue; } SetCell(cell, column.Attribute.Name ?? column.HeaderValue, column, true); } return(row); } // If no column cached, populate the first row with attributes and object properties. MapHelper.LoadAttributes(Attributes, type); var attributes = Attributes.Where(p => p.Value.Property != null && p.Value.Property.ReflectedType == type); var properties = new List <PropertyInfo>(type.GetProperties(MapHelper.BindingFlag)); // Firstly populate for those have Attribute specified. foreach (var pair in attributes) { var pi = pair.Key; var attribute = pair.Value; if (pair.Value.Index < 0) { continue; } var cell = row.CreateCell(attribute.Index); if (HasHeader) { cell.SetCellValue(attribute.Name ?? pi.Name); } properties.Remove(pair.Key); // Remove populated property. } var index = 0; // Then populate for those do not have Attribute specified. foreach (var pi in properties) { var attribute = Attributes.ContainsKey(pi) ? Attributes[pi] : null; if (attribute?.Ignored == true) { continue; } while (row.GetCell(index) != null) { index++; } var cell = row.CreateCell(index); if (HasHeader) { cell.SetCellValue(attribute?.Name ?? pi.Name); } else { new ColumnAttribute { Index = index, Property = pi }.MergeTo(Attributes); } index++; } return(row); }