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.

Excess

nice post .. +1 .. Do you happen to have any good documentation on EPPlus? – Concomitance

I don't, but the examples contained in the download were helpful. – Excess

There are more efficient ways to do this now using "Rows" and "Columns" properties from the DImension. – Tindall

Answer 1

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.

Something

+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. – Tammeratammi

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. – Something

I've never had a problem reading existing XLS/XLSX files with EPPlus. Maybe you used a beta version. – Sequestered

What about using WorkSheet.Dimensions.Rows? – Rubie

Answer 2

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;
    }

Sashasashay

What about using WorkSheet.Dimensions.Rows? – Rubie

Answer 3

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;
}

Tindall

Answer 4

Quite easy with:

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

Bally