how to get EPPlus OpenXML row count (c#)

Question

I searched for it and found the link C# EPPlus OpenXML count rows

int iRowCount = currentWorksheet.Dimension.End.Row - currentWorksheet.Dimension.Start.Row;

But this gives a value of 4721 as count. It is giving the whole row count, how can I get row count of rows which has value. Something like UsedRange.

Answer 1

Actual Answer to return the number of Rows and Columns of the UsedRange (the dimention) of a sheet is...

int iColCnt = Worksheet.Dimension.End.Column
int iRowCnt = Worksheet.Dimension.End.Row

But you need to test if Worksheet.Dimension is null because for new worksheets or empty worksheets the Dimension property will be null.

Also since the definition of "Empty" is something that is very specific to each case it would be hard to have a generic function like that. The only one that seems to make the most sense is all values are blank. But Blank and Nothing are really different in themselves. (EG a comment in a cell could be present and that could be considered enough for a row to not be considered blank in a specific case)

See Peter Reisz answer for example of that style to find the end of your worksheet.

Answer 2

Empty cells in a worksheet may still contain formatting causing them to be counted in the sheet Dimension:

Empty cells can be cleared using the steps here: http://office.microsoft.com/en-au/excel-help/locate-and-reset-the-last-cell-on-a-worksheet-HA010218871.aspx

I wrote this function to get the last row that contains text:

int GetLastUsedRow(ExcelWorksheet sheet) {
  if (sheet.Dimension == null) {  return 0; } // In case of a blank sheet
    var row = sheet.Dimension.End.Row;
    while(row >= 1) {
        var range = sheet.Cells[row, 1, row, sheet.Dimension.End.Column];
        if(range.Any(c => !string.IsNullOrEmpty(c.Text))) {
            break;
        }
        row--;
    }
    return row;
}

Answer 3

Another way to do it.

var lastRow = sheet.Cells.Where(cell => !string.IsNullOrEmpty(cell.Value?.ToString() ?? string.Empty)).LastOrDefault().End.Row;