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.

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)

Answer 2

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