A new 2D Range class

Aug 14, 2012 at 12:41 AM

I found POI's manner of accessing named ranges in Excle to be somewhat clunky. I am more familiar with VSTO's Range objects and so I whipped up a quick reading implementation for named Ranges and share it here for you in case it's of benefit. To make the Ramnge class writeable would take a little more work I haven't time for right now, it's in C# and again if I had time and could be bothered I'd probably make it an extension of the existing XSSFWorkbook class for a tidier implementation. Main thing is I have a 2D Range object now to work with and I'm only reading at present anyhow.

using System;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
using NPOI.SS.UserModel;

namespace NPOI
{
    class Helpers
    {
        public class Range
        {
            private XSSFWorkbook _WB;
            private string _RefersToFormula;
            private AreaReference _AR;
            private CellReference[] _CR;
            private ICell[,] _Values;
            private int _R1;
            private int _C1;

            public string RefersToFormula
            {
                get { return _RefersToFormula; }
                set
                {
                    _RefersToFormula = value;
                    _AR = new AreaReference(_RefersToFormula);
                    _CR = _AR.GetAllReferencedCells();

                    int Rmin = int.MaxValue;
                    int Rmax = int.MinValue;
                    int Cmin = int.MaxValue;
                    int Cmax = int.MinValue;
                    for (int i = 0; i < _CR.Length; i++)
                    {
                        int R = _CR[i].Row;
                        int C = _CR[i].Col;
                        if (R < Rmin) Rmin = R;
                        if (R > Rmax) Rmax = R;
                        if (C < Cmin) Cmin = C;
                        if (C > Cmax) Cmax = C;
                    }

                    _R1 = Rmin;
                    _C1 = Cmin;

                    int rows = Rmax - Rmin + 1;
                    int cols = Cmax - Cmin + 1;

                    _Values = new ICell[rows, cols];

                    for (int i = 0; i < _CR.Length; i++)
                    {
                        string S = _CR[i].SheetName;
                        int R = _CR[i].Row;
                        int C = _CR[i].Col;

                        _Values[R - _R1, C - _C1] = _WB.GetSheet(S).GetRow(R).GetCell(C);
                    }
                }
            }

            public ICell this[int index1, int index2]
            {
                get
                {
                    return _Values[index1, index2];
                }
            }

            public int Rows { get { return _Values.GetLength(0); } }
            public int Cols { get { return _Values.GetLength(1); } }

            public Range(XSSFWorkbook WB, string NameOrFormula)
            {
                _WB = WB;
                IName Range = WB.GetName(NameOrFormula);
                if (Range == null)
                    RefersToFormula = NameOrFormula;
                else
                    RefersToFormula = Range.RefersToFormula;
            }
        }
    }
}