Пример #1
0
        /// <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));
        }
Пример #2
0
 public RangeReference(RangeReference r)
 {
     _topLeft     = new CellReference(r._topLeft);
     _bottomRight = new CellReference(r._bottomRight);
 }
Пример #3
0
        /// <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));
                }
            }
        }
Пример #4
0
 /// <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;
 }