Epplus: Find If the Entire Row Is Empty in Excel

Find row number based on text in excel using EPPLUS

Use can use LINQ query to get your result

int rowStart = worksheet.Dimension.Start.Row; 
int rowEnd = worksheet.Dimension.End.Row;

string cellRange = rowStart.ToString() + ":" + rowEnd.ToString();

var searchCell =from cell in worksheet.Cells[cellRange] //you can define your own range of cells for lookup
                 where cell.Value.ToString() == "Total"
                 select cell.Start.Row;

int rowNum = searchCell.First();

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);
    }

Dealing with empty cells when importing Excel file using EPPlus

I thing its fine to assign a empty string i.e. string.Empty for empty cells .And if you are fine you can put it this way :

var newRecord = new DB_USER
      {
           ID = Int32.Parse(worksheet.Cells[idColumn + row].Value.ToString()),
           FIRST_NAME = worksheet.Cells[firstNameColumn + row].Value.ToString(),
           LAST_NAME = worksheet.Cells[lastNameColumn + row].Value ?? string.Empty).ToString() //for a null value assign a empty string else the string value
       };

A cleaner approach would be Extension method :

public static string ToNullSafeString(this object obj)
{
    return (obj ?? string.Empty).ToString();
}

and use it as :

LAST_NAME = worksheet.Cells[lastNameColumn + row].Value.ToNullSafeString();

still if you wish to return a null instead of string.Empty then a slight modification to ToNullSafeString extension method above will work.

---

Related Topics