/// <summary> /// Initialize entity enumerator /// </summary> /// <param name="doc">Excel document</param> /// <param name="colCfg">Configuration for entity collection template</param> /// <param name="parentRange">Parent range that contains collection</param> /// <remarks> /// When parentRange is not null, all reference should be related to it. /// </remarks> public ExcelEntityEnumerator(SpreadsheetDocument doc, CollectionConfigElement colCfg, ExcelOpenXMLRange parentRange = null) { _doc = doc; _colCfg = colCfg; _parentRange = parentRange; Reset(); }
/// <summary> /// Reset enumerator, move to first entity /// </summary> public void Reset() { _currentIdx = -1; _current = null; _entityRange = null; _endBefore = null; if (!String.IsNullOrEmpty(_colCfg.EndBefore)) { // There is abort flag cell, using defined name to mark cell dynamically. _endBefore = _doc.GetRange(_colCfg.EndBefore); } }
/// <summary> /// Move to next entity /// </summary> /// <returns></returns> public bool MoveNext() { _currentIdx++; _entityRange = null; try { _current = ReadCurrent(); return(_current != null); } catch (NoMoreEntityException) { return(false); } }
/// <summary> /// Write one object to excel at current location and move to next location for next object /// </summary> /// <param name="obj"></param> public void Write(T obj) { // Calculate the entity range if (_entityRange == null) { // No entity range calculated, this is the first entity if (_collectionRange == null) { // First time, calculate collection range if (_parentRange != null) { _collectionRange = _parentRange.GetSubRange(_colCfg.Range); } else { _collectionRange = _doc.GetRange(_colCfg.Range); } if (_collectionRange == null) { throw new InvalidDataException(String.Format("Cannot find valid range for collection of {0}!", typeof(T).FullName)); } } EntityConfigElement entCfg = _colCfg.ItemTemplate.Entity; String entRef = _collectionRange.Sheet.ExpandToSheetBound(entCfg.Range); String entRange = ExcelOpenXMLHelper.CalculateEntityRange(_collectionRange, entRef, 0, _colCfg.Orientation.Equals("vertical", StringComparison.OrdinalIgnoreCase)); if (entRange == null) { throw new NoMoreEntityException(typeof(T)); } _entityRange = _collectionRange.GetSubRange(entRange); if (_entityRange == null) { throw new NoMoreEntityException(typeof(T)); } } // Write object to range _entityRange.WriteEntity(_colCfg.ItemTemplate.Entity, obj); // Move range to next if (_colCfg.Orientation.Equals("vertical", StringComparison.OrdinalIgnoreCase)) { // Move vertically int h = _entityRange.Height; _entityRange.Move(0, h); } else { // Move horizontally int w = _entityRange.Width; _entityRange.Move(w, 0); } }
/// <summary> /// Read entity at specific index /// </summary> /// <typeparam name="T"></typeparam> /// <returns></returns> protected T ReadCurrent() { // Get range of collection if (_collectionRange == null) { if (_parentRange != null) { _collectionRange = _parentRange.GetSubRange(_colCfg.Range); } else { _collectionRange = _doc.GetRange(_colCfg.Range); } if (_collectionRange == null) { throw new InvalidDataException(String.Format("Cannot find valid range for collection of {0}!", typeof(T).FullName)); } // For dynamic collection, it's possible to use a defined name to mark the end of co } // Calculate range of current entity if (_entityRange == null) { EntityConfigElement entCfg = _colCfg.ItemTemplate.Entity; String entRef = _collectionRange.Sheet.ExpandToSheetBound(entCfg.Range); String entRange = ExcelOpenXMLHelper.CalculateEntityRange(_collectionRange, entRef, _currentIdx, _colCfg.Orientation.Equals("vertical", StringComparison.OrdinalIgnoreCase), _endBefore); if (entRange == null) { throw new NoMoreEntityException(typeof(T)); } _entityRange = _collectionRange.GetSubRange(entRange); if (_entityRange == null) { throw new NoMoreEntityException(typeof(T)); } } // Read data for entity var ent = _entityRange.ReadEntity <T>(_colCfg.ItemTemplate.Entity); if (ent == null) { throw new NoMoreEntityException(typeof(T)); } return(ent); }
/// <summary> /// Calculate range of entity /// </summary> /// <param name="rangeFirst">Range in configuration</param> /// <param name="idx">Index of entity</param> /// <param name="isVertical">Whether entities arranged vertically</param> /// <returns></returns> public static String CalculateEntityRange(ExcelOpenXMLRange collectionRange, String rangeFirst, int idx, bool isVertical = true, ExcelOpenXMLRange endBefore = null) { var tl = new CellReference(); var br = new CellReference(); RangeReference.ParseRange(rangeFirst, ref tl, ref br); int height = br.Row - tl.Row + 1; int width = br.Col - tl.Col + 1; int rangeH = collectionRange.Height; int rangeW = collectionRange.Width; if (height > rangeH) { height = rangeH; // Make sure height of entity not bigger than collection } if (width > rangeW) { width = rangeW; // Make sure width of entity not bigger than collection } int c = 0, r = 0; if (isVertical) { // Entities arranged by rows int entPerRow = rangeW / width; // How many entities could be in one row int rowIdx = idx / entPerRow; int colIdx = idx % entPerRow; r = rowIdx * height; c = colIdx * width; } else { // Entities arranged by columns int entPerCol = rangeH / height; // How many entities could be in one column int colIdx = idx / entPerCol; int rowIdx = idx % entPerCol; r = rowIdx * height; c = colIdx * width; } tl.Col = c + 1; tl.Row = r + 1; // Check abort flag if (endBefore != null) { if (isVertical) { // Vertical if (tl.Row + collectionRange.Top - 1 >= endBefore.Top) { return(null); } } else { // Horizontal if (tl.Col + collectionRange.Left - 1 >= endBefore.Left) { return(null); } } } br = tl.Offset(width - 1, height - 1); return(String.Format("{0}:{1}", tl, br)); }