EPPlus：在 Excel 中查找整行是否为空

Question

我在我的 .net core web api 中使用 EPPlus 库。在上述方法中，我想验证他上传的 excel。我想知道我的整行是否为空。我有以下代码：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 会给我。是否可以检查整行并在为空时继续下一行。

Answer 1

您可以for在行级别的循环中设置 bool 。然后循环所有单元格并在单元格不为空时更改布尔值。

//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>";

    }

}

Answer 2

您可以使用 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 3

如果您不知道要检查的列数，则可以利用以下事实：Worksheet.Cells集合仅包含实际具有值的单元格的条目：

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

    }

}

在输出中给出这个（第 0 行是列标题）：

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