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?
-
1Daniel, I think you marked the wrong comment as the answer.Francois Botha– Francois Botha2018-01-02 13:33:43 +00:00Commented Jan 2, 2018 at 13:33
Add a comment
|
5 Answers
The LastRowUsed() function returns a row object. Use
worksheet.LastRowUsed().RowNumber()
to get the number of the last row used.
Comments
You could use RowsUsed();
var wb = new XLWorkbook();
var ws = wb.worksheets.add("data");
var totalRows = ws.RowsUsed().Count();
4 Comments
bird
And then from there you can figure out which row is the last row.
Francois Botha
Other answers are better.
Bryan Adams
This doesn't work as expected when there are hidden rows. @Raidri answer is the perfect answer for this question.
Bolle
This is not dangerous with hidden rows only, but also when there are empty rows as well. I tried this first and wondered why the end was cut off sometimes. Any row without a used cell will not count. Yes, .LastRowUsed().RowNumber() does a much better job.
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()
1 Comment
Matt Roy
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;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
1 Comment
FluidMechanics Potential Flows
These two are not equivalent, see github.com/ClosedXML/ClosedXML/issues/2604
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();
}
2 Comments
Korashen
Please avoid posting code only answers. Please add some explenation to your code, what it does and why you use this code.
José Gavilán Huaquifil
Recorre la columna "i", desde la fila 7 hasta la ultima celda usada y en cada una agrega la posición en curso.