EPPlus: Find if the entire row is empty in Excel

Question

I am using EPPlus library in my .net core web api. In the said method I want to validate he uploaded excel. I want to find out if my entire row is empty. I have the following code:

using (ExcelPackage package = new ExcelPackage(file.OpenReadStream()))
{
    ExcelWorksheet worksheet = package.Workbook.Worksheets[1];
    int rowCount = worksheet.Dimension.End.Row;
    int colCount = worksheet.Dimension.End.Column;

    //loop through rows and columns
    for (int row = 1; row <= rowCount; row++)
    {
        for (int col = 1; col <= ColCount; col++)
        {
            var rowValue = worksheet.Cells[row, col].Value;
            //Want to find here if the entire row is empty
        }
    }
}

rowValue above would give me if the particular cell if empty or not. Is it possible to check for the entire row and proceed to next row if empty.

Philanthropy

Answer 1

you can check the row cell range value with linq:

var startRow = 1;
var endRow = 1;
var columnStart = 1;
var columnEnd = worksheet.Cells.End.Column;

var cellRange = worksheet.Cells[startRow, columnStart , endRow, columnEnd];

var isRowEmpty = cellRange.All(c => c.Value == null)

Pharisee

Thank you for this code snippet, which might provide some limited, immediate help. A proper explanation would greatly improve its long-term value by showing why this is a good solution to the problem and would make it more useful to future readers with other, similar questions. Please edit your answer to add some explanation, including the assumptions you’ve made. – Rigamarole

Answer 2

You can set a bool in the for loop at row level. Then loop all the cells and change the bool when a cell is not empty.

//loop through rows and columns
for (int row = 1; row <= rowCount; row++)
{
    //create a bool
    bool RowIsEmpty = true;

    for (int col = 1; col <= colCount; col++)
    {
        //check if the cell is empty or not
        if (worksheet.Cells[row, col].Value != null)
        {
            RowIsEmpty = false;
        }
    }

    //display result
    if (RowIsEmpty)
    {
        Label1.Text += "Row " + row + " is empty.<br>";
    }
}

Dispart

This works. But it takes a considerable amount of time to process when file has more than 100 rows and 10 columns. – Dane

Answer 3

If you do not know the number of columns to check you can take advantage of the fact that the Worksheet.Cells collection only contains entries for cells that actually have values:

[TestMethod]
public void EmptyRowsTest()
{
    //Throw in some data
    var datatable = new DataTable("tblData");
    datatable.Columns.AddRange(new[] { new DataColumn("Col1", typeof(int)), new DataColumn("Col2", typeof(int)), new DataColumn("Col3", typeof(object)) });

    //Only fille every other row
    for (var i = 0; i < 10; i++)
    {
        var row = datatable.NewRow();
        if (i % 2 > 0)
        {
            row[0] = i;
            row[1] = i * 10;
            row[2] = Path.GetRandomFileName();
        }
        datatable.Rows.Add(row);
    }

    //Create a test file
    var existingFile = new FileInfo(@"c:\temp\EmptyRowsTest.xlsx");
    if (existingFile.Exists)
        existingFile.Delete();

    using (var pck = new ExcelPackage(existingFile))
    {
        var worksheet = pck.Workbook.Worksheets.Add("Sheet1");
        worksheet.Cells.LoadFromDataTable(datatable, true);

        pck.Save();
    }

    //Load from file
    using (var pck = new ExcelPackage(existingFile))
    {
        var worksheet = pck.Workbook.Worksheets["Sheet1"];

        //Cells only contains references to cells with actual data
        var cells = worksheet.Cells;
        var rowIndicies = cells
            .Select(c => c.Start.Row)
            .Distinct()
            .ToList();

        //Skip the header row which was added by LoadFromDataTable
        for (var i = 1; i <= 10; i++)
            Console.WriteLine($"Row {i} is empty: {rowIndicies.Contains(i)}");
    }
}

Gives this in the output (Row 0 is the column headers):

Row 1 is empty: True
Row 2 is empty: False
Row 3 is empty: True
Row 4 is empty: False
Row 5 is empty: True
Row 6 is empty: False
Row 7 is empty: True
Row 8 is empty: False
Row 9 is empty: True
Row 10 is empty: False

Naphthol

Just tried this, found it isn't true. Unless it also uses cell formatting data to validate this, I'm getting 400 rows where manual entry only occurs in 2 – Rowlock

@CaptainPrinny Did you confirm those other 398 rows are truly "empty"? Meaning, do any of the cells have, say, empty strings or some other kind of null value? This can be seen in Excel if you do a ctrl+end - if it lands at the 400th row you have white space somewhere. – Naphthol

Answer 4

You can use the worksheet.Cells[row, col].count() method for this. If the row is empty then this method will return 0.

Engage

This just gives you one single cell, with count either 0 or 1. You need the cells[fromRow, fromCol, toRow, toCol] addressing format which gives you a range of cells. – Loferski