代码之家 › 专栏 › 技术社区 › kaka1234

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

epplus excel c#

kaka1234 · 技术社区 · 7 年前

我正在.net核心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将给出我。是否可以检查整行,如果为空则继续下一行。

2 回复 | 直到 7 年前

Ernie S 7 年前

如果不知道要检查的列数,可以利用 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

David Petric reza.cse08 6 年前

你可以在 for

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

B--rian Optider 6 年前

你可以使用 worksheet.Cells[row, col].count()