18

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.

Community
  • 1
  • 1
Murthy
  • 1,502
  • 11
  • 31
  • 45

4 Answers4

63

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.

DarrenMB
  • 2,342
  • 1
  • 21
  • 26
  • 5
    From my experience just now, Dimension.End.Row includes cells that have no values. For example, if a user creates a workbook with 10,000 rows that has values then deletes values in the second half of the rows without deleting the whole row, Dimension.End.Row will still return 10,000. – Hugh Seagraves Feb 03 '16 at 22:48
  • Yep, but this is functionally equivalent to the Excel VBA Style COM+ Library for "UsedRange" which is what the Original Question referenced. See the other popular answer for the calculated version of what your talking about. – DarrenMB Feb 04 '16 at 02:37
  • 1
    Also since the definition of "Empty" is something that is very specific to each case it would be hard to have a 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) – DarrenMB Feb 04 '16 at 02:40
  • 1
    GREAT! My question was about NULL Dimension. Thank you so much. – Vladislav Feb 22 '17 at 13:45
36

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;
}
Hasala Senevirathne
  • 57
  • 4
Peter Riesz
  • 3,091
  • 29
  • 33
  • 2
    You may want to use string.IsNullOrWhiteSpace in the example above instead of string.IsNullOrEmpty to avoid rows containing cells with several whitespaces or tabs considered as non-empty. – Aleksei Mialkin Dec 17 '19 at 13:17
4

Another way to do it.

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

Menelisi
  • 41
  • 1
  • 4
1

There is a way to get the cells that don't have no value using the Where method. We can use it to find the very last cell (and its row in this case) from a worksheet, or a range.

int lastRow = sheet.Cells.Where(cell => !cell.Value.ToString().Equals("")).Last().End.Row;

About the Where method : https://msdn.microsoft.com/en-us/library/bb534803(v=vs.110).aspx?cs-save-lang=1&cs-lang=csharp#code-snippet-1

Mathieu Desjardin
  • 64
  • 4
  • This fails for me (null values not handled right) with an Excel having empty content rows at the end with borders drawn on them, while "Menelisi" answer avobe did the job. – tomasofen Oct 28 '22 at 04:07