如何用C# DataTable跳过≤2个空白行？校验代码失效求排查

Alright, let's break down exactly why your Validate method isn't skipping blank rows correctly, and fix it up to match your requirements perfectly.

Problem Analysis #

Your current implementation has several critical issues that break the blank row skipping and validation logic:

1. Stuck Row Counter & Mismatched Variables
   Your rowCounter starts at 7 but never increments throughout the loop—you're only ever validating the 7th row over and over! On top of that, you mix up i (the loop index for DataTable rows) and rowCounter when accessing row data (e.g., data.Rows[i][0] vs data.Rows[rowCounter][0]), which pulls entirely wrong rows for validation.

2. Broken Blank Row Skipping Logic
   The line if (!isRowEmpty(data, rowCounter-1) && isRowEmpty(data, rowCounter) && !isRowEmpty(data, rowCounter + 1)) i+=1; doesn't align with your requirement to skip blank rows. It only skips a single blank row if it's sandwiched between non-blank rows, which doesn't handle consecutive blank rows or the general case of skipping any blank row.

3. Inverted Validation Logic for Rate Type Fields
   Your checks for rate, units, and cost are backwards. Right now, you throw an error if rate type is null, but your requirement states these fields are required only when rate type is not blank.

4. Loop Range Error
   for (int i =0; i < data.Rows.Count - 1; i++) skips the last row of your DataTable entirely. You should loop through all rows with i < data.Rows.Count.

Fix Recommendations #

Here's a refactored version of your Validate method that fixes all these issues and implements your desired logic:

public DataValidationModel Validate(DataTable data, IList<FieldModel> fields)
{
    var model = new DataValidationModel() { Errors = new List<RowErrorModel>() };
    int allowedBlankRows = 2; // Match your requirement of 1-2 allowed blank rows
    int blankRowCount = 0;
    int excelRowNumber = 7; // Assuming DataTable row 0 maps to Excel row 7

    foreach (DataRow row in data.Rows)
    {
        // Check if current row is empty
        bool isEmpty = isRowEmpty(data, data.Rows.IndexOf(row));

        if (isEmpty)
        {
            blankRowCount++;
            // If we exceed allowed blank rows, add an error
            if (blankRowCount > allowedBlankRows)
            {
                model.Errors.Add(new RowErrorModel()
                {
                    Row = excelRowNumber,
                    Error = $"Exceeded maximum allowed blank rows ({allowedBlankRows})."
                });
            }
            excelRowNumber++;
            continue; // Skip validation for blank rows
        }

        // Reset blank row counter when we hit a non-blank row
        blankRowCount = 0;

        // Validate all required fields for non-blank row
        // Check Name field
        if (row[0] == DBNull.Value || string.IsNullOrWhiteSpace(row[0].ToString()))
        {
            model.Errors.Add(new RowErrorModel()
            {
                Row = excelRowNumber,
                Error = "The name cannot be blank."
            });
        }

        // Check Site field
        if (row["Site"] == DBNull.Value || string.IsNullOrWhiteSpace(row["Site"].ToString()))
        {
            model.Errors.Add(new RowErrorModel()
            {
                Row = excelRowNumber,
                Error = "Site is required."
            });
        }

        // Check Date fields
        if (row["start date"] == DBNull.Value)
        {
            model.Errors.Add(new RowErrorModel()
            {
                Row = excelRowNumber,
                Error = "Start date is required."
            });
        }
        if (row["end date"] == DBNull.Value)
        {
            model.Errors.Add(new RowErrorModel()
            {
                Row = excelRowNumber,
                Error = "End date is required."
            });
        }

        // Check Classification fields
        if (row["Placement Type"] == DBNull.Value)
        {
            model.Errors.Add(new RowErrorModel()
            {
                Row = excelRowNumber,
                Error = "Placement Type is required."
            });
        }
        if (row["Channel"] == DBNull.Value)
        {
            model.Errors.Add(new RowErrorModel()
            {
                Row = excelRowNumber,
                Error = "Channel is required."
            });
        }
        if (row["Environment"] == DBNull.Value)
        {
            model.Errors.Add(new RowErrorModel()
            {
                Row = excelRowNumber,
                Error = "Environment is required."
            });
        }

        // Check Rate Type related fields (fixed inverted logic)
        if (row["rate type"] != DBNull.Value && !string.IsNullOrWhiteSpace(row["rate type"].ToString()))
        {
            if (row["rate"] == DBNull.Value || string.IsNullOrWhiteSpace(row["rate"].ToString()))
            {
                model.Errors.Add(new RowErrorModel()
                {
                    Row = excelRowNumber,
                    Error = "Rate is required when a rate type is specified."
                });
            }
            if (row["units"] == DBNull.Value || string.IsNullOrWhiteSpace(row["units"].ToString()))
            {
                model.Errors.Add(new RowErrorModel()
                {
                    Row = excelRowNumber,
                    Error = "Units is required when a rate type is specified."
                });
            }
            if (row["cost"] == DBNull.Value || string.IsNullOrWhiteSpace(row["cost"].ToString()))
            {
                model.Errors.Add(new RowErrorModel()
                {
                    Row = excelRowNumber,
                    Error = "Cost is required when a rate type is specified."
                });
            }
        }

        excelRowNumber++;
    }

    // Sort errors by row number for readability
    model.Errors = model.Errors.OrderBy(e => e.Row).ToList();
    return model;
}

Key Improvements: #

• Synchronized Row Tracking: Uses foreach to iterate through DataTable rows, with excelRowNumber correctly incrementing to match the user-facing row number (e.g., Excel rows starting at 7).
• Proper Blank Row Handling: Tracks consecutive blank rows, allows up to 2, and throws an error if the limit is exceeded. Blank rows are skipped from validation entirely, as per your ideal logic.
• Fixed Rate Type Validation: Now checks if rate type is non-blank before requiring rate, units, and cost—aligning with your requirement.
• Cleaner Loop Logic: Eliminates the confusing mix of i and rowCounter by directly iterating over rows.
• Full Row Coverage: Loops through all rows in the DataTable instead of skipping the last one.

Quick Note on isRowEmpty #

Your isRowEmpty method looks mostly solid, but you can optimize it slightly to avoid converting every value to a string unnecessarily:

public bool isRowEmpty(DataTable dt, int index)
{
    if (index < 0 || index >= dt.Rows.Count)
        return false;

    DataRow dr = dt.Rows[index];
    foreach (var item in dr.ItemArray)
    {
        if (item != DBNull.Value && !string.IsNullOrWhiteSpace(item.ToString()))
        {
            return false; // Found a non-empty value, row is not empty
        }
    }
    return true; // All values are null/whitespace
}

This version exits early as soon as it finds a non-empty value, which is more efficient for large tables.

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