Finding the last used row in an excel sheet

Hello!

I tried to find the last line that has ‘someting’ in it but it does not work the way I would need it. Sometimes it works, sometimes not.

I tried this by counting empty cells. But how can I find out, that a cell is 100% empty? This is my code:

private bool isCellEmpty(Cell pCell)
{
try
{
if (pCell == null)
{
return (true);
}

if (pCell.Type == CellValueType.IsNull)
{
return (true);
}

if (pCell.Type == CellValueType.IsString)
{
if (pCell.StringValue == null)
{
return (true);
}

if (pCell.StringValue.Trim().Equals(""))
{
return (true);
}

return (false);
}

if (pCell.Type == CellValueType.IsBool)
{
return (false);
}

if (pCell.Type == CellValueType.IsNumeric)
{
if (pCell.DoubleValue != double.NaN)
{
return (false);
}
if (pCell.FloatValue != float.NaN)
{
return (false);
}

return (true);
}

if (pCell.IsFormula)
{
return (false);
}

if (pCell.Type == CellValueType.IsDateTime)
{
return (false);
}

if (pCell.Type == CellValueType.IsUnknown)
{
if (pCell.Value == null)
{
return (true);
}

return (pCell.Value.ToString().Trim().Equals(""));
}
}
catch (Exception ex)
{
ISG.Tracing.Trace.Current.Error(ex.ToString());
}

return (true);
}

I tried to check nearly everything that could be inside a cell but I suppose, that I forgot something.

WorkSheet.Cells.Rows.Count seems also not to points really to the last used row. What else can I do to find the last used row?

I need this to append some data to sheets that already contain data from user input.

best regards,

Thomas.

Hi Thomas,

I think you may use Worksheet.Cells.MaxDataRow attribute to get the index of the last row which contains data in it.

See the code segment with comments:

Workbook wb = new Workbook(“d:\test\Book1.xls”);
Worksheet ws = wb.Worksheets[0];
int colcount = ws.Cells.Columns.Count; //gives the columns count, since the columns with standard (default) settings / formatting are not included
int rowcount = ws.Cells.Rows.Count; // //gives the rows count, since all the rows with standard (default) settings / formatting are not included
int maxcol = ws.Cells.MaxColumn; //gives you the maximum data/styled column index
int maxdatacol = ws.Cells.MaxDataColumn; // //gives you the maximum data column index
int maxdatarowincol = ws.Cells.GetLastDataRow(3); //get the last data row index in D column
int maxrow = ws.Cells.MaxRow; //gives the maximum/last row index that contains data / style
int maxdatarow = ws.Cells.MaxDataRow; ////gives the maximum/last row index that contains data only


Thank you.

Hi!

Thank you very much for your help! Worksheet.Cells.MaxRow brought the success. Now it works!

best regards,

Thomas.