Simplify a list of excel cells to create a Range

Question

I want to create a Excel.Range object from a list of cell references which are dynamically generated.

Excel.Range outputRange = sheet.get_Range(strCellRange, Type.Missing);

Since the strCellRange can get quite large, it gives com exceptions. Therefore I want to simplify it to have range notation with union.

e.g.

string strCellRange = "F2,G2,H2,I2,J2,K2,L2,F7,G7,H7,I7,J7,K7,L7,F12,G12,H12,I12,J12,K12,L12,F17,G17,H17,I17,J17,K17,L17,F22,G22,H22,I22,J22,K22,L22,F27,G27,H27,I27,J27,K27,L27";

to

string strCellRange = "F2:L2,F7:L7,F12:L12,F17:L17,F22:L22,F27:L27";

1. Is there any Excel method to create a Range object with lot of cell references?
2. Is there a known algorithm to achieve above simplification (a matrix algorithm)?

Answer 1

Gayan,

In VBA you could force your direct string to a range with

Sub Test()
Dim rng1 As Range
Dim strCellRange As String
strCellRange = "F2,G2,H2,I2,J2,K2,L2,F7,G7,H7,I7,J7,K7,L7,F12,G12,H12,I12,J12,K12,L12,F17,G17,H17,I17,J17,K17,L17,F22,G22,H22,I22,J22,K22,L22,F27,G27,H27,I27,J27,K27,L27"
Set rng1 = Range(strCellRange)
Set rng1 = Union(rng1, rng1)
Debug.Print rng1.Address
End Sub

Answer 2

VBA

Function Unionize(src As Range) As Range
Dim cell As Range
Dim unionizedRange As Range

For Each cell In src
    If unionizedRange Is Nothing Then
        Set unionizedRange = cell
    Else
        Set unionizedRange = Union(unionizedRange, cell)
    End If
Next

Set Unionize = unionizedRange
End Function

c# (rough cut, didn't run it through compiler for syntax)

Excel.Range Unionize(Excel.Range src)
{
    Excel.Range unionizedRange;

    foreach (Excel.Range cell in src)
    {
        if (unionizedRange == null)
        {
            unionizedRange = cell;
        }
        Else
        {
            unionizedRange = Application.Union(unionizedRange, cell);
        }
    }
    return unionizedRange;
}

EDIT: Based on @brettdj's solution

Excel.Range outputRange = sheet.get_Range(strCellRange, Type.Missing);
strCellRange = Application.Union(outputRange, outputRange).Address(false, false);

Answer 3

This could be a starting point (it doesn't work for columns beyond Z, and doesn't identify rectangles):

    private string CompactRangeStringByRows(string strCellRange)
    {
        SortedDictionary<int, SortedList<char, char>> rows = new SortedDictionary<int, SortedList<char, char>>();
        foreach (string aCell in strCellRange.Split(new Char[] { ',' }))
        {
            char col = aCell[0];
            int row = int.Parse(aCell.Substring(1, aCell.Length - 1));
            SortedList<char, char> cols;
            if (!rows.TryGetValue(row, out cols))
            {
                cols = new SortedList<char, char>();
                rows[row] = cols;
            }
            cols.Add(col, col);
        }
        StringBuilder sb = new StringBuilder();
        bool first = true;
        foreach (KeyValuePair<int, SortedList<char, char>> rowCols in rows)
        {
            char minCol = '0';
            char maxCol = '0';
            foreach (char col in rowCols.Value.Keys)
            {
                if (minCol == '0')
                {
                    minCol = col;
                    maxCol = col;
                }
                else
                {
                    if (col == maxCol + 1)
                        maxCol = col;
                    else
                    {
                        AddRangeString(sb, first, rowCols.Key, minCol, maxCol);
                        minCol = col;
                        maxCol = col;
                        first = false;
                    }
                }
            }
            AddRangeString(sb, first, rowCols.Key, minCol, maxCol);
            first = false;
        }
        return sb.ToString();
    }

    private void AddRangeString(StringBuilder sb, bool first, int row, char minCol, char maxCol)
    {
        if (!first)
            sb.Append(',');
        sb.Append(minCol);
        sb.Append(row);
        if (maxCol != minCol)
        {
            sb.Append(':');
            sb.Append(maxCol);
            sb.Append(row);
        }
    }

Answer 4

First have some classes holding references...

public class CellRef : IEquatable<CellRef>
{
    public int Row { get; private set; }
    public int Col { get; private set; }

    // some more code...
}

public class CellRange : IEquatable<CellRange>
{
    public CellRef Start { get; private set; }
    public CellRef Stop { get; private set; }

    // some more code...
}

Then the algorithm and method... The list of cells need to be put in a List and sorted before passing to this method.

    public static string GetSimplifiedRangeString(List<CellRef> cellList)
    {
        #region Column wise simplify (identify lines)

        Dictionary<CellRef, CellRef> rowRanges = new Dictionary<CellRef, CellRef>(new CellRefEqualityComparer());

        int currentRangeStart = 0;
        for (int currentRangeStop = 0; currentRangeStop < cellList.Count; currentRangeStop++)
        {
            CellRef currentCell = cellList[currentRangeStop];
            CellRef previousCell = (currentRangeStop == 0) ? null : cellList[currentRangeStop - 1];

            bool cont = IsContigousX(currentCell, previousCell);

            if (!cont)
            {
                currentRangeStart = currentRangeStop;
            }

            if (!rowRanges.ContainsKey(cellList[currentRangeStart]))
                rowRanges.Add(cellList[currentRangeStart], cellList[currentRangeStop]);
            else
                rowRanges[cellList[currentRangeStart]] = cellList[currentRangeStop];
        }

        #endregion


        #region Row wise simplify (identify rectangles)

        List<CellRange> rangeList = new List<CellRange>();
        foreach (KeyValuePair<CellRef, CellRef> range in rowRanges)
        {
            rangeList.Add(new CellRange(range.Key, range.Value));                
        }            
        Dictionary<CellRange, CellRange> colRanges = new Dictionary<CellRange, CellRange>(new CellRangeEqualityComparer()); 

        currentRangeStart = 0;
        for (int currentRangeStop = 0; currentRangeStop < rangeList.Count; currentRangeStop++)
        {
            CellRange currentCellRange = rangeList[currentRangeStop];
            CellRange previousCellRange = (currentRangeStop == 0) ? null : rangeList[currentRangeStop - 1];

            bool cont = IsContigousY(currentCellRange, previousCellRange);

            if (!cont)
            {
                currentRangeStart = currentRangeStop;
            }

            if (!colRanges.ContainsKey(rangeList[currentRangeStart]))
                colRanges.Add(rangeList[currentRangeStart], rangeList[currentRangeStop]);
            else
                colRanges[rangeList[currentRangeStart]] = rangeList[currentRangeStop];
        }

        #endregion


        #region Simplify ranges (identify atomic lines and rectangles)

        StringBuilder retStr = new StringBuilder();
        foreach (KeyValuePair<CellRange, CellRange> ranges in colRanges)
        {
            string rangePart = string.Empty;
            if (ranges.Key.Equals(ranges.Value))
            {
                if (ranges.Key.Start.Equals(ranges.Key.Stop))
                {
                    rangePart = ranges.Key.Start.ToString();
                }
                else
                {
                    rangePart = ranges.Key.ToString();
                }
            }
            else
            {
                rangePart = new CellRange(ranges.Key.Start, ranges.Value.Stop).ToString();
            }

            if (retStr.Length == 0)
            {
                retStr.Append(rangePart);
            }
            else
            {
                retStr.Append("," + rangePart);
            }
        }

        return retStr.ToString();

        #endregion
    }

    /// <summary>
    /// Checks whether the given two cells represent a line.
    /// </summary>
    /// <param name="currentCell">Line start</param>
    /// <param name="previousCell">Line end</param>
    /// <returns></returns>
    private static bool IsContigousX(CellRef currentCell, CellRef previousCell)
    {
        if (previousCell == null)
            return false;
        return (currentCell.Row == previousCell.Row) && (currentCell.Col == (previousCell.Col + 1));
    }

    /// <summary>
    /// Checks whether the given two cells represents a rectangle.
    /// </summary>
    /// <param name="currentCellRange">Top-left cell</param>
    /// <param name="previousCellRange">Bottom-right cell</param>
    /// <returns></returns>
    private static bool IsContigousY(CellRange currentCellRange, CellRange previousCellRange)
    {
        if (previousCellRange == null)
            return false;

        bool sameVertically = (currentCellRange.Start.Col == previousCellRange.Start.Col) && (currentCellRange.Stop.Col == previousCellRange.Stop.Col);
        bool contigous = (currentCellRange.Start.Row == currentCellRange.Stop.Row) && (previousCellRange.Start.Row == previousCellRange.Stop.Row) && ((previousCellRange.Stop.Row + 1) == currentCellRange.Stop.Row);
        return sameVertically && contigous;
    }

Hope this helps someone.

Answer 5

I dont think there is any method to retrieve long range of cell references. I would prefer taking complete sheet and then navigating with in it through codings.