C# EPPlus OpenXML count rows
Solution 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.
Solution 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;
}
Solution 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;
}
Solution 4:
Quite easy with:
private int GetDimensionRows(ExcelWorksheet sheet)
{
var startRow = sheet.Dimension.Start.Row;
var endRow = sheet.Dimension.End.Row;
return endRow - startRow;
}