C#中ExcelPackage读取空行问题：如何获取有效行数？

Hey there! I’ve dealt with this exact frustration before—EPPlus’s Dimension.Rows can be unreliable after deleting rows because Excel often keeps old metadata marking those "empty" rows as part of the used range. Let’s get you the actual count of valid rows.

Why This Happens #

When you delete rows in Excel, the underlying file doesn’t always immediately update its "used range" metadata. EPPlus uses this metadata for the Dimension property, so it still counts those now-empty rows as part of the dataset. We need to bypass this and find the last row that actually contains data.

Solution 1: Manually Find the Last Used Row (Works for All EPPlus Versions) #

We’ll loop from the bottom up until we hit the first row with non-empty content. This is reliable and works regardless of your EPPlus version:

var package = new ExcelPackage(new FileInfo(filename));
var worksheet = package.Workbook.Worksheets[1];

int actualRowCount = 0;
int totalReportedRows = worksheet.Dimension?.Rows ?? 0;
int totalColumns = worksheet.Dimension?.Columns ?? 0;

// Start from the last row Excel reports and move up
for (int row = totalReportedRows; row >= 1; row--)
{
    // Check all cells in the row for non-empty content
    var rowCells = worksheet.Cells[row, 1, row, totalColumns];
    if (rowCells.Any(cell => !string.IsNullOrEmpty(cell.Text?.Trim())))
    {
        actualRowCount = row;
        break;
    }
}

// actualRowCount will be 5 in your case

Solution 2: Use GetUsedRange() (EPPlus 5.x+) #

If you’re using a newer version of EPPlus (5.0 and above), there’s a built-in method to get the actual used range which ignores empty rows:

var package = new ExcelPackage(new FileInfo(filename));
var worksheet = package.Workbook.Worksheets[1];

var usedRange = worksheet.Cells.GetUsedRange();
int actualRowCount = usedRange.End.Row;

Notes for Edge Cases #

• If your sheet has cells with formulas that return empty strings, adjust the check to use cell.Value instead of cell.Text (since Text will show the empty string result, but Value might hold the formula itself).
• For hidden rows: If you want to include hidden rows with data, the above methods will still count them. If you need to exclude hidden rows, add a check for worksheet.Row(row).Hidden == false in the loop.

内容的提问来源于stack exchange，提问作者Janani