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