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 columnsfor (int row = 1; row <= rowCount; row++){&nbsp; &nbsp; //create a bool&nbsp; &nbsp; bool RowIsEmpty = true;&nbsp; &nbsp; for (int col = 1; col <= colCount; col++)&nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; //check if the cell is empty or not&nbsp; &nbsp; &nbsp; &nbsp; if (worksheet.Cells[row, col].Value != null)&nbsp; &nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; RowIsEmpty = false;&nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; }&nbsp; &nbsp; //display result&nbsp; &nbsp; if (RowIsEmpty)&nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; Label1.Text += "Row " + row + " is empty.<br>";&nbsp; &nbsp; }}

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(){&nbsp; &nbsp; //Throw in some data&nbsp; &nbsp; var datatable = new DataTable("tblData");&nbsp; &nbsp; datatable.Columns.AddRange(new[] { new DataColumn("Col1", typeof(int)), new DataColumn("Col2", typeof(int)), new DataColumn("Col3", typeof(object)) });&nbsp; &nbsp; //Only fille every other row&nbsp; &nbsp; for (var i = 0; i < 10; i++)&nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; var row = datatable.NewRow();&nbsp; &nbsp; &nbsp; &nbsp; if (i % 2 > 0)&nbsp; &nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; row[0] = i;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; row[1] = i * 10;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; row[2] = Path.GetRandomFileName();&nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; datatable.Rows.Add(row);&nbsp; &nbsp; }&nbsp; &nbsp; //Create a test file&nbsp; &nbsp; var existingFile = new FileInfo(@"c:\temp\EmptyRowsTest.xlsx");&nbsp; &nbsp; if (existingFile.Exists)&nbsp; &nbsp; &nbsp; &nbsp; existingFile.Delete();&nbsp; &nbsp; using (var pck = new ExcelPackage(existingFile))&nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; var worksheet = pck.Workbook.Worksheets.Add("Sheet1");&nbsp; &nbsp; &nbsp; &nbsp; worksheet.Cells.LoadFromDataTable(datatable, true);&nbsp; &nbsp; &nbsp; &nbsp; pck.Save();&nbsp; &nbsp; }&nbsp; &nbsp; //Load from file&nbsp; &nbsp; using (var pck = new ExcelPackage(existingFile))&nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; var worksheet = pck.Workbook.Worksheets["Sheet1"];&nbsp; &nbsp; &nbsp; &nbsp; //Cells only contains references to cells with actual data&nbsp; &nbsp; &nbsp; &nbsp; var cells = worksheet.Cells;&nbsp; &nbsp; &nbsp; &nbsp; var rowIndicies = cells&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .Select(c => c.Start.Row)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .Distinct()&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .ToList();&nbsp; &nbsp; &nbsp; &nbsp; //Skip the header row which was added by LoadFromDataTable&nbsp; &nbsp; &nbsp; &nbsp; for (var i = 1; i <= 10; i++)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Console.WriteLine($"Row {i} is empty: {rowIndicies.Contains(i)}");&nbsp; &nbsp; }}在输出中给出这个（第 0 行是列标题）：Row 1 is empty: TrueRow 2 is empty: FalseRow 3 is empty: TrueRow 4 is empty: FalseRow 5 is empty: TrueRow 6 is empty: FalseRow 7 is empty: TrueRow 8 is empty: FalseRow 9 is empty: TrueRow 10 is empty: False