/// <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));
}
public RangeReference(RangeReference r)
{
_topLeft = new CellReference(r._topLeft);
_bottomRight = new CellReference(r._bottomRight);
}
/// <summary>
/// Parse range from string expression
/// </summary>
/// <param name="reference"></param>
/// <param name="topLeft"></param>
/// <param name="bottomRight"></param>
/// <param name="boundary">Boundary range used when the range reference is open</param>
public static void ParseRange(String reference, ref CellReference topLeft, ref CellReference bottomRight, RangeReference boundary = null)
{
var matchRange = _RegexRange.Match(reference);
if (topLeft == null)
{
topLeft = new CellReference();
}
if (bottomRight == null)
{
bottomRight = new CellReference();
}
if (matchRange.Success)
{ // It's a range
var col1 = matchRange.Groups["COL1"].Value;
var col2 = matchRange.Groups["COL2"].Value;
var row1 = matchRange.Groups["ROW1"].Value;
var row2 = matchRange.Groups["ROW2"].Value;
if (boundary != null)
{ // Expand to boundary if range is open
if (String.IsNullOrEmpty(col1))
{
col1 = boundary._topLeft.ColumnName;
}
if (String.IsNullOrEmpty(col2))
{
col2 = boundary._bottomRight.ColumnName;
}
if (String.IsNullOrEmpty(row1))
{
row1 = boundary._topLeft.Row.ToString(CultureInfo.InvariantCulture);
}
if (String.IsNullOrEmpty(row2))
{
row2 = boundary._bottomRight.Row.ToString(CultureInfo.InvariantCulture);
}
}
var cell1 = new CellReference(col1 + row1);
var cell2 = new CellReference(col2 + row2);
int left = cell1.Col <= cell2.Col ? cell1.Col : cell2.Col;
int right = left == cell1.Col ? cell2.Col : cell1.Col;
int top = cell1.Row <= cell2.Row ? cell1.Row : cell2.Row;
int bottom = top == cell1.Row ? cell2.Row : cell1.Row;
topLeft.Col = left;
topLeft.Row = top;
bottomRight.Col = right;
bottomRight.Row = bottom;
}
else
{
var matchCell = _RegexCell.Match(reference);
if (matchCell.Success && matchCell.Length == reference.Trim().Length)
{ // It's a cell
topLeft.Set(reference);
bottomRight.Set(reference);
}
else
{
throw new InvalidDataException(String.Format("Range reference {0} is invalid!", reference));
}
}
}
/// <summary>
/// Initialize a range with range reference
/// </summary>
/// <param name="doc">Excel document</param>
/// <param name="sheet">Worksheet</param>
/// <param name="range">Range reference</param>
public ExcelOpenXMLRange(SpreadsheetDocument doc, Worksheet sheet, RangeReference range)
: base(range)
{
_doc = doc;
_sheet = sheet;
}