Identify last row in a worksheet and retrieve just that row

[steve-daedilus]

Hi,

Is there an easier / more efficient way to identify the last row with data in a sheet and retrieve that last row (or even cell) without retrieving the rest of the data?

Currently doing the following, however with over 100k rows, this is pretty slow. Changed identifiable variables / names etc, otherwise the same.

$lastRow = Import-Excel -Path $xlFile -WorksheetName "Sheet1" | Sort-Object -Property "DateTime" -Descending | Select-Object -First 1

I found I could catch the number of rows by the following:

$excel = Open-ExcelPackage -Path $xlFile
$excel.Sheet1.Dimension.Rows

I'm clearly overlooking how I can leverage this to avoid reading everything out of the sheet. Any pointers?

[steve-daedilus]

OK, found I could do it this way, but there's an arbitrary limit.

$lastRow = Import-Excel -Path $xlFile -WorksheetName "Sheet1" -StartRow $xlFile.Sheet1.Dimension.Rows -EndRow $xlFile.Sheet1.Dimension.Rows -NoHeader

Limit is 9999, and found this limit set in Public\Import-Excel.ps1 at Line 26.

Locally I've added some extra 9's to the upper range, and this then works instantly.

@dfinke How am I best to sort out a change to this / pull request?

[dfinke]

Moved to issues #1272