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