Saturday, October 19, 2013

Getting A Range of Cells With NPOI (POI)

I'm embarrassed to admit it but I write C# code. Recently I've been doing some very lightweight Excel file manipulation and with minimal research started using NPOI. I was surprised to find that there wasn't any GetRange() type of function (that I saw). So here's my implementation:

        public static ICell[,] GetRange(ISheet sheet, string range)
        {
            string[] cellStartStop = range.Split(':');

            CellReference cellRefStart = new CellReference(cellStartStop[0]);
            CellReference cellRefStop = new CellReference(cellStartStop[1]);

            ICell[,] cells = new ICell[cellRefStop.Row - cellRefStart.Row + 1, cellRefStop.Col - cellRefStart.Col + 1];

            for (int i = cellRefStart.Row; i < cellRefStop.Row + 1; i++)
            {
                IRow row = sheet.GetRow(i);
                for (int j = cellRefStart.Col; j < cellRefStop.Col + 1; j++)
                {
                    cells[i - cellRefStart.Row, j - cellRefStart.Col] = row.GetCell(j);
                }
            }

            return cells;
        }


And then an easy way to get the desired values:


        public static T1[,] GetCellValues<T1>(ICell[,] cells)
        {
            T1[,] values = new T1[cells.GetLength(0), cells.GetLength(1)];

            for (int i = 0; i < values.GetLength(0); i++)
            {
                for (int j = 0; j < values.GetLength(1); j++)
                {
                    if (typeof(T1) == typeof(double) || typeof(T1) == typeof(int) || 
                        typeof(T1) == typeof(float) || typeof(T1) == typeof(long))
                    {
                        values[i, j] = (T1)Convert.ChangeType(cells[i, j].NumericCellValue, typeof(T1));
                    }
                    else if (typeof(T1) == typeof(DateTime))
                    {
                        values[i, j] = (T1)Convert.ChangeType(cells[i, j].DateCellValue, typeof(T1));
                    }
                    else if (typeof(T1) == typeof(string))
                    {
                        values[i, j] = (T1)Convert.ChangeType(cells[i, j].StringCellValue, typeof(T1));
                    }
                }
            }

            return values;
        }



No comments:

Post a Comment