This project has moved. For the latest updates, please go here.

Reading the Used Range

Mar 9, 2016 at 8:59 PM
After I open the Excel spreadsheet is there a way to determine the USED Cell Range like there is in the Excel Interop tools?
May 29, 2016 at 3:30 AM
A straightforward approach is to base on the first start cell & the last end cell.
Hope it helps :)
using (ExcelPackage package = new ExcelPackage(new FileInfo(file)))
                {
                   var cells = package.Workbook.Worksheets[1].Cells;
                   var start = cells.First().Start; // find first initialized cell
                   var end = cells.Last().End; // find the last initialized cell
                   int startRow = start.Row;
                   int startColumn = start.Column;
                   int endRow = end.Row;
                   int endColumn = end.Column;

                   List<MyModel> models = new List<MyModel>(endRow - startRow);
                   int curRowIndex = startRow;
                   MyModel model = new MyModel();
                   foreach (var cell in cells)
                   {
                       if (cell.Start.Row != curRowIndex) // difference row => requires a new instance of MyModel
                       {
                           model = new MyModel();
                           curRowIndex = cell.Start.Row;
                           models.Add(model);
                       }
                       PopulateModel(model, cell);
                   }
                }

private void PopulateModel(MyModel model, ExcelRangeBase cell)
        {
            switch(cell.Start.Column)
            {
                case 1:
                    {
                        model.Id = Convert.ToInt64(cell.Value);
                        break;
                    }
                case 2:
                    {
                        model.Name = Convert.ToString(cell.Value);
                    }
                    //
                    //
            }
        }
Jul 8, 2016 at 8:39 AM
Sadly, that method won't work, if you have blanked cells.
Say, that you have a worksheet containing 10 rows of data - from 1 to 10. In your code, startRow will equal 1 and endRow equals 10.
If you then delete (using the delete key, so i guess the correct term is "blank the content") the 8 last row in the worksheet and run the code again, they you will still see, that endRow contains the value 10.
If you mark the 8 columns, right click and choose delete, then endRow contains the expected value 2 and not 10.

It is an annoying "feature" of Excel. I would really like to know the dimensions of the sheet, that contains REAL data, and not just formatted cells
Jul 13, 2016 at 12:18 AM
Edited Jul 13, 2016 at 12:18 AM
Hi,
It was just a way to loop through the used range of cells.
In your case (blanked cells), the main magic should be done inside PopulateModel to ignore or specially handle blank row(s)/cell(s)

I'm not saying that the method can handle all scenarios but for case by case we can add some tricks :)

Cheers,
Doanh
Sep 8, 2016 at 5:24 PM
You should also consider Worksheet.Dimension. Its Start and End properties get the top left and bottom right cells used on a worksheet. See this discussion: http://epplus.codeplex.com/discussions/257072.