Epplus delete all rows from specific row
I know that it is old but I could not find any solution so made one my by own. It is checking the last row if it is empty and if yes it deletes it and doing this until finds non-empty row. (non-empty means here: all columns in this row have some value)
worksheet.TrimLastEmptyRows();
public static void TrimLastEmptyRows(this ExcelWorksheet worksheet)
{
while (worksheet.IsLastRowEmpty())
worksheet.DeleteRow(worksheet.Dimension.End.Row);
}
public static bool IsLastRowEmpty(this ExcelWorksheet worksheet)
{
var empties = new List<bool>();
for (int i = 1; i <= worksheet.Dimension.End.Column; i++)
{
var rowEmpty = worksheet.Cells[worksheet.Dimension.End.Row, i].Value == null ? true : false;
empties.Add(rowEmpty);
}
return empties.All(e => e);
}
Above solution is to delete last empty rows in the file. This will not work if file has empty rows in the middle of the rows list somewhere.
Below is the solution to identify the empty rows in the middle of the rows list.
I used combination of both above and mine to delete empty rows at the end of the rows list and empty rows in the middle of the rows list
private void TrimEmptyRows(ExcelWorksheet worksheet)
{
//loop all rows in a file
for (int i = worksheet.Dimension.Start.Row; i <=
worksheet.Dimension.End.Row; i++)
{
bool isRowEmpty = true;
//loop all columns in a row
for (int j = worksheet.Dimension.Start.Column; j <= worksheet.Dimension.End.Column; j++)
{
if (worksheet.Cells[i, j].Value != null)
{
isRowEmpty = false;
break;
}
}
if (isRowEmpty)
{
worksheet.DeleteRow(i);
}
}
}