C# EPPlus OpenXML count rows

Question

With EPPlus and OpenXML does anyone know the syntax on how to count the rows?

Say my worksheet is called "worksheet"

int numberRows = worksheet.rows.count()? or worksheet.rows.dimension

I'm certainly interested in the answer, but how to find the answer would be cool to, like "Go to definition" and look for this or that, etc.

nice post .. +1 .. Do you happen to have any good documentation on EPPlus? — JustBeingHelpful, Feb 13 '12 at 21:57
I don't, but the examples contained in the download were helpful. — rd42, Feb 14 '12 at 16:02
There are more efficient ways to do this now using "Rows" and "Columns" properties from the DImension. — Roland Schaer, Oct 14 '16 at 16:24

score 92 · Accepted Answer · edited Apr 18 '19 at 16:57

92

With a worksheet object called worksheet, worksheet.Dimension.Start.Row and worksheet.Dimension.End.Row should give you the information you need.

worksheet.Dimension.Address will give you a string containing the worksheet dimensions in the traditional Excel range format (e.g. 'A1:I5' for rows 1-5, columns 1-9).

There is a documentation file available. In many cases it might be just as quick to play around with the library and find the answer that way. EPPlus seems to be well designed - everything seems to be logically named, at least.

edited Apr 18 '19 at 16:57

Saikat

14,222
20
104
125

answered Jan 24 '11 at 13:09

Quppa

1,841
20
18

5

+1 for correct answer with link, but I do not agree with your comment about "EPPlus seems to be well designed". I tried using it myself and the API is straightforward, but many sample xlsx files I tried from various individual generated an NullReferenceException inside the library. I stepped through the library to identify what was causing the problem, and a lot of assumptions are made that cause reading valid *.xlsx generated by Excel to fail to load. I'd view EPPlus as a ticking time bomb in your code if you're reading user-supplied files. – Samuel Neff Jan 24 '11 at 13:55
2

Fair enough - I must admit I've never used the library for reading existing spreadsheets, just writing new ones. For the latter purpose EPPlus has served me well. The project seems to be under active development, so let's hope more bugs are ironed out in future releases. – Quppa Jan 24 '11 at 14:05
1

I've never had a problem reading existing XLS/XLSX files with EPPlus. Maybe you used a beta version. – Johnny DropTables Jun 17 '14 at 08:14
What about using WorkSheet.Dimensions.Rows? – MGE Jun 24 '16 at 14:01

Kwex · Answer 2 · 2012-10-19T10:32:41.877

Thanks for that tip Quppa. I used it in my bid to populate a DataTable from a Workbook Spreadsheet as below:

    /// <summary>
    /// Converts a Worksheet to a DataTable
    /// </summary>
    /// <param name="worksheet"></param>
    /// <returns></returns>
    private static DataTable WorksheetToDataTable(ExcelWorksheet worksheet)
    {
        // Vars
        var dt = new DataTable();
        var rowCnt = worksheet.Dimension.End.Row;
        var colCnt = worksheet.Dimension.End.Column + 1;

        // Loop through Columns
        for (var c = 1; c < colCnt; c++ )
        {
            // Add Column
            dt.Columns.Add(new DataColumn());

            // Loop through Rows
            for(var r = 1; r < rowCnt; r++ )
            {
                // Add Row
                if (dt.Rows.Count < (rowCnt-1)) dt.Rows.Add(dt.NewRow());

                // Populate Row
                dt.Rows[r - 1][c - 1] = worksheet.Cells[r, c];
            }
        }

        // Return
        return dt;
    }

What about using WorkSheet.Dimensions.Rows? – MGE Jun 24 '16 at 14:01 — MGE, Jun 24 '16 at 14:01

score 6 · Answer 3 · answered Oct 14 '16 at 16:23

I am working with version 4.1 and it looks like they have added some properties (mentioned in comments from previous answers) to make this easier.

string Filepath = "c:\excelfile.xlsx";
FileInfo importFileInfo = new FileInfo(FilePath);
using(var excelPackage = new ExcelPackage(importFileInfo))
{
    ExcelWorksheet worksheet = excelPackage.Workbook.Worksheets[1];
    int rowCount = worksheet.Dimension.Rows;
    int colCount = worksheet.Dimension.Columns;
}

score 3 · Answer 4 · answered Jun 28 '18 at 12:04

3

Quite easy with:

private int GetDimensionRows(ExcelWorksheet sheet)
{
    var startRow = sheet.Dimension.Start.Row;
    var endRow = sheet.Dimension.End.Row;
    return endRow - startRow;
}

answered Jun 28 '18 at 12:04

SiL3NC3

690
6
29

C# EPPlus OpenXML count rows

4 Answers4

Linked