C#: Getting the number of rows/columns with ExcelPackage

Question

I need to read and write data from an Excel spreadsheet. Is there a method to finding out how many rows/columns a certain worksheet has using ExcelPackage? I have the following code:

FileInfo newFile = new FileInfo(@"C:\example.xlsx");
using (ExcelPackage xlPackage = new ExcelPackage(newFile)) 
{
    ExcelWorksheet worksheet = xlPackage.Workbook.Worksheets[1];
}

I need to iterate through each cell this worksheet has and spit it into a fairly big table, but I don't want to print out blank cells or get an exception. Is there a method resembling worksheet.rowNum or colNum?

Solution 1

You can get the row and column count using ExcelPackage (EPPlus.dll version 3.0.0.2) as below:

  var rowCnt = worksheet.Dimension.End.Row;
  var colCnt = worksheet.Dimension.End.Column;

Solution 2

This is what I do:

To get the array of values on the workbook:

object[,] valueArray = sheet.Cells.GetValue<object[,]>();

to get the range do the following:

int rangeMaxRows = sheet.Dimension.End.Row; 
int rangeMaxColumns = sheet.Dimension.End.Column;

Solution 3

I would start with the UsedRange property and then for each Cell in the final Row of the UsedRange do Cell.End(xlUp). This should get you the final cell for each column, the cell which has the maximum row index is the last cell in your true used range.

The UsedRange property can appear wrong because when cells are cleared but not deleted, the UsedRange property is not updated. To make the UsedRange property valid again simply select all the rows and columns after your last cell (so all the blank cells) and go edit->delete.

Solution 4

int row = _excelSheet.Rows.CurrentRegion.EntireRow.Count;
int col = _excelSheet.Columns.CurrentRegion.EntireColumn.Count;

Solution 5

I just did the following loop to solve the problem. It works fine only if you know how many columns there will be beforehand. Otherwise it would take another loop iteration.

int totalCells = 0;
int totalRows = -1;

do
{
     totalRows++;
} while (worksheet.Cell(totalRows + 1, 1).Value != @"");
totalCells = totalRows * 12;

View more solutions