ClosedXML find last row number

Question

I'm using ClosedXML with C# to modify an Excel workbook. I need to find the last row number used but .RowCount() counts how many rows are in the worksheet. So it is returning 1 million rows when there are only a few thousand. I have tried LastRowUsed() but that doesn't return an int and there is no .row method for it. How can I get the last row in an int?

Daniel, I think you marked the wrong comment as the answer. – Francois Botha Jan 02 '18 at 13:33 — Francois Botha, Jan 02 '18 at 13:33

score 33 · Answer 1 · answered Feb 01 '17 at 13:12

33

The LastRowUsed() function returns a row object. Use

worksheet.LastRowUsed().RowNumber()

to get the number of the last row used.

answered Feb 01 '17 at 13:12

Raidri

17,258
9
62
65

score 10 · Accepted Answer · answered Jan 31 '17 at 21:57

10

You could use RowsUsed();

var wb = new XLWorkbook();
    var ws = wb.worksheets.add("data");
    var totalRows = ws.RowsUsed().Count();

answered Jan 31 '17 at 21:57

bird

152
10

And then from there you can figure out which row is the last row. – bird Jan 31 '17 at 22:05
1

Other answers are better. – Francois Botha Feb 02 '17 at 09:10
1

This doesn't work as expected when there are hidden rows. [@Raidri](https://stackoverflow.com/users/2610249/raidri) answer is the perfect answer for this question. – Bryan Adams Jan 10 '18 at 02:13

score 5 · Answer 3 · answered Jan 31 '17 at 21:39

5

You have to use .LastRowUsed()

More useful stuff from official GitHub repo:

range.FirstCell()
range.FirstCellUsed()
range.FirstColumn()
range.FirstColumnUsed()
range.FirstRow()
range.FirstRowUsed()

range.LastCell()
range.LastCellUsed()
range.LastColumn()
range.LastColumnUsed()
range.LastRow()
range.LastRowUsed()

answered Jan 31 '17 at 21:39

ManishChristian

3,759
3
22
50

2

Just be careful not to use the functions "range.Last..." in a big loop, since they can be slow. Assign the value you need in a variable and use that variable in your loop. As in `IXLCell MyLastCellUsed = ws.LastCellUsed;` – Matt Roy Feb 25 '19 at 16:45

score 1 · Answer 4 · answered Jul 20 '22 at 17:59

I had this problem.

This example has the best performance:

XLWorkbook workbook = new XLWorkbook(FilePath);
// row count
var rowCount = workbook.Worksheet(1).LastRowUsed().RowNumber();
// column count
var columnCount = workbook.Worksheet(1).LastColumnUsed().ColumnNumber();

or

XLWorkbook workbook = new XLWorkbook(FilePath);
// row count
var rowCount = workbook.Worksheet(1).RowsUsed().Count();
// column count
var columnCount = workbook.Worksheet(1).ColumnUsed().Count();

I hope this helps you

score 0 · Answer 5 · answered Nov 15 '18 at 14:45

0

I used this code

libro = new XLWorkbook("file.xlsx");
hoja = libro.Worksheet(1);
var primeraCeldaUsada = hoja.FirstCellUsed();
var ultimaCeldaUsada = hoja.LastCellUsed();
var rango = hoja.Range(primeraCeldaUsada.Address, ultimaCeldaUsada.Address);
int ultfila = int.Parse(ultimaCeldaUsada.Address.RowNumber.ToString());
for (int i = 7; i <= ultfila; i++)
    {
    hoja.Cell(i, 3).Value = hoja.Cell(i, 3).Address.ToString();
    }

answered Nov 15 '18 at 14:45

José Gavilán Huaquifil

11

2

Please avoid posting code only answers. Please add some explenation to your code, what it does and why you use this code. – Korashen Nov 15 '18 at 14:49
Recorre la columna "i", desde la fila 7 hasta la ultima celda usada y en cada una agrega la posición en curso. – José Gavilán Huaquifil Nov 15 '18 at 17:00

ClosedXML find last row number

5 Answers5